UPDATE to incorrect timestamps results in NULL instead of '0000-00-00' or adjusted to next valid time, as in MySQL

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
set sql_mode = '';
create table t (a serial, b timestamp);
set time_zone='Europe/Amsterdam';
insert into t values (1, '2025-06-01 12:00:00'), (2, '2025-06-01 12:00:00'), (3, '2025-06-01 12:00:00');
update t set b = '2025-03-30 02:30:00' where a = 1;
show warnings;
update t set b = '1969-03-10 02:30:00' where a = 2;
show warnings;
update t set b = '2039-03-10 02:30:00' where a = 3;
show warnings;
select * from t;

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

mysql> drop table if exists t;
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (a serial, b timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone='Europe/Amsterdam';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1, '2025-06-01 12:00:00'), (2, '2025-06-01 12:00:00'), (3, '2025-06-01 12:00:00');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update t set b = '2025-03-30 02:30:00' where a = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1299 | Invalid TIMESTAMP value in column 'b' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> update t set b = '1969-03-10 02:30:00' where a = 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'b' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> update t set b = '2039-03-10 02:30:00' where a = 3;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'b' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 2025-03-30 03:00:00 |
| 2 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
+---+---------------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

which is in DST non-existent range or out-of-range

tidb> drop table if exists t;
Query OK, 0 rows affected (0.08 sec)

tidb> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

tidb> create table t (a serial, b timestamp);
Query OK, 0 rows affected (0.03 sec)

tidb> set time_zone='Europe/Amsterdam';
Query OK, 0 rows affected (0.00 sec)

tidb> insert into t values (1, '2025-06-01 12:00:00'), (2, '2025-06-01 12:00:00'), (3, '2025-06-01 12:00:00');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

tidb> update t set b = '2025-03-30 02:30:00' where a = 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

tidb> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Incorrect time value: '{2025 3 30 2 30 0 0}' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

tidb> update t set b = '1969-03-10 02:30:00' where a = 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

tidb> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Incorrect time value: '{1969 3 10 2 30 0 0}' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

tidb> update t set b = '2039-03-10 02:30:00' where a = 3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

tidb> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Incorrect time value: '{2039 3 10 2 30 0 0}' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

tidb> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+---+------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

tidb_version(): Release Version: v8.5.1
Edition: Community
Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:40:13
GoVersion: go1.23.2
Race Enabled: false
Check Table Before Drop: false
Store: tikv