insert into timestamp column with daylight saving affected time generate different results from MySQL

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Note timestamp value is inserted under Asia/Shanghai timezone

mysql> select @@time_zone;
+---------------+
| @@time_zone   |
+---------------+
| Asia/Shanghai |
+---------------+
1 row in set (0.00 sec)

mysql> desc timestamp_table;
+-------+-----------+------+------+---------+-------+
| Field | Type      | Null | Key  | Default | Extra |
+-------+-----------+------+------+---------+-------+
| id    | int(11)   | YES  |      | NULL    |       |
| value | timestamp | YES  |      | NULL    |       |
+-------+-----------+------+------+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into timestamp_table values(1,'1990-09-16 01:59:59'),(1,'1990-09-16 02:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into timestamp_table values(1,'1990-09-16 00:59:59'),(1,'1990-09-16 01:00:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> select * from timestamp_table;
+------+---------------------+
| id   | value               |
+------+---------------------+
|    1 | 1990-09-15 17:59:59 |
|    1 | 1990-09-15 18:00:00 |
|    1 | 1990-09-15 15:59:59 |
|    1 | 1990-09-15 17:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)

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

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

mysql> select * from timestamp_table;
+------+---------------------+
| id   | value               |
+------+---------------------+
|    1 | 1990-09-15 16:59:59 |
|    1 | 1990-09-15 18:00:00 |
|    1 | 1990-09-15 15:59:59 |
|    1 | 1990-09-15 16:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)

3. What did you see instead (Required)

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

mysql> select * from timestamp_table;
+------+---------------------+
| id   | value               |
+------+---------------------+
|    1 | 1990-09-15 17:59:59 |
|    1 | 1990-09-15 18:00:00 |
|    1 | 1990-09-15 15:59:59 |
|    1 | 1990-09-15 17:00:00 |
+------+---------------------+
4 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.5.0-alpha-202-g077eb80f1
Edition: Community
Git Commit Hash: 077eb80f1317a54b1a73894d9f58cd6dfff19260
Git Branch: master
UTC Build Time: 2022-02-08 04:27:22
GoVersion: go1.17.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[likzn]

I think the reason of it is below. The daylight time has overlap in some time, but go does not guarantee which timezone is be selected. Some issue in https://github.com/golang/go/issues/50223

// A daylight savings time transition skips or repeats times.
// For example, in the United States, March 13, 2011 2:15am never occurred,
// while November 6, 2011 1:15am occurred twice. In such cases, the
// choice of time zone, and therefore the time, is not well-defined.
// Date returns a time that is correct in one of the two zones involved
// in the transition, but it does not guarantee which.
func Date(year int, month Month, day, hour, min, sec, nsec int, loc *Location) Time {
	if loc == nil {
		panic("time: missing Location in call to Date")
	}

[mjonss]

Related to #61368