Bug #92524 Left join with datetime join condition produces wrong results
Submitted: 21 Sep 7:54 Modified: 15 Oct 10:15
Reporter: Wei Zhao (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.41, 5.5.61,5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[21 Sep 7:54] Wei Zhao
Description:
This is how to reproduce the bug. 

mysql> show create table g;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| g     | CREATE TABLE `g` (
  `a` int(11) NOT NULL,
  `d` datetime DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

mysql> show create table h1;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| h1    | CREATE TABLE `h1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

mysql> select*from g;
+---+---------------------+
| a | d                   |
+---+---------------------+
| 1 | 9999-12-31 00:00:00 |
+---+---------------------+
1 row in set (0.00 sec)

mysql> select*from h1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>  select B.* from h1 left join g B on h1.a=B.a where B.d=str_to_date('99991231',"%Y%m%d") and h1.a=1;
+------+---------------------+
| a    | d                   |
+------+---------------------+
|    1 | 9999-12-31 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql>  select B.* from h1 left join g B on h1.a=B.a and B.d=str_to_date('99991231',"%Y%m%d") where h1.a=1;
+------+------+
| a    | d    |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

The two join queries above are equivalent, they should produce identical results, but they dont. And interestingly, if you replace the 'left join' with 'inner join' in above two queries, then the results are identical.

It turns out the date comparison in the 2nd query is using string comparison, so the date 9999-12-31 is then evaluates unequal to 9999-12-31 00:00:00, hence the error.

I made a patch which solves the problem.

How to repeat:
As above.

Suggested fix:
See my patch.
[21 Sep 7:54] Wei Zhao
this patch fixes the bug

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: left-join-date-time.diff (application/octet-stream, text), 1.94 KiB.

[21 Sep 8:36] Umesh Shastry
Hello Wei Zhao,

Thank you for the report and contribution.

regards,
Umesh
[15 Oct 10:15] Wei Zhao
The bug also happens on 5.7.17, i.e. the version I'm using.