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
