| Bug #92524 | Left join with datetime join condition produces wrong results | ||
|---|---|---|---|
| Submitted: | 21 Sep 2018 7:54 | Modified: | 20 Jul 2020 9:48 |
| Reporter: | Wei Zhao (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 2018 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 2018 8:36]
MySQL Verification Team
Hello Wei Zhao, Thank you for the report and contribution. regards, Umesh
[15 Oct 2018 10:15]
Wei Zhao
The bug also happens on 5.7.17, i.e. the version I'm using.
[9 Mar 2020 11:59]
Frederic Descamps
Thank you for your patch however this is a duplicate of an internal bug which was fixed in MySQL 5.7.22 Cheers,
[20 Jul 2020 9:48]
Erlend Dahl
Fixed in 5.7.22, 8.0.5 under the heading of Bug#25949639 DATE FORMAT 'YYYYMMDD' ISN'T RECOGNIZED IN LEFT JOIN
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.