Query failed to execute in TiDB but succeeded in MySQL

[FullDuplexFish]

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

[dash12653]

tidb/pkg/planner/core/expression_rewriter.go

Line 2410 in 1a24c03

col, name, err := findFieldNameFromNaturalUsingJoin(planCtx.plan, v)

tidb/pkg/planner/core/expression_rewriter.go

Lines 2441 to 2457 in 1a24c03

	func findFieldNameFromNaturalUsingJoin(p base.LogicalPlan, v *ast.ColumnName) (col *expression.Column, name *types.FieldName, err error) {
	switch x := p.(type) {
	case *LogicalLimit, *LogicalSelection, *LogicalTopN, *LogicalSort, *LogicalMaxOneRow:
	return findFieldNameFromNaturalUsingJoin(p.Children()[0], v)
	case *LogicalJoin:
	if x.fullSchema != nil {
	idx, err := expression.FindFieldName(x.fullNames, v)
	if err != nil {
	return nil, nil, err
	}
	if idx >= 0 {
	return x.fullSchema.Columns[idx], x.fullNames[idx], nil
	}
	}
	}
	return nil, nil, nil
	}

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.

[dash12653]

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'

[dash12653]

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?

[FullDuplexFish]

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?

[dash12653]

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?

Thanks for reminding~