mysql中不报错,但是tidb中执行SQL语句报错1105 - should ensure all columns have the same length

SQL语句如下:
SELECT
*
FROM
(
SELECT
ISDRUG,
CLINICID,
CLINICCODE,
CLINICNAME,
SPECS,
SPELLCODE,
WBCODE,
DRUGID,
CLASSCODE,
FREQCODE,
USAGECODE,
BASEDOSE,
DOSEUNIT,
SAMPLEPOSITION,
EXEDPCODE,
EXEDPNAME,
ITEMPRICE,
SCALRATION,
SCOPE,
ISLONGORDER,
REGULARNAME,
RSPELLCODE,
RWBCODE,
SKINTEST,
CHILDDOSE,
ADULTDOSE,
PACKQTY,
PACKUNIT,
MINUNIT,
CLINICSENDUNIT,
HOSSENDUNIT,
OPERATETYPE,
STORENUM,
EXCLUDETYPE,
DOCPOSITION,
CLIEXECDEPTFLAG,
UNITPRICE,
DOSEMODELCODE,
CUSTOMCODE,
CLINICQTYUNIT,
TOPINTEGRAL,
DRUGQUALITYLEVEL,
OTCFLAG,
ISANTIBIOTIC,
DRUGQUALITY,
FUNCTION,
CHECKMETHOD,
USINGTIMES,
PAYRATIO,
ISZERORATE,
PHABASETYPE,
FACNAME
FROM
(
SELECT
‘0’ ISDRUG,
‘’ AS UNITPRICE,
T.CLIITEMID AS CLINICID,
T.CLIITEMCODE AS CLINICCODE,
T.CLIITEMNAME AS CLINICNAME,
‘’ AS DOSEMODELCODE,
‘’ AS SPECS,
T.SPELLCODE AS SPELLCODE,
T.WBCODE AS WBCODE,
‘’ DRUGID,
T.CLIITEMTYPE AS CLASSCODE,
‘’ FREQCODE,
‘’ USAGECODE,
0 BASEDOSE,
T.CALCULATEUNIT AS DOSEUNIT,
‘’ AS CHILDDOSE,
‘’ AS ADULTDOSE,
T.SAMPLEORPART AS SAMPLEPOSITION,
ROUND((
SELECT
IFNULL( sum( u.UNITPRICE * t1.qty ), 0 ) AS SALEPRICE
FROM
cli_clinicitem_fee_cmp t1,
fin_undruginfo u
WHERE
t1.cliitemid = T.CLIITEMID
AND u.itemstatus = ‘1’
AND t1.isdrug = ‘0’
AND t1.feeitemid = u.itemid
) + (
SELECT
ROUND( IFNULL( sum( u1.saleprice * t1.qty / u1.packqty ), 0 ), 2 ) AS SALEPRICE
FROM
cli_clinicitem_fee_cmp t1,
pha_druginfo u1
WHERE
t1.cliitemid = T.CLIITEMID
AND u1.isvalid = ‘1’
AND t1.isdrug = ‘1’
AND t1.feeitemid = u1.drugid
),
2
) AS ITEMPRICE,
0 SCALRATION,
T.SCOPE,
‘1’ AS ISLONGORDER,
T.CLIITEMNAME AS REGULARNAME,
T.SPELLCODE AS RSPELLCODE,
T.WBCODE AS RWBCODE,
‘0’ AS SKINTEST,
‘1’ AS PACKQTY,
T.CALCULATEUNIT AS PACKUNIT,
T.CALCULATEUNIT AS MINUNIT,
‘’ AS CLINICSENDUNIT,
‘’ AS HOSSENDUNIT,
OPERATETYPE AS OPERATETYPE,
‘’ AS STORENUM,
T.EXCLUDETYPE AS EXCLUDETYPE,
‘’ AS DOCPOSITION,
T.EXECDEPT AS CLIEXECDEPTFLAG,
‘’ AS CUSTOMCODE,
‘’ AS CLINICQTYUNIT,
‘’ AS TOPINTEGRAL,
‘’ AS DRUGQUALITYLEVEL,
‘’ AS OTCFLAG,
‘’ AS ISANTIBIOTIC,
‘’ AS DRUGQUALITY,
t.OPEXECDEPT AS EXEDPCODE,
t.OPEXECDEPTNAME AS EXEDPNAME,
T.CLASSIFICATION AS FUNCTION,
T.APPLYSEX,
T.CHECKMETHOD,
D.USINGTIMES,
T.PAYRATIO,
‘’ AS ISZERORATE,
‘’ AS PHABASETYPE,
‘’ AS FACNAME
FROM
Cli_Clinicitem_info t
LEFT JOIN COM_DATA_FREQUENTNESS D ON T.CLIITEMID = D.DATAID
AND D.DATATYPE = ‘CLINIC’
WHERE
T.ISVALID = ‘1’
AND t.ORGCODE = ‘’
AND NOT (
t.CLIITEMTYPE IN ( ‘E’, ‘D’ )
AND T.OPERATETYPE IN ( ‘2’, ‘3’, ‘4’ ))
AND ( t.herbalflag != ‘1’ OR t.herbalflag IS NULL )
) TAB
WHERE
(
TAB.SCOPE LIKE concat( ‘%’, ‘’, ‘%’ )
OR TAB.SCOPE = ‘0’
OR TAB.scope = ‘’
OR TAB.scope IS NULL
)
AND (
TAB.CLINICNAME LIKE concat( concat( ‘%’, ‘’ ), ‘%’ )
OR TAB.SPELLCODE LIKE concat( concat( ‘%’, ‘’ ), ‘%’ )
OR TAB.customcode LIKE concat( concat( ‘%’, ‘’ ), ‘%’ )
OR TAB.rspellcode LIKE concat( concat( ‘%’, ‘’ ), ‘%’ )
OR TAB.regularname LIKE concat( concat( ‘%’, ‘’ ), ‘%’ ))
AND CLASSCODE IN ( ‘C’ )
ORDER BY
TAB.USINGTIMES DESC,
LENGTH( CLINICNAME ) ASC
) AS _page
LIMIT 0,
10

1、反馈下 mysql 和 tidb 的版本
2、将 mysql 和 tidb 的结果反馈下,建议在 linux 中执行,
3、看下 mysql 和 tidb 的 sql_mode 是否相同
4、可以将 tidb.log 中的详细报错信息反馈下。

PS:请正确选择帖子标签和分类、

经过试验发现,是因为下面这个字段导致的。
这个字段,是从其他表中关联查询数据,临时计算得出字段值,是有些复杂;
但是,这么写,在逻辑上说得通,而且在mysql中也没有问题。
希望tidb能够考虑下。
我们用的tidb4.0.2版本。

ROUND((
SELECT
IFNULL( sum( u.UNITPRICE * t1.qty ), 0 ) AS SALEPRICE
FROM
cli_clinicitem_fee_cmp t1,
fin_undruginfo u
WHERE
t1.cliitemid = T.CLIITEMID
AND u.itemstatus = ‘1’
AND t1.isdrug = ‘0’
AND t1.feeitemid = u.itemid
) + (
SELECT
ROUND( IFNULL( sum( u1.saleprice * t1.qty / u1.packqty ), 0 ), 2 ) AS SALEPRICE
FROM
cli_clinicitem_fee_cmp t1,
pha_druginfo u1
WHERE
t1.cliitemid = T.CLIITEMID
AND u1.isvalid = ‘1’
AND t1.isdrug = ‘1’
AND t1.feeitemid = u1.drugid
),
2
) AS ITEMPRICE,

show creaet table cli_clinicitem_fee_cmp ;
show creaet table fin_undruginfo ;
show creaet table cli_clinicitem_fee_cmp ;
show creaet table pha_druginfo COM_DATA_FREQUENTNESS ;
show creaet table Cli_Clinicitem_info ;

辛苦提供下表结构我们复现一下。

以下是所有的表结构(包含视图)

cli_clinicitem_fee_cmp.sql (2.9 KB) cli_clinicitem_info.sql (5.3 KB) com_data_frequentness.sql (1.6 KB) fin_undrug.sql (3.8 KB) fin_undrug_ext.sql (4.1 KB) fin_undruginfo.sql (1.7 KB) pha_drug.sql (10.1 KB) pha_drug_extend.sql (6.5 KB) pha_druginfo.sql (4.1 KB)

根据报错是这两个子查询的值长度不同,能否麻烦您单独查下 ,这两个 saleprice 的结果值是多少? 请展示 tidb 和 mysql 中的结果,麻烦了,多谢。

两个子查询的值长度不同,为什么会报错呢?这个不太理解啊。
我原来使用的那个tidb环境被征做他用了,后来搭建了个单机版的4.0.3版本的,
刚才执行了下,不报错了。难道在4.0.3版本修复了这个问题么?

请问一下,在新搭的环境中两个子查询查询出来的值和原先的环境都是一样的吗?除了集群版本之外,还有无其他差别呢?

不能保证和原来的一样。原来的数据库不能用了,没法继续试验。
SQL语句仅调整过查询条件的参数值,表里的数据是一致的。

抱歉,暂时没有复现您的问题,下次问题出现时,麻烦帮忙再采集对比下这两个值,多谢。