Bug #96608 Mishandled timezone conversions on timestamps
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
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.