Bug 反馈
【 TiDB 版本】7.1.0
【 Bug 的影响】
查询无法执行，影响AP业务
【可能的问题复现步骤】
- 表结构
CREATE TABLE `dws_gs_bill_i18n_user_amt_mdi` (
`I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`I_DATE` int(11) NOT NULL DEFAULT '0' COMMENT '',
`CH_ID_CARD_ENCRYPT` varchar(160) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '',
`I_AMOUNT` decimal(20,4) NOT NULL DEFAULT '0.0000' COMMENT '',
`D_CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
`D_UPDATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `UNIQ_DT_CH_ID_CARD_ENCRYPT` (`I_DATE`,`CH_ID_CARD_ENCRYPT`),
KEY `IDX_CH_ID_CARD_ENCRYPT` (`CH_ID_CARD_ENCRYPT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=90001 COMMENT=''
CREATE TABLE `redpacket_white_user_bankcards` (
`I_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`CH_ID_CARD_ENCRYPT` varchar(160) NOT NULL DEFAULT '' COMMENT '',
`I_STATUS` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
`B_ACTIVE` tinyint(1) NOT NULL DEFAULT '1' COMMENT '',
`CH_COUNTRY_CODE` varchar(3) NOT NULL DEFAULT '' COMMENT '',
PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=485514 COMMENT='';
- sql 语句
SELECT group_concat(concat('{"key":"', CH_DATA_KEY, '","value":[', '{"key":"name1","value":"', I_AMOUNT, '"}', ']}') order by I_AMOUNT) CH_DATA_KEY
FROM (
select 'name2' as CH_DATA_KEY, round(sum(I_AMOUNT) / 100 / count(distinct a.CH_ID_CARD_ENCRYPT), 2) as I_AMOUNT
from dws_gs_bill_i18n_user_amt_mdi as a
join (
select distinct u.CH_ID_CARD_ENCRYPT from broker.redpacket_white_user_bankcards as u
where CH_COUNTRY_CODE != '' and CH_COUNTRY_CODE != 'CHN' and I_STATUS IN (1, 4) and B_ACTIVE = 1) as w
on a.CH_ID_CARD_ENCRYPT = w.CH_ID_CARD_ENCRYPT
where a.I_DATE = 202304 and a.I_AMOUNT >= 100 * 100) sql_fmt_base_0;
【看到的非预期行为】
- 结果
补充说明：
字符集都改成utf8mb4，sql 依然报错
root@127.0.0.1 11:53:50 [test]> ALTER TABLE redpacket_white_user_bankcards CONVERT TO CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.18 sec)
root@127.0.0.1 12:04:00 [test]> SELECT group_concat(concat('{"key":"', CH_DATA_KEY, '","value":[', '{"key":"name1","value":"', I_AMOUNT, '"}', ']}') order by I_AMOUNT) CH_DATA_KEY FROM ( select 'name2' as CH_DATA_KEY, round(sum(I_AMOUNT) / 100 / count(distinct a.CH_ID_CARD_ENCRYPT), 2) as I_AMOUNT from dws_gs_bill_i18n_user_amt_mdi as a join ( select distinct u.CH_ID_CARD_ENCRYPT from redpacket_white_user_bankcards as u where CH_COUNTRY_CODE != '' and CH_COUNTRY_CODE != 'CHN' and I_STATUS IN (1, 4) and B_ACTIVE = 1) as w on a.CH_ID_CARD_ENCRYPT = w.CH_ID_CARD_ENCRYPT where a.I_DATE = 202304 and a.I_AMOUNT >= 100 * 100) sql_fmt_base_0;
ERROR 1105 (HY000): Can't find column Column#40 in schema Column: [Column#37,Column#38] Unique key: []
- 给表join字段加索引，执行失败
root@127.0.0.1 12:08:01 [test]> ALTER TABLE redpacket_white_user_bankcards add key idx_card_enc(CH_ID_CARD_ENCRYPT);
Query OK, 0 rows affected (1.38 sec)
root@127.0.0.1 12:08:25 [test]> SELECT group_concat(concat('{"key":"', CH_DATA_KEY, '","value":[', '{"key":"name1","value":"', I_AMOUNT, '"}', ']}') order by I_AMOUNT) CH_DATA_KEY FROM ( select 'name2' as CH_DATA_KEY, round(sum(I_AMOUNT) / 100 / count(distinct a.CH_ID_CARD_ENCRYPT), 2) as I_AMOUNT from dws_gs_bill_i18n_user_amt_mdi as a join ( select distinct u.CH_ID_CARD_ENCRYPT from redpacket_white_user_bankcards as u where CH_COUNTRY_CODE != '' and CH_COUNTRY_CODE != 'CHN' and I_STATUS IN (1, 4) and B_ACTIVE = 1) as w on a.CH_ID_CARD_ENCRYPT = w.CH_ID_CARD_ENCRYPT where a.I_DATE = 202304 and a.I_AMOUNT >= 100 * 100) sql_fmt_base_0;
ERROR 1105 (HY000): Can't find column Column#40 in schema Column: [Column#37,Column#38] Unique key: []
