MySQL Bugs: #88465: JOIN is performed incorrectly on TIMESTAMP

Bug #88465	JOIN is performed incorrectly on TIMESTAMP
Submitted:	13 Nov 2017 16:34	Modified:	19 Nov 2017 3:37
Reporter:	Vincent Lavoie	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	join, timestamp, timezone

Description:
JOINs produce incorrect output when joining 2 tables on a TIMESTAMP field when the server is in a timezone that observes daylight savings. It seems like the MySQL server performs the JOIN after the conversion to the local timezone. The conversion to the local timezone should be done at the very end, just before sending the data back to the client. This produces an issue around daylight savings time when the same "hour happens twice".

How to repeat:
1- Set server to UTC timezone
2- Create table1 with this content:
  TIMESTAMP             VALUE
 ---------------------|------
  2017-11-05 04:00:00	1.00
  2017-11-05 05:00:00	2.00
  2017-11-05 06:00:00	3.00

3- Create table2 with this content:
  TIMESTAMP             VALUE
 ---------------------|------
  2017-11-05 04:00:00	4.00
  2017-11-05 05:00:00	5.00
  2017-11-05 06:00:00	6.00

4- Run the following queries:
SET time_zone = 'UTC';
SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;

5- Run the following queries:
SET time_zone = 'America/New_York';
SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;

OUTCOME: Step 4 produces 3 lines (as expected), while Step 5 produces 5 lines:
2017-11-05 00:00:00	1.00	2017-11-05 00:00:00	4.00
2017-11-05 01:00:00	2.00	2017-11-05 01:00:00	5.00
2017-11-05 01:00:00	3.00	2017-11-05 01:00:00	5.00
2017-11-05 01:00:00	2.00	2017-11-05 01:00:00	6.00
2017-11-05 01:00:00	3.00	2017-11-05 01:00:00	6.00

EXPECTED: Both queries should return the same number of lines, with the timestamps simply converted for display. The JOIN logic should be performed before the conversion.

Thank you for the bug report. Please provide the complete test case, create tables and insert data statements, queries and their real results and comment the expected results. Thanks.

CREATE TABLE `test1` (
   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `value` decimal(10,2) DEFAULT NULL,
   PRIMARY KEY (`time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test2` (
   `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `value` decimal(10,2) DEFAULT NULL,
   PRIMARY KEY (`time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET time_zone = 'UTC';
INSERT INTO test1 VALUES ('2017-11-05 4:00:00', 1.0), ('2017-11-05 5:00:00', 2.0), ('2017-11-05 6:00:00', 3.0);
INSERT INTO test2 VALUES ('2017-11-05 4:00:00', 3.0), ('2017-11-05 5:00:00', 4.0), ('2017-11-05 6:00:00', 5.0);

SET time_zone = 'America/New_York';
SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;

Should output:
2017-11-05 00:00:00	1.00	2017-11-05 00:00:00	3.00
2017-11-05 01:00:00	2.00	2017-11-05 01:00:00	4.00
2017-11-05 01:00:00	3.00	2017-11-05 01:00:00	5.00

Instead, it outputs:
2017-11-05 00:00:00	1.00	2017-11-05 00:00:00	3.00
2017-11-05 01:00:00	2.00	2017-11-05 01:00:00	4.00
2017-11-05 01:00:00	3.00	2017-11-05 01:00:00	4.00
2017-11-05 01:00:00	2.00	2017-11-05 01:00:00	5.00
2017-11-05 01:00:00	3.00	2017-11-05 01:00:00	5.00

Thank you for the feedback.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log Source distribution 2017-NOV-10

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE `test1` (
    ->    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    `value` decimal(10,2) DEFAULT NULL,
    ->    PRIMARY KEY (`time`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `test2` (
    ->    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    `value` decimal(10,2) DEFAULT NULL,
    ->    PRIMARY KEY (`time`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

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

mysql 5.7 > INSERT INTO test1 VALUES ('2017-11-05 4:00:00', 1.0), ('2017-11-05 5:00:00', 2.0), ('2017-11-05 6:00:00', 3.0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > INSERT INTO test2 VALUES ('2017-11-05 4:00:00', 3.0), ('2017-11-05 5:00:00', 4.0), ('2017-11-05 6:00:00', 5.0);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.7 > SET time_zone = 'America/New_York';
Query OK, 0 rows affected (0.01 sec)

mysql 5.7 > SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 00:00:00 |  1.00 | 2017-11-05 00:00:00 |  3.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  5.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  5.00 |
+---------------------+-------+---------------------+-------+
5 rows in set (0.00 sec)

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

mysql 5.7 > SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 04:00:00 |  1.00 | 2017-11-05 04:00:00 |  3.00 |
| 2017-11-05 05:00:00 |  2.00 | 2017-11-05 05:00:00 |  4.00 |
| 2017-11-05 06:00:00 |  3.00 | 2017-11-05 06:00:00 |  5.00 |
+---------------------+-------+---------------------+-------+
3 rows in set (0.00 sec)

mysql 5.7 >

C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.4-rc-log Source distribution 2017-NOV-10

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > use test
Database changed
mysql 8.0 > SET time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 04:00:00 |  1.00 | 2017-11-05 04:00:00 |  3.00 |
| 2017-11-05 05:00:00 |  2.00 | 2017-11-05 05:00:00 |  4.00 |
| 2017-11-05 06:00:00 |  3.00 | 2017-11-05 06:00:00 |  5.00 |
+---------------------+-------+---------------------+-------+
3 rows in set (0.00 sec)

mysql 8.0 > SET time_zone = 'America/New_York';
Query OK, 0 rows affected (0.00 sec)

mysql 8.0 > SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 00:00:00 |  1.00 | 2017-11-05 00:00:00 |  3.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  4.00 |
+---------------------+-------+---------------------+-------+
3 rows in set (0.00 sec)

mysql 8.0 >

Thank you for the quick reply.

Does this mean the bug is reproducible on 5.7, but it's been fixed in 8.0?

Should I wait for an official 8.0 release or are bug fixes retrofitted in 5.7 as well?

Thanks!

Actually, I went back and noticed that the output in version is still incorrect. The last row when in timezone 'America/New_York' should still read value 5. Instead, it outputs value 4 twice, which is incorrect.

Posted by developer:
 
This looks like a problem with BNL.

On 5.7 latest:

smysql> SET time_zone = 'America/New_York';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT /*+ NO_BNL(t1, t2) */ * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.time |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT /*+ NO_BNL(t1, t2) */ * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 00:00:00 |  1.00 | 2017-11-05 00:00:00 |  3.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  4.00 |
+---------------------+-------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql> EXPLAIN SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SELECT * FROM test.test1 t1 JOIN test.test2 t2 ON t1.time = t2.time;
+---------------------+-------+---------------------+-------+
| time                | value | time                | value |
+---------------------+-------+---------------------+-------+
| 2017-11-05 00:00:00 |  1.00 | 2017-11-05 00:00:00 |  3.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  4.00 |
| 2017-11-05 01:00:00 |  2.00 | 2017-11-05 01:00:00 |  5.00 |
| 2017-11-05 01:00:00 |  3.00 | 2017-11-05 01:00:00 |  5.00 |
+---------------------+-------+---------------------+-------+
5 rows in set (0.00 sec)