ERROR 1054 (42S22): Unknown column '1' in 'where clause'

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (

  `status` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> select * from t where t. status = 1;

ERROR 1054 (42S22): Unknown column '1' in 'where clause'

mysql> select * from t where t.status = 1;

Empty set (0.01 sec)

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

No error.

3. What did you see instead (Required)

Error.

4. What is your TiDB version? (Required)

8acd5c8 and above.

[jackysp]

[2022/05/30 17:15:02.544 +08:00] [INFO] [conn.go:1149] ["command dispatched failed"] [conn=2199023255955] [connInfo="id:2199023255955, addr:127.0.0.1:52046 status:10, collation:utf8mb4_0900_ai_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="select * from t where t. status = 1"] [txn_mode=OPTIMISTIC] [timestamp=0] [err="[planner:1054]Unknown column '1' in 'where clause'
github.com/pingcap/errors.AddStack
    /Users/yusp/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/errors.go:174
github.com/pingcap/errors.(*Error).GenWithStackByArgs
    /Users/yusp/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20211224045212-9687c2b0f87c/normalize.go:164
github.com/pingcap/tidb/planner/core.(*expressionRewriter).toColumn
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:1937
github.com/pingcap/tidb/planner/core.(*expressionRewriter).Leave
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:1095
github.com/pingcap/tidb/parser/ast.(*ColumnName).Accept
    /Users/yusp/work/tidb/parser/ast/expressions.go:534
github.com/pingcap/tidb/parser/ast.(*ColumnNameExpr).Accept
    /Users/yusp/work/tidb/parser/ast/expressions.go:596
github.com/pingcap/tidb/parser/ast.(*BinaryOperationExpr).Accept
    /Users/yusp/work/tidb/parser/ast/expressions.go:217
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:199
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:145
github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewrite
    /Users/yusp/work/tidb/planner/core/expression_rewriter.go:113
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelection
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:992
github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSelect
    /Users/yusp/work/tidb/planner/core/logical_plan_builder.go:3864
github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
    /Users/yusp/work/tidb/planner/core/planbuilder.go:701
github.com/pingcap/tidb/planner.optimize
    /Users/yusp/work/tidb/planner/optimize.go:329
github.com/pingcap/tidb/planner.Optimize
    /Users/yusp/work/tidb/planner/optimize.go:202
github.com/pingcap/tidb/executor.(*Compiler).Compile
    /Users/yusp/work/tidb/executor/compiler.go:79
github.com/pingcap/tidb/session.(*session).ExecuteStmt
    /Users/yusp/work/tidb/session/session.go:1916
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
    /Users/yusp/work/tidb/server/driver_tidb.go:231
github.com/pingcap/tidb/server.(*clientConn).handleStmt
    /Users/yusp/work/tidb/server/conn.go:2022
github.com/pingcap/tidb/server.(*clientConn).handleQuery
    /Users/yusp/work/tidb/server/conn.go:1876
github.com/pingcap/tidb/server.(*clientConn).dispatch
    /Users/yusp/work/tidb/server/conn.go:1371
github.com/pingcap/tidb/server.(*clientConn).Run
    /Users/yusp/work/tidb/server/conn.go:1121
github.com/pingcap/tidb/server.(*Server).onConn
    /Users/yusp/work/tidb/server/server.go:559
runtime.goexit
    /usr/local/go/src/runtime/asm_arm64.s:1263"]

[jackysp]

#24854 is the pr which introduce this. @crazycs520 PTAL

[fanrenhoo]

it related to the status is a reserved word,

[fanrenhoo]

This is a parser problem, it seems that t. status = 1, will be parsed as "identifier ....", that make "1" as a column