MySQL Bugs: #89819: Equivalent queries produce different results with LEFT JOIN

Bug #89819	Equivalent queries produce different results with LEFT JOIN
Submitted:	27 Feb 2018 7:17	Modified:	27 Feb 2018 8:37
Reporter:	Jaime Sicam	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This bug is similar to https://bugs.mysql.com/bug.php?id=53334 . Please take note of the 
"Impossible ON condition" in the explain plan:

mysql> EXPLAIN SELECT * FROM test.`datetimetest` t 
    -> LEFT JOIN test.`joindatetime` j 
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate` 
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                   |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 17      | const |    1 |   100.00 | NULL                    |
|  1 | SIMPLE      | j     | NULL       | const | PRIMARY       | PRIMARY | 17      | const |    1 |   100.00 | Impossible ON condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------------------+
2 rows in set, 1 warning (0.01 sec)

How to repeat:
Use test data uploaded, then run these queries:

mysql> SELECT * FROM test.`datetimetest` t 
    -> LEFT JOIN test.`joindatetime` j 
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate` 
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------+------+
| thedate             | id | thedate | id   |
+---------------------+----+---------+------+
| 2018-01-31 00:00:00 | 1  | NULL    | NULL |
+---------------------+----+---------+------+
1 row in set (0.00 sec)

Queries that produce the correct result:
mysql> SELECT * FROM test.`datetimetest` t 
    -> LEFT JOIN test.`joindatetime` j 
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate` 
    -> WHERE t.id = 1 AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.`datetimetest` t IGNORE INDEX(PRIMARY) 
    -> LEFT JOIN test.`joindatetime` j IGNORE INDEX(PRIMARY) 
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate` 
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.00 sec)

Table structure and test data

Attachment: test_case.sql (application/octet-stream, text), 587 bytes.

Thank you for the bug report. I was able to repeat with current released version but no anymore with recent source server so it was fixed so what.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> USE `test`;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS `datetimetest`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `datetimetest` (
    ->   `thedate` datetime DEFAULT NULL,
    ->   `id` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `datetimetest`(`thedate`,`id`) values
('2018-01-31 00:00:00','1');

DROP TABLE IF EXISTS `joindatetime`;

CREATE TABLE `joindatetime` (
  `thedate` datetime DEFAULT NULL,
  `id` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `joindatetime`(`thedate`,`id`) values
('2018-01-31 00:00:00','1');
Query OK, 0 rows affected (0.76 sec)

mysql>
mysql> insert  into `datetimetest`(`thedate`,`id`) values
    -> ('2018-01-31 00:00:00','1');
Query OK, 1 row affected (0.16 sec)

mysql>
mysql> DROP TABLE IF EXISTS `joindatetime`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `joindatetime` (
    ->   `thedate` datetime DEFAULT NULL,
    ->   `id` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.62 sec)

mysql>
mysql> insert  into `joindatetime`(`thedate`,`id`) values
    -> ('2018-01-31 00:00:00','1');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test.`datetimetest` t
    -> LEFT JOIN test.`joindatetime` j
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate`
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------+------+
| thedate             | id | thedate | id   |
+---------------------+----+---------+------+
| 2018-01-31 00:00:00 | 1  | NULL    | NULL |
+---------------------+----+---------+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> SELECT * FROM test.`datetimetest` t IGNORE INDEX(PRIMARY)
    ->  LEFT JOIN test.`joindatetime` j IGNORE INDEX(PRIMARY)
    ->  ON t.`id` = j.`id` AND t.`thedate` = j.`thedate`
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.01 sec)

mysql>
----------------------------------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 Source distribution 2018-FEB-12

Copyright (c) 2000, 2018, 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 >
mysql 5.7 > DROP TABLE IF EXISTS `datetimetest`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `datetimetest` (
    ->   `thedate` datetime DEFAULT NULL,
    ->   `id` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `datetimetest`(`thedate`,`id`) values
('2018-01-31 00:00:00','1');

DROP TABLE IF EXISTS `joindatetime`;

CREATE TABLE `joindatetime` (
  `thedate` datetime DEFAULT NULL,
  `id` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert  into `joindatetime`(`thedate`,`id`) values
('2018-01-31 00:00:00','1');
Query OK, 0 rows affected (0.62 sec)

mysql 5.7 >
mysql 5.7 > insert  into `datetimetest`(`thedate`,`id`) values
    -> ('2018-01-31 00:00:00','1');
Query OK, 1 row affected (0.09 sec)

mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS `joindatetime`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `joindatetime` (
    ->   `thedate` datetime DEFAULT NULL,
    ->   `id` varchar(15) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.65 sec)

mysql 5.7 >
mysql 5.7 > insert  into `joindatetime`(`thedate`,`id`) values
    -> ('2018-01-31 00:00:00','1');
Query OK, 1 row affected (0.09 sec)

mysql 5.7 > SELECT * FROM test.`datetimetest` t
    -> LEFT JOIN test.`joindatetime` j
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate`
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.00 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM test.`datetimetest` t IGNORE INDEX(PRIMARY)
    ->  LEFT JOIN test.`joindatetime` j IGNORE INDEX(PRIMARY)
    ->  ON t.`id` = j.`id` AND t.`thedate` = j.`thedate`
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.00 sec)

mysql 5.7 > SELECT * FROM test.`datetimetest` t
    -> LEFT JOIN test.`joindatetime` j
    -> ON t.`id` = j.`id` AND t.`thedate` = j.`thedate`
    -> WHERE t.id = '1' AND t.`thedate` = '2018-01-31';
+---------------------+----+---------------------+------+
| thedate             | id | thedate             | id   |
+---------------------+----+---------------------+------+
| 2018-01-31 00:00:00 | 1  | 2018-01-31 00:00:00 | 1    |
+---------------------+----+---------------------+------+
1 row in set (0.00 sec)

mysql 5.7 >