Failed to execute SQL, but succeed in mysql:v8.0

[joccau]

Bug Report

1. there are 2 tables in database test.
2. The query SQL is executed successfully at mysql:8.0.37 and TiDB:v7.1.0, but failed to execute SQL at TiDB:v7.5.0.
3. the error is below when execute SQL at TiDB:v7.5.0

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[lcwangchao]

A more simple way to reproduce this bug:

create table t1(id int primary key, a varchar(128));
create table t2(id int primary key, b varchar(128), c varchar(128));
UPDATE
    t1
SET
    t1.a = IFNULL(
            (
                SELECT
                    t2.c
                FROM
                    t2
                WHERE
                    t2.b = t1.a
                ORDER BY
                    t2.b DESC,
                    t2.c DESC
                LIMIT
                    1
            ), ''
        )
WHERE
    t1.id = 1;

[lcwangchao]

Seems the UniqueID are not same for schema and column. Stack:

Can't find column test.t2.c in schema Column: [test.t1.id,test.t1.a,test.t2.c] Unique key: []
github.com/pingcap/tidb/pkg/expression.(*Column).resolveIndices
	/Users/wangchao/Code/pingcap/tidb/pkg/expression/column.go:545
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).resolveIndices
	/Users/wangchao/Code/pingcap/tidb/pkg/expression/scalar_function.go:626
github.com/pingcap/tidb/pkg/expression.(*ScalarFunction).ResolveIndices
	/Users/wangchao/Code/pingcap/tidb/pkg/expression/scalar_function.go:620
github.com/pingcap/tidb/pkg/planner/core.(*Update).ResolveIndices
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/core/resolve_indices.go:825
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).buildUpdate
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/core/logical_plan_builder.go:6101
github.com/pingcap/tidb/pkg/planner/core.(*PlanBuilder).Build
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/core/planbuilder.go:863
github.com/pingcap/tidb/pkg/planner.buildLogicalPlan
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/optimize.go:577
github.com/pingcap/tidb/pkg/planner.optimize
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/optimize.go:495
github.com/pingcap/tidb/pkg/planner.Optimize
	/Users/wangchao/Code/pingcap/tidb/pkg/planner/optimize.go:353
github.com/pingcap/tidb/pkg/executor.(*Compiler).Compile
	/Users/wangchao/Code/pingcap/tidb/pkg/executor/compiler.go:98
github.com/pingcap/tidb/pkg/session.(*session).ExecuteStmt
	/Users/wangchao/Code/pingcap/tidb/pkg/session/session.go:2211
github.com/pingcap/tidb/pkg/server.(*TiDBContext).ExecuteStmt
	/Users/wangchao/Code/pingcap/tidb/pkg/server/driver_tidb.go:292
github.com/pingcap/tidb/pkg/server.(*clientConn).handleStmt
	/Users/wangchao/Code/pingcap/tidb/pkg/server/conn.go:2010
github.com/pingcap/tidb/pkg/server.(*clientConn).handleQuery
	/Users/wangchao/Code/pingcap/tidb/pkg/server/conn.go:1801
github.com/pingcap/tidb/pkg/server.(*clientConn).dispatch
	/Users/wangchao/Code/pingcap/tidb/pkg/server/conn.go:1288
github.com/pingcap/tidb/pkg/server.(*clientConn).Run
	/Users/wangchao/Code/pingcap/tidb/pkg/server/conn.go:1067
github.com/pingcap/tidb/pkg/server.(*Server).onConn
	/Users/wangchao/Code/pingcap/tidb/pkg/server/server.go:700
runtime.goexit
	/Users/wangchao/.gvm/gos/go1.21.0/src/runtime/asm_arm64.s:1197