Can't find column in update statement and wrong update results

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists tb1, tb2;
create table tb1(user_no int ,balance int,primary key(user_no));
create table tb2 (user_no int ,balance int,seq_no int,dt datetime,primary key(seq_no),key idx_u(user_no));
insert into tb1 values(1,100),(2,100),(3,200),(4,1000),(5,232),(6,111);
insert into tb2 values(1,99,1,now()),(2,101,2,now()),(3,800,3,now()),(4,6000,4,now()),(5,232,5,now()),(6,11132,6,now());
insert into tb2 values(1,999,7,now()),(2,1013,8,now()),(3,83200,9,now()),(4,632000,10,now()),(5,2332,11,now()),(6,121132,12,now());
update tb1 set tb1.balance=(select tb2.balance from tb2 where tb2.user_no=tb1.user_no order by seq_no desc limit 1);
update tb1 inner join tb2 on tb1.user_no = tb2.user_no set tb1.balance = tb2.balance order by tb2.seq_no desc;
select * from tb1;

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

The two update statements should be successful.

3. What did you see instead (Required)

The first one return an error, "ERROR 1105 (HY000): Can't find column test.tb2.balance in schema Column: [test.tb1.user_no,test.tb1.balance,test.tb2.balance] Unique key: []"

If I remove the first update statement, the second update statement will succeed, but it updates the wrong values.

mysql> select * from tb1;
+---------+---------+
| user_no | balance |
+---------+---------+
|       1 |     999 |
|       2 |    1013 |
|       3 |   83200 |
|       4 |  632000 |
|       5 |     232 |
|       6 |  121132 |
+---------+---------+
6 rows in set (0.00 sec)

The balance of user_no = 5 should be 2332.

If I run the second update statement again, I get

mysql> select * from tb1;
+---------+---------+
| user_no | balance |
+---------+---------+
|       1 |      99 |
|       2 |     101 |
|       3 |     800 |
|       4 |    6000 |
|       5 |    2332 |
|       6 |   11132 |
+---------+---------+
6 rows in set (0.00 sec)

It seems the order by seq_no clause does not work.

I try the first statement in MySQL 8.0 it gets the correct results, and the second one returns an error, "ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

4. What is your TiDB version? (Required)

v6.1.0

[jackysp]

For the first update error, it seems there's something wrong in the logical plan builder:

github.com/pingcap/tidb/expression.(*Column).resolveIndices
\t/Users/yusp/work/tidb/expression/column.go:503
github.com/pingcap/tidb/expression.(*Column).ResolveIndices
\t/Users/yusp/work/tidb/expression/column.go:496
github.com/pingcap/tidb/planner/core.(*Update).ResolveIndices
\t/Users/yusp/work/tidb/planner/core/resolve_indices.go:586
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildUpdate
\t/Users/yusp/work/tidb/planner/core/logical_plan_builder.go:5145
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
\t/Users/yusp/work/tidb/planner/core/planbuilder.go:705
github.com/pingcap/tidb/planner.optimize
\t/Users/yusp/work/tidb/planner/optimize.go:354
github.com/pingcap/tidb/planner.Optimize
\t/Users/yusp/work/tidb/planner/optimize.go:227
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildExplain
\t/Users/yusp/work/tidb/planner/core/planbuilder.go:4526
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
\t/Users/yusp/work/tidb/planner/core/planbuilder.go:680
github.com/pingcap/tidb/planner.optimize
\t/Users/yusp/work/tidb/planner/optimize.go:354
github.com/pingcap/tidb/planner.Optimize
\t/Users/yusp/work/tidb/planner/optimize.go:227
github.com/pingcap/tidb/executor.(*Compiler).Compile
\t/Users/yusp/work/tidb/executor/compiler.go:81
github.com/pingcap/tidb/session.(*session).ExecuteStmt
\t/Users/yusp/work/tidb/session/session.go:1916
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
\t/Users/yusp/work/tidb/server/driver_tidb.go:230
github.com/pingcap/tidb/server.(*clientConn).handleStmt
\t/Users/yusp/work/tidb/server/conn.go:2022
github.com/pingcap/tidb/server.(*clientConn).handleQuery
\t/Users/yusp/work/tidb/server/conn.go:1876
github.com/pingcap/tidb/server.(*clientConn).dispatch
\t/Users/yusp/work/tidb/server/conn.go:1371
github.com/pingcap/tidb/server.(*clientConn).Run
\t/Users/yusp/work/tidb/server/conn.go:1121
github.com/pingcap/tidb/server.(*Server).onConn
\t/Users/yusp/work/tidb/server/server.go:559
runtime.goexit
\t/usr/local/go/src/runtime/asm_arm64.s:1165

[jackysp]

Tried another update statement

update tb1,(select tb2.balance ,user_no from tb2  order by  seq_no desc limit 1 )  tb3 set tb1.balance=tb3.balance where tb1.user_no=tb3.user_no;

get a panic,

github.com/pingcap/tidb/server.(*clientConn).Run.func1
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1059
runtime.gopanic
\t/usr/local/go/src/runtime/panic.go:838
github.com/pingcap/tidb/executor.(*ExecStmt).Exec.func1
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:371
runtime.gopanic
\t/usr/local/go/src/runtime/panic.go:838
runtime.goPanicIndex
\t/usr/local/go/src/runtime/panic.go:89
github.com/pingcap/tidb/util/chunk.(*Column).IsNull
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/column.go:170
github.com/pingcap/tidb/util/chunk.Row.IsNull
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/row.go:206
github.com/pingcap/tidb/util/chunk.Row.GetDatum
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/row.go:132
github.com/pingcap/tidb/util/chunk.Row.GetDatumRow
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/util/chunk/row.go:121
github.com/pingcap/tidb/executor.(*UpdateExec).updateRows
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/update.go:284
github.com/pingcap/tidb/executor.(*UpdateExec).Next
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/update.go:225
github.com/pingcap/tidb/executor.Next
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:319
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:665
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:516
github.com/pingcap/tidb/executor.(*ExecStmt).Exec
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:465
github.com/pingcap/tidb/session.runStmt
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2069
github.com/pingcap/tidb/session.(*session).ExecuteStmt
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1938
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:230
github.com/pingcap/tidb/server.(*clientConn).handleStmt
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2022
github.com/pingcap/tidb/server.(*clientConn).handleQuery
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1876
github.com/pingcap/tidb/server.(*clientConn).dispatch
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1371
github.com/pingcap/tidb/server.(*clientConn).Run
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1121
github.com/pingcap/tidb/server.(*Server).onConn
\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:559

But it doesn't appear in mocktikv, only with tikv cluster could meet this panic.

[AilinKid]

the reason is the same as: #10962, we should avoid projection elimination in buildUpdate.

assignment {
  expr: depend on -> column-7 (comes from sub-query's output)
}

projection                                         apply join
column-7 <- column-4 --------------------- column-4 & ...

once the projection is eliminated here, the assignment couldn't resolve its self-expression anymore.