INSERT..SET vs INSERT ..VALUES to set Default current time stamp : INSERT..SET is not setting default timestamp.

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
create table t1 (a int not null auto_increment,primary key(a), t timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
insert into t1 values (default,default);
insert into t1 set a=default,t=default;
select * from t1;

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

mysql> select * from t1;
+---+---------------------+
| a | t                   |
+---+---------------------+
| 1 | 2021-11-18 08:29:14 |
| 2 | 2021-11-18 08:29:14 |
+---+---------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> select * from t1;
+---+---------------------+
| a | t                   |
+---+---------------------+
| 1 | 2021-11-18 08:29:10 |
| 2 | 0000-00-00 00:00:00 |
+---+---------------------+
2 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: master
UTC Build Time: 2021-11-16 02:57:45
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

This has 2 issues:

1. In INSERT .. SET context, the default used is incorrect for timestamps (it should be the same as regular insert syntax). This is critical.
2. The value 0000-00-00 00:00:00 should not even be possible to insert in the default SQL mode, so why is it chosing this instead of the actual default? See:

tidb> insert into t1 values (3, '0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect timestamp value: '0000-00-00 00:00:00' for column 't' at row 1
tidb> insert into t1 set a=3,t='0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect timestamp value: '0000-00-00 00:00:00' for column 't' at row 1

[mjonss]

/assign

[mjonss]

My findings so far is that INSERT INTO t1 SET t = DEFAULT sets the timestamp to the zero-timestamp here
See stacktrace:

 0  0x0000000003140afe in github.com/pingcap/tidb/planner/core.(*expressionRewriter).evalDefaultExpr
    at ./planner/core/expression_rewriter.go:1890
 1  0x0000000003134425 in github.com/pingcap/tidb/planner/core.(*expressionRewriter).Leave
    at ./planner/core/expression_rewriter.go:1147
 2  0x00000000020f11d9 in github.com/pingcap/tidb/parser/ast.(*DefaultExpr).Accept
    at ./parser/ast/expressions.go:636
 3  0x0000000003122357 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteExprNode
    at ./planner/core/expression_rewriter.go:199
 4  0x0000000003121a7b in github.com/pingcap/tidb/planner/core.(*PlanBuilder).rewriteWithPreprocess
    at ./planner/core/expression_rewriter.go:145
 5  0x000000000325ff9d in github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildSetValuesOfInsert
    at ./planner/core/planbuilder.go:3235
 6  0x000000000325cded in github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildInsert
    at ./planner/core/planbuilder.go:3069
 7  0x00000000032324f9 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
    at ./planner/core/planbuilder.go:699
 8  0x00000000033b5458 in github.com/pingcap/tidb/planner.optimize
    at ./planner/optimize.go:337
 9  0x00000000033b3d85 in github.com/pingcap/tidb/planner.Optimize
    at ./planner/optimize.go:221
10  0x0000000003da56af in github.com/pingcap/tidb/executor.(*Compiler).Compile
    at ./executor/compiler.go:66
11  0x0000000003fe3791 in github.com/pingcap/tidb/session.(*session).ExecuteStmt
    at ./session/session.go:1560
12  0x00000000040a5b4f in github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
    at ./server/driver_tidb.go:219
13  0x0000000004096e65 in github.com/pingcap/tidb/server.(*clientConn).handleStmt
    at ./server/conn.go:1902
14  0x0000000004094cd2 in github.com/pingcap/tidb/server.(*clientConn).handleQuery
    at ./server/conn.go:1771
15  0x000000000408f59c in github.com/pingcap/tidb/server.(*clientConn).dispatch

While the INSERT INTO t1 (t) VALUES (DEFAULT) sets the timestamp to current time here
See stacktrace

 0  0x0000000002aaee47 in github.com/pingcap/tidb/expression.GetTimeValue
    at ./expression/helper.go:131
 1  0x0000000002c54bc8 in github.com/pingcap/tidb/table.getColDefaultValue
    at ./table/column.go:602
 2  0x0000000002c5379b in github.com/pingcap/tidb/table.GetColDefaultValue
    at ./table/column.go:535
 3  0x000000000325a7c8 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).getDefaultValue
    at ./planner/core/planbuilder.go:2915
 4  0x0000000003261545 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildValuesListOfInsert
    at ./planner/core/planbuilder.go:3299
 5  0x000000000325d4d3 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).buildInsert
    at ./planner/core/planbuilder.go:3075
 6  0x00000000032324f9 in github.com/pingcap/tidb/planner/core.(*PlanBuilder).Build
    at ./planner/core/planbuilder.go:699
 7  0x00000000033b5458 in github.com/pingcap/tidb/planner.optimize
    at ./planner/optimize.go:337
 8  0x00000000033b3d85 in github.com/pingcap/tidb/planner.Optimize
    at ./planner/optimize.go:221
 9  0x0000000003da56af in github.com/pingcap/tidb/executor.(*Compiler).Compile
    at ./executor/compiler.go:66
10  0x0000000003fe3791 in github.com/pingcap/tidb/session.(*session).ExecuteStmt
    at ./session/session.go:1560
11  0x00000000040a5b4f in github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
    at ./server/driver_tidb.go:219
12  0x0000000004096e65 in github.com/pingcap/tidb/server.(*clientConn).handleStmt
    at ./server/conn.go:1902
13  0x0000000004094cd2 in github.com/pingcap/tidb/server.(*clientConn).handleQuery
    at ./server/conn.go:1771
14  0x000000000408f59c in github.com/pingcap/tidb/server.(*clientConn).dispatch
    at ./server/conn.go:1276
15  0x000000000408b8ff in github.com/pingcap/tidb/server.(*clientConn).Run
    at ./server/conn.go:1031

Unfortunately the same code that resolves/rewrites SELECT DEFAULT(t) FROM t1 returns the zero timestamp in MySQL, so a quick fix there for INSERT INTO t1 SET t = DEFAULT would break that behaviour instead.

[bb7133]

Change the severity of this issue from 'critical' to 'major' because the syntax is not commonly used.

[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.