# [KB]TiDB compile SQL failed, update wrong result ## Key Words update, ERROR 1105, compile, SQL failed, wrong result ## Description 有一条 update SQL，逻辑为先关联 test1 和 test2 表，取关联成功的最大值更新到 test1 对应的那行数据。SQL 文本： explain update sbtest1 t1 set t1.k=(select t2.k+4 from sbtest2 t2 where [t1.id](http://t1.id)=[t2.id](http://t2.id) order by t2.k desc limit 1)。 在执行这个 SQL 逻辑时，客户端返回报错 ERROR 1105 (HY000): Can't find column Column#10 in schema Column，并且后台 tidb 日志会打印如下信息： ```go [2022/09/02 10:25:21.687 +08:00] [WARN] [session.go:1974] ["compile SQL failed"] [conn=973028208163160485] [error="Can't find column Column#10 in schema Column: test.sbtest1.id,test.sbtest1.k,test.sbtest1.c,test.sbtest1.pad,Column#9 Unique key: []"] [SQL="explain update sbtest1 t1 set t1.k=(select t2.k+4 from sbtest2 t2 where t1.id=t2.id order by t2.k desc limit 1)"] [2022/09/02 10:25:21.687 +08:00] [INFO] [conn.go:1165] ["command dispatched failed"] [conn=973028208163160485] [connInfo="id:973028208163160485, addr:172.16.4.89:52878 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="explain update sbtest1 t1 set t1.k=(select t2.k+4 from sbtest2 t2 where t1.id=t2.id order by t2.k desc limit 1)"] [txn_mode=PESSIMISTIC] [timestamp=0] [err="Can't find column Column#10 in schema Column: test.sbtest1.id,test.sbtest1.k,test.sbtest1.c,test.sbtest1.pad,Column#9 Unique key: []\ngithub.com/pingcap/tidb/expression.(*Column).resolveIndices\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/column.go:508\ngithub.com/pingcap/tidb/expression.(*Column).ResolveIndices\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/expression/column.go:501\ngithub.com/pingcap/tidb/planner/core.(*Update).ResolveIndices\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/resolve_indices.go:586\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildUpdate\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/logical_plan_builder.go:5247\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).Build\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:743\ngithub.com/pingcap/tidb/planner.optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:328\ngithub.com/pingcap/tidb/planner.Optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:201\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).buildExplain\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:4575\ngithub.com/pingcap/tidb/planner/core.(*PlanBuilder).Build\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:718\ngithub.com/pingcap/tidb/planner.optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:328\ngithub.com/pingcap/tidb/planner.Optimize\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/planner/optimize.go:201\ngithub.com/pingcap/tidb/executor.(*Compiler).Compile\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/compiler.go:80\ngithub.com/pingcap/tidb/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1962\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:229\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2038\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1892\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1387\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1137\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:563\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571" ``` ## Root Cause 原因是 TiDB 在生成 update 执行计划做“消除投影”时，对于查询投影字段经过排序操作后无法与更新字段的列进行正确映射匹配。这个问题也可能会引起更新结果错误问题，参考相关[issue](https://github.com/pingcap/tidb/issues/37568) ## Resolution 升级到未受影响版本 ## Workaround 改写 SQL 将原 SQL `explain update sbtest1 t1 set t1.k=(select t2.k+4 from sbtest2 t2 where t1.id=t2.id order by t2.k desc limit 1)` 改写为 `explain update tb1 set tb1.balance=(select tb2.balance from (select tb2.balance, tb2.seq_no from tb2 where tb2.user_no=tb1.user_no order by seq_no desc limit 1) tb2);` ```sql +----------------------------------------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------+ | Update_11 | N/A | root | | N/A | | └─Apply_15 | 6.00 | root | | CARTESIAN left outer join | | ├─TableReader_18(Build) | 6.00 | root | | data:TableFullScan_17 | | │ └─TableFullScan_17 | 6.00 | cop[tikv] | table:tb1 | keep order:false, stats:pseudo | | └─Projection_19(Probe) | 0.01 | root | | test.tb2.balance | | └─TopN_21 | 0.01 | root | | test.tb2.seq_no:desc, offset:0, count:1 | | └─IndexLookUp_32 | 0.01 | root | | | | ├─TopN_31(Build) | 0.01 | cop[tikv] | | test.tb2.seq_no:desc, offset:0, count:1 | | │ └─IndexRangeScan_29 | 0.01 | cop[tikv] | table:tb2, index:idx_u(user_no) | range: decided by [eq(test.tb2.user_no, test.tb1.user_no)], keep order:false, stats:pseudo | | └─TableRowIDScan_30(Probe) | 0.01 | cop[tikv] | table:tb2 | keep order:false, stats:pseudo | +----------------------------------------+---------+-----------+---------------------------------+--------------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` ## Affect Version v5.0.0-v5.0.6 v5.1.0-v5.1.4 v5.2.0-v5.2.4 v5.3.0-v5.3.3 v5.4.0-v5.4.2 v6.0.0 v6.1.0-v6.1.1