pingcap
Query failed to execute in TiDB but succeeded in MySQL
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t0(c0 int);
CREATE TABLE t1(c0 int);
SELECT t0.c0, t1.c0 FROM t0 NATURAL JOIN t1 WHERE '1' AND (t0.c0 IN (SELECT c0 FROM t0));
2. What did you expect to see? (Required)
Query executed successfully.
3. What did you see instead (Required)
MySQL [d0]> SELECT t0.c0, t1.c0 FROM t0 NATURAL JOIN t1 WHERE '1' AND (t0.c0 IN (SELECT c0 FROM t0));
ERROR 1054 (42S22): Unknown column 't1.c0' in 'field list'
4. What is your TiDB version? (Required)
Release Version: v8.2.0-alpha-234-gdf64c343dd-dirty
Edition: Community
Git Commit Hash: df64c343ddfc4063891900acf95e2b3640c79f29
Git Branch: master
UTC Build Time: 2024-05-27 08:15:16
GoVersion: go1.21.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv
https://github.com/pingcap/tidb/blob/1a24c032126dce4a79ea14b51108dbf4caf02a03/pkg/planner/core/expression_rewriter.go#L2410 https://github.com/pingcap/tidb/blob/1a24c032126dce4a79ea14b51108dbf4caf02a03/pkg/planner/core/expression_rewriter.go#L2441-L2457
It seems that when rewriting an expression, if the SQL statement is a natural join, the field will be retrieved again based on the plan's fullNames. However, at this point, the plan's fullSchema is empty, and null is directly returned, resulting in the inability to find t1.c0.
And when there is a natural join and the where clause is converted to (* github. com/pingcap/tidb/pkg/planner/core. LogicalAggregation), this bug will appear
tidb> SELECT t1.c0, t0.c0 FROM t0 NATURAL JOIN t1 where (select c0 from t2 group by c0); ERROR 1054 (42S22): Unknown column 't1.c0' in 'field list' tidb> SELECT t1.c0, t0.c0 FROM t0 NATURAL JOIN t1 where (t0.c0 in (select c0 from t2 group by c0)); ERROR 1054 (42S22): Unknown column 't1.c0' in 'field list' tidb> SELECT t1.c0, t0.c0 FROM t0 NATURAL JOIN t1 where (select distinct c0 from t2); ERROR 1054 (42S22): Unknown column 't1.c0' in 'field list'
I have modified some code in my repository, but I am not familiar with the TIDB test process. Can anyone help to check which kind of test I should add and how to add test cases?
I have modified some code in my repository, but I am not familiar with the TIDB test process. Can anyone help to check which kind of test I should add and how to add test cases?
I'm not fimiliar with development of TIDB, maybe this doc will help?