DDL set default timestamp value is not correct. #31968
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
SET timestamp = 1000;
drop table if exists t1;
CREATE TABLE t1 ( b INT );
INSERT INTO t1 VALUES (1),(2);
ALTER TABLE t1 ADD COLUMN a6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
ALTER TABLE t1 ADD COLUMN a5 DATETIME(6) DEFAULT NOW(6) FIRST;
ALTER TABLE t1 ADD COLUMN a4 DATETIME(6) ON UPDATE NOW(6) FIRST;
select * from t1;2. What did you expect to see? (Required)
mysql> SET timestamp = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 ( b INT );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD COLUMN a6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD COLUMN a5 DATETIME(6) DEFAULT NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD COLUMN a4 DATETIME(6) ON UPDATE NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from t1;
+------+----------------------------+----------------------------+------+
| a4 | a5 | a6 | b |
+------+----------------------------+----------------------------+------+
| NULL | 1969-12-31 19:16:40.000000 | 1969-12-31 19:16:40.000000 | 1 |
| NULL | 1969-12-31 19:16:40.000000 | 1969-12-31 19:16:40.000000 | 2 |
+------+----------------------------+----------------------------+------+
2 rows in set (0.00 sec)3. What did you see instead (Required)
mysql> SET timestamp = 1000;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t1 ( b INT );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD COLUMN a6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE t1 ADD COLUMN a5 DATETIME(6) DEFAULT NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE t1 ADD COLUMN a4 DATETIME(6) ON UPDATE NOW(6) FIRST;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from t1;
+------+----------------------------+----------------------------+------+
| a4 | a5 | a6 | b |
+------+----------------------------+----------------------------+------+
| NULL | 2022-01-26 12:29:23.624460 | 2022-01-26 12:29:23.613093 | 1 |
| NULL | 2022-01-26 12:29:23.624460 | 2022-01-26 12:29:23.613093 | 2 |
+------+----------------------------+----------------------------+------+
2 rows in set (0.00 sec)4. What is your TiDB version? (Required)
| Release Version: v5.5.0-alpha-60-g8c70432b0-dirty
Edition: Community
Git Commit Hash: 8c70432b000d648e9ea26cc57a7fe79d2e67d341
Git Branch: master
UTC Build Time: 2022-01-26 16:56:34
GoVersion: go1.16.9
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |