query failed after add index

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set time_zone='UTC';
drop table if exists t02;
create table t02(a timestamp);
select * from t02 where a != "2038-1-19 3:14:45";
alter table t02 add unique index (a);
select * from t02 where a != "2038-1-19 3:14:45";

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

query successfully after adding index.

3. What did you see instead (Required)

ySQL root@127.0.0.1:test> set time_zone='UTC';
                       ->  drop table if exists t02;
                       ->  create table t02(a timestamp);
                       ->  select * from t02 where a != "2038-1-19 3:14:45";
                       ->  alter table t02 add unique index (a);
                       ->  select * from t02 where a != "2038-1-19 3:14:45";
Query OK, 0 rows affected
Time: 0.002s

Query OK, 0 rows affected
Time: 0.012s

Query OK, 0 rows affected
Time: 0.006s

+---+
| a |
+---+

0 rows in set
Time: 0.014s

Query OK, 0 rows affected
Time: 2.546s
(1292, "Incorrect timestamp value: '2038-01-19 03:14:45.000000'")
MySQL root@127.0.0.1:test>

4. What is your TiDB version? (Required)

master (76267b2)
v5.2.1

[bb7133]

It is about the sql_mode when building the plan for a 'out-of-ranged' date:

A simplified case to reproduce it:

set time_zone='UTC';
create table t02(a timestamp key);
select * from t02 where a != "2038-1-19 3:14:45";

And the error stack:

[2021/10/31 23:29:10.774 +08:00] [INFO] [conn.go:1053] ["command dispatched failed"] [conn=3] [connInfo="id:3, addr:127.0.0.1:45598 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="select * from t02 where a != \"2038-1-19 3:14:45\""] [txn_mode=OPTIMISTIC] [err="[types:1292]Incorrect timestamp value: '2038-01-19 03:14:45.000000'
github.com/pingcap/errors.AddStack
	/home/bb7133/Projects/gopath/pkg/mod/github.com/pingcap/[email protected]/errors.go:174
github.com/pingcap/errors.(*Error).GenWithStackByArgs
	/home/bb7133/Projects/gopath/pkg/mod/github.com/pingcap/[email protected]/normalize.go:159
github.com/pingcap/tidb/types.(*Datum).convertToMysqlTimestamp
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/types/datum.go:1135
github.com/pingcap/tidb/types.(*Datum).ConvertTo
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/types/datum.go:844
github.com/pingcap/tidb/util/ranger.convertPoint
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/ranger.go:95
github.com/pingcap/tidb/util/ranger.points2Ranges
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/ranger.go:63
github.com/pingcap/tidb/util/ranger.(*rangeDetacher).buildCNFIndexRange
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/ranger.go:376
github.com/pingcap/tidb/util/ranger.(*rangeDetacher).detachCNFCondAndBuildRangeForIndex
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/detacher.go:367
github.com/pingcap/tidb/util/ranger.(*rangeDetacher).detachCondAndBuildRangeForCols
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/detacher.go:791
github.com/pingcap/tidb/util/ranger.DetachCondAndBuildRangeForIndex
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/util/ranger/detacher.go:756
github.com/pingcap/tidb/planner/core.(*DataSource).fillIndexPath
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/logical_plans.go:871
github.com/pingcap/tidb/planner/core.(*DataSource).DeriveStats
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/stats.go:399
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).recursiveDeriveStats
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/stats.go:131
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).recursiveDeriveStats
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/stats.go:124
github.com/pingcap/tidb/planner/core.physicalOptimize
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/optimizer.go:289
github.com/pingcap/tidb/planner/core.DoOptimize
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/core/optimizer.go:164
github.com/pingcap/tidb/planner.optimize
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/optimize.go:386
github.com/pingcap/tidb/planner.Optimize
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/planner/optimize.go:218
github.com/pingcap/tidb/executor.(*Compiler).Compile
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/executor/compiler.go:66
github.com/pingcap/tidb/session.(*session).ExecuteStmt
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/session/session.go:1555
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/driver_tidb.go:219
github.com/pingcap/tidb/server.(*clientConn).handleStmt
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/conn.go:1893
github.com/pingcap/tidb/server.(*clientConn).handleQuery
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/conn.go:1762
github.com/pingcap/tidb/server.(*clientConn).dispatch
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/conn.go:1274
github.com/pingcap/tidb/server.(*clientConn).Run
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/conn.go:1031
github.com/pingcap/tidb/server.(*Server).onConn
	/home/bb7133/Projects/gopath/src/github.com/pingcap/tidb/server/server.go:550
runtime.goexit
	/home/bb7133/Softwares/go/src/runtime/asm_amd64.s:1371"]

I think it is a corner case(very rared scenario for 'out-of-ranged' date condition and MySQL treat it as a warning instead of error:

mysql> select * from t02 where a != "2038-1-19 3:14:45";
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------+
| Level   | Code | Message                                                               |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2038-1-19 3:14:45' for column 'a' at row 1 |
+---------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

I would treat it as 'minor' compatibility bug and we need to update the planner to fix it.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.