Bug #96608 Mishandled timezone conversions on timestamps

Submitted: 21 Aug 2019 18:53 Modified: 5 Feb 2020 17:18
Reporter: Chris Shumake Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.23, 5.7.27 OS:Debian
Assigned to: CPU Architecture:Any
Tags: DST, timestamp, timezone

[21 Aug 2019 18:53] Chris Shumake
Description:
Timestamps are not converted consistently when compared to themselves for the A/LA timezone.

(This is the first in a series of bugs to decompose the parts from #93199)

How to repeat:
Setup:
username@hostname [dbname]>set session time_zone = '+0:00';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>create table foo (ts timestamp not null primary key default current_timestamp, data char(4));
Query OK, 0 rows affected (0.02 sec)

username@hostname [dbname]>insert into foo values (from_unixtime(1478421000), 'a');
Query OK, 1 row affected (0.01 sec)

username@hostname [dbname]>insert into foo values (from_unixtime(1478424600), 'b');
Query OK, 1 row affected (0.00 sec)

username@hostname [dbname]>select * from foo;
+---------------------+------+
| ts                  | data |
+---------------------+------+
| 2016-11-06 08:30:00 | a    |
| 2016-11-06 09:30:00 | b    |
+---------------------+------+
2 rows in set (0.00 sec)

Demonstration 1:
username@hostname [dbname]>set session time_zone = '-8:00';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>select * from foo f1 join foo f2 on f1.ts = f2.ts;
+---------------------+------+---------------------+------+
| ts                  | data | ts                  | data |
+---------------------+------+---------------------+------+
| 2016-11-06 00:30:00 | a    | 2016-11-06 00:30:00 | a    |
| 2016-11-06 01:30:00 | b    | 2016-11-06 01:30:00 | b    |
+---------------------+------+---------------------+------+
2 rows in set (0.00 sec)

username@hostname [dbname]>set session time_zone = 'America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)

username@hostname [dbname]>select * from foo f1 join foo f2 on f1.ts = f2.ts;
+---------------------+------+---------------------+------+
| ts                  | data | ts                  | data |
+---------------------+------+---------------------+------+
| 2016-11-06 01:30:00 | a    | 2016-11-06 01:30:00 | a    |
| 2016-11-06 01:30:00 | b    | 2016-11-06 01:30:00 | a    |
| 2016-11-06 01:30:00 | a    | 2016-11-06 01:30:00 | b    |
| 2016-11-06 01:30:00 | b    | 2016-11-06 01:30:00 | b    |
+---------------------+------+---------------------+------+
4 rows in set (0.00 sec)

Demonstration 2:
username@hostname [dbname]>delete from foo limit 1;
Query OK, 1 row affected (0.00 sec)

username@hostname [dbname]>select * from foo;
+---------------------+------+
| ts                  | data |
+---------------------+------+
| 2016-11-06 01:30:00 | b    |
+---------------------+------+
1 row in set (0.00 sec)

username@hostname [dbname]>select * from foo f1 join foo f2 on f1.ts = f2.ts;
Empty set (0.00 sec)

Demonstration 3: (maybe index comparison related?)

username@hostname [dbname]>select * from foo f1 join foo f2 on f1.ts + interval 0 hour = f2.ts + interval 0 hour;
+---------------------+------+---------------------+------+
| ts                  | data | ts                  | data |
+---------------------+------+---------------------+------+
| 2016-11-06 01:30:00 | b    | 2016-11-06 01:30:00 | b    |
+---------------------+------+---------------------+------+
1 row in set (0.01 sec)

Suggested fix:
Option 1: Stop transparent timezone conversions when using TIMESTAMP datatype. Displayed datetime should be application controlled.

Option 2: Eliminate timestamp datatype.

Option 3: Figure out where the mismatch is in when DST is being applied for comparisons
[21 Aug 2019 19:03] Chris Shumake
Note this is about daylight savings time conversions
[22 Aug 2019 9:46] MySQL Verification Team
Hello Christopher,

Thank you for the report and test case.
Verified as described with 5.7.27 build.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.