TiDB 7.1.0 Can't find column Column#40 in schema Column: [Column#37, Column#38] Unique key: []

translator_bot · June 22, 2024, 12:50am

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB 7.1.0 Can’t find column Column#40 in schema Column: [Column#37,Column#38] Unique key: []

| username: TiDBer_yyy

Bug Report
【TiDB Version】7.1.0
【Impact of the Bug】
Query cannot be executed, affecting AP business

【Possible Steps to Reproduce the Issue】

Table Structure

CREATE TABLE `dws_gs_bill_i18n_user_amt_mdi` (
  `I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment 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 'Auto-increment primary key',
  `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 Statement

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;

【Observed Unexpected Behavior】

Result

image1380×702 230 KB

Additional Notes:
Changed all character sets to utf8mb4, SQL still reports an error

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: []

Adding an index to the join field of the table, execution failed

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: []

translator_bot · June 22, 2024, 12:50am

| username: ShawnYan | Original post link

Simplify the test case (test version 7.2.0):

CREATE TABLE `t2` (
  `I_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '',
  `c` varchar(160) NOT NULL DEFAULT '' COMMENT '',
  PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */
);

SELECT group_concat(concat('{"key":"', 'name2', '","value":[', '{"key":"name1","value":"', 1, '"}', ']}') order by a) CH_DATA_KEY
FROM (
  select round(1 / count(distinct '1'), 2) as a
  from t2
  join (
    select distinct u.c from t2 as u) as w
  ) t;

SELECT group_concat(concat('{"key":"', 'name2', '","value":[', '{"key":"name1","value":"', 1, '"}', ']}') ) CH_DATA_KEY
FROM (
  select round(1 / count(distinct '1'), 2) as a
  from t2
  join (
    select distinct u.c from t2 as u) as w
  ) t;

There is an error with order by a

translator_bot · June 22, 2024, 12:50am

| username: 有猫万事足 | Original post link

It seems that there is a similar issue on GitHub already. You can track the progress of the fix.

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

Understood, thank you. Let’s see how we can resolve it at the business level.

Recently, during the upgrade, we encountered many SQL compatibility issues. Why isn’t it backward compatible?

translator_bot · June 22, 2024, 12:50am

| username: aytrack | Original post link

Which version were you using before? Could it execute successfully on the previous version?

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

Version 5.0.4 can be executed. This issue mainly affects core functionality.

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

The unstable result set mentioned GROUP_CONCAT( customer_id order by customer_id SEPARATOR ',' )

I wonder if it is related to this

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

Buddy, the official documentation states that using GROUP_CONCAT with ORDER BY can solve the issue of unstable result sets. It’s a bit strange…

https://docs.pingcap.com/zh/tidb/stable/dev-guide-unstable-result-set#due-to-the-lack-of-order-by-in-group_concat-resulting-in-unstable-result-sets

translator_bot · June 22, 2024, 12:50am

| username: redgame | Original post link

Did you encounter that issue…?

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

Yes~

translator_bot · June 22, 2024, 12:50am

| username: TiDBer_yyy | Original post link

Is there any other way to modify the SQL?
The business layer requires the order by feature.

Fix: Add order by in the outermost subquery

SELECT group_concat(concat('{"key":"', 'name2', '","value":[', '{"key":"name1","value":"', 1, '"}', ']}') order by a) CH_DATA_KEY
FROM (
  select round(1 / count(distinct '1'), 2) as a
  from t2
  join (
    select distinct u.c from t2 as u) as w
  order by a ) t;

translator_bot · June 22, 2024, 12:50am

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.