Some bugs on date types boundary values

[you06]

Bug Report

Please answer these questions before submitting your issue. Thanks!

I ran sql-boundary on TiDB, and find the 50 failure cases.

1. Minimal reproduce step (Required)

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('2038-01-18 03:14:07');
UPDATE t SET c = adddate(c,INTERVAL 48 HOUR);
SELECT * FROM t;

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

Update statement should got failed.

3. What did you see instead (Required)

Update statement successes, the select statement got error.

mysql> SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 03:14:07'

4. What is your TiDB version? (Required)

Release Version: v5.1.0-alpha-167-ga8ad9b7d3
Edition: Community
Git Commit Hash: a8ad9b7d3aa53d0181422d2b22fa8d57b7d56db1
Git Branch: master
UTC Build Time: 2021-06-05 07:04:28
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

5. Attachment

There are many related bugs.

Manipulate data with different units.

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('2038-01-18 03:14:07');
UPDATE t SET c = adddate(c,INTERVAL 2880 MINUTE);
SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 03:14:07'

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('2038-01-18 03:14:07');
UPDATE t SET c = adddate(c,INTERVAL 172800 SECOND);
SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 03:14:07'

Similar bugs on lower bound.

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('1970-01-02 00:00:01');
UPDATE t SET c = adddate(c,INTERVAL -48 HOUR);
SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 00:00:01'

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('1970-01-02 00:00:01');
UPDATE t SET c = adddate(c,INTERVAL -2880 MINUTE);
SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 00:00:01'

Similar bugs on other functions.

CREATE TABLE t(c timestamp);
INSERT INTO t VALUES('2038-01-18 03:14:07');
UPDATE t SET c = subdate(c,INTERVAL -48 HOUR);
SELECT * FROM t;
ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 03:14:07'

Similar bugs on other data types.

CREATE TABLE t(c datetime);
INSERT INTO t VALUES('9999-12-30 23:59:59');
UPDATE t SET c = subtime(c,'-3 0:0:0');
SELECT * FROM t;
+---------------------+
| c                   |
+---------------------+
| 1816-04-01 05:56:07 |
+---------------------+

Notice this update should fail and the read value is strange.

[dveeden]

I can't reproduce the issue on v5.2.1

 MySQL  127.0.0.1:4000  test  SQL > SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.2.1
Edition: Community
Git Commit Hash: cd8fb24c5f7ebd9d479ed228bb41848bd5e97445
Git Branch: heads/refs/tags/v5.2.1
UTC Build Time: 2021-09-08 02:32:56
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.0006 sec)
 MySQL  127.0.0.1:4000  test  SQL > CREATE TABLE t(c timestamp);
Query OK, 0 rows affected (0.1110 sec)
 MySQL  127.0.0.1:4000  test  SQL > INSERT INTO t VALUES('2038-01-18 03:14:07');
Query OK, 1 row affected (0.0216 sec)
 MySQL  127.0.0.1:4000  test  SQL > UPDATE t SET c = adddate(c,INTERVAL 48 HOUR);
ERROR: 1292 (22007): Incorrect timestamp value: '2038-01-20 03:14:07'
 MySQL  127.0.0.1:4000  test  SQL > SELECT * FROM t;
+---------------------+
| c                   |
+---------------------+
| 2038-01-18 03:14:07 |
+---------------------+
1 row in set (0.0034 sec)