INSERT IGNORE + STRICT_TRANS_TABLES results in different TIMESTAMP than MySQL

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Similar to #61334 but only with INSERT IGNORE + STRICT_TRANS_TABLES SQL_MODE.

create table t (ts timestamp);
set time_zone = 'Europe/Amsterdam';
set sql_mode = 'strict_trans_tables,no_zero_date,no_zero_in_date,error_for_division_by_zero';
insert into t values ('2025-03-30 02:00:00');
select * from t;
insert ignore into t values ('2025-03-30 02:00:00');
show warnings;
select * from t;

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

Adjusted timestamp to next valid timestamp (02:00:00-02:59:59 does not exists during this Daylight Saving Time transition).

mysql-80> create table t (ts timestamp);
Query OK, 0 rows affected (0.01 sec)

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

mysql-80> set sql_mode = 'strict_trans_tables,no_zero_date,no_zero_in_date,error_for_division_by_zero';
Query OK, 0 rows affected (0.00 sec)

mysql-80> insert into t values ('2025-03-30 02:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2025-03-30 02:00:00' for column 'ts' at row 1
mysql-80> select * from t;
Empty set (0.01 sec)

mysql-80> insert ignore into t values ('2025-03-30 02:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

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

mysql-80> select * from t;
+---------------------+
| ts                  |
+---------------------+
| 2025-03-30 03:00:00 |
+---------------------+
1 row in set (0.01 sec)

3. What did you see instead (Required)

ZERO timestamp '0000-00-00 00:00:00'

tidb> create table t (ts timestamp);
Query OK, 0 rows affected (0.04 sec)

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

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

tidb> insert into t values ('2025-03-30 02:00:00');
ERROR 1292 (22007): Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
tidb> select * from t;
Empty set (0.01 sec)

tidb> insert ignore into t values ('2025-03-30 02:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

tidb> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1292 | Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1 |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

tidb> select * from t;
+---------------------+
| ts                  |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

And after #61337 there are no row!

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

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

tidb> set sql_mode = 'strict_trans_tables,no_zero_date,no_zero_in_date,error_for_division_by_zero';
Query OK, 0 rows affected (0.01 sec)

tidb> insert into t values ('2025-03-30 02:00:00');
ERROR 1292 (22007): Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
tidb> select * from t;
Empty set (0.01 sec)

tidb> insert ignore into t values ('2025-03-30 02:00:00');
ERROR 1292 (22007): Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1
tidb> show warnings;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message                                                                   |
+-------+------+---------------------------------------------------------------------------+
| Error | 1292 | Incorrect timestamp value: '2025-03-30 02:00:00' for column 'ts' at row 1 |
+-------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

tidb> select * from t;
Empty 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

And row not inserted:

tidb_version(): Release Version: v9.0.0-beta.1.pre-849-gfbf158686d
Edition: Community
Git Commit Hash: fbf158686df0d6c276a5233746bf4ce8d59e9b72
Git Branch: HEAD
UTC Build Time: 2025-05-30 21:10:25
GoVersion: go1.23.9
Race Enabled: false
Check Table Before Drop: false
Store: tikv