sql return error:ERROR 1105 (HY000): Can't find column in schema Column: [] Unique key: []

[showonlady]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE /*! IF EXISTS*/ mysql_1;
CREATE TABLE `mysql_3` (
`col_int_auto_increment` int(10) AUTO_INCREMENT,
`col_pk_char` char(60)  not null,
`col_pk_varchar` varchar(60) not null,
`col_pk_date` date not null,
`col_int` int,
`col_int__1` int,
`col_int_8` int(8),
`col_int_8__1` int(8),
`col_bigint` bigint,
`col_smallint` smallint,
`col_tinyint` tinyint,
`col_float` float,
`col_double` double,
`col_numeric` numeric,
`col_bit` bit,
`col_bit__1` bit,
`col_enum` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_set` set ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
`col_char_3` char(3),
`col_char_255` char(255),
`col_varchar_1` varchar(1),
`col_varchar_64` varchar(64),
`col_varchar_2048` varchar(2048),
`col_binary_8` binary(8),
`col_varbinary_8` varbinary(8),
`col_date` date,
`col_time` time,
`col_datetime` datetime,
`col_timestamp` timestamp ,
`col_year` year,
`col_text` text,
`col_bool` bool,
`col_boolean` boolean,
`col_longtext` longtext,
`col_mediumtext` mediumtext,
`col_tinyblob` tinyblob,
`col_mediumblob` mediumblob,
`col_longblob` longblob,
`col_blob` blob,
/*Indices*/
KEY `prefix_index2` (`col_char_255`(10)),
KEY `prefix_index1` (`col_text`(15)),
KEY `functional_index2` (`col_datetime`,`col_date`),
KEY `functional_index1` (`col_enum`,`col_year`,`col_int`),
KEY k2 (`col_int`,`col_char_255`),
KEY k1 (`col_pk_varchar`),
primary key (`col_int_auto_increment`,`col_pk_varchar`,`col_datetime`,`col_int`,`col_date`))  ;
ALTER TABLE `mysql_1` DISABLE KEYS;


select * from mysql_3 t1 where  exists ( SELECT DISTINCT a1.* FROM mysql_3 a1 WHERE ( a1.col_pk_char NOT IN  (  SELECT a1.col_pk_char FROM mysql_3 a1 NATURAL RIGHT JOIN mysql_3 a2 WHERE t1.col_pk_date IS NULL GROUP BY a1.col_pk_char   ) )     ) /* QNO 8668 CON_ID 2407531720 */  ;

2. What did you expect to see? (Required)

results from MySQL:

MySQL [chqin]> select * from mysql_3 t1 where  exists ( SELECT DISTINCT a1.* FROM mysql_3 a1 WHERE ( a1.col_pk_char NOT IN  (  SELECT a1.col_pk_char FROM mysql_3 a1 NATURAL RIGHT JOIN mysql_3 a2 WHERE t1.col_pk_date IS NULL GROUP BY a1.col_pk_char   ) )     ) /* QNO 8668 CON_ID 2407531720 */  ;
Empty set (0.00 sec)

3. What did you see instead (Required)

results from tidb

mysql> select * from mysql_3 t1 where  exists ( SELECT DISTINCT a1.* FROM mysql_3 a1 WHERE ( a1.col_pk_char NOT IN  (  SELECT a1.col_pk_char FROM mysql_3 a1 NATURAL RIGHT JOIN mysql_3 a2 WHERE t1.col_pk_date IS NULL GROUP BY a1.col_pk_char   ) )     ) /* QNO 8668 CON_ID 2407531720 */  ;
ERROR 1105 (HY000): Can't find column chqin.mysql_3.col_pk_char in schema Column: [] Unique key: []

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.1
Edition: Community
Git Commit Hash: fea86c8
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:38:34
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[hawkingrei]

Problem at the planner.core.(*basePhysicalAgg).ResolveIndices

[hawkingrei]

CREATE TABLE mysql_3 (
    col_int_auto_increment INT(10) AUTO_INCREMENT,
    col_pk_char CHAR(60) NOT NULL,
    col_pk_date DATE NOT NULL,
    col_datetime DATETIME,
    col_int INT,
    col_date DATE,
    PRIMARY KEY (col_int_auto_increment, col_pk_char, col_datetime, col_int, col_date)
);

SELECT *
FROM mysql_3 t1
WHERE EXISTS
    (SELECT DISTINCT a1.*
     FROM mysql_3 a1
     WHERE (a1.col_pk_char NOT IN
              (SELECT a1.col_pk_char
               FROM mysql_3 a1 NATURAL
               RIGHT JOIN mysql_3 a2
               WHERE t1.col_pk_date IS NULL
               GROUP BY a1.col_pk_char)) )