TiDB v6.1: ERROR 1105 (HY000): Can't find column test.tbl.id in schema Column: [Column#7] Unique key: [[test.tbl.id]]

[shawn0915]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists tbl;
create table tbl (id int);
with t1 as (select id from tbl), t2 as (select a.id from t1 a join t1 b on a.id = b.id) select * from t2 where id in (select id from t2);

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

return success with empty set.

3. What did you see instead (Required)

ERROR 1105 (HY000): Can't find column test.tbl.id in schema Column: [Column#7] Unique key: [[test.tbl.id]]

4. What is your TiDB version? (Required)

SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89dec
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:15:11
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.001 sec)

[AilinKid]

This is because the main select clause referred to the CTE t2 twice and cloned its schema in buildTableRef and BuildSelectSubq respectively. Nonetheless, it just forgets the wipe out the old schema column's hashcode, which will be used to map the parent column and child's column directly when doing the projection elimination. As a consequence, the join EQ-condition's columns will be mapped to a same left-child column causing resolveIndices chaos.

[AilinKid]

I will file a PR later

[shawn0915]

@aytrack @AilinKid thanks for following.

[shawn0915]

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.2.0-alpha
Edition: Community
Git Commit Hash: 9a77892ac8e513eba25813963de808310295d1ec
Git Branch: heads/refs/tags/v6.2.0-alpha
UTC Build Time: 2022-06-17 14:31:04
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

mysql> drop table if exists tbl;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tbl (id int);
Query OK, 0 rows affected (0.10 sec)

mysql> with t1 as (select id from tbl), t2 as (select a.id from t1 a join t1 b on a.id = b.id) select * from t2 where id in (select id from t2);
Empty set (0.01 sec)