Bug #20472 Incorrect behavior of left join with where condition using OR
Submitted: 14 Jun 2006 21:29 Modified: 15 Jun 2006 7:13
Reporter: Martin Sedlak Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.22 OS:Microsoft Windows (win)
Assigned to: CPU Architecture:Any

[14 Jun 2006 21:29] Martin Sedlak 
Description:
After upgrade from version 5.0.18 to 5.0.22, some of the SQL statements didn't work like in previous version. After examintation I found out that left join with "or" condition don't work like in previous version and I didn't receive any rows where expected.

This is simplified statement where I simulate the issue:

SELECT t1.*
FROM table1 t1
left join table2 t2 ON (t1.Id_Table2=t2.Id_Table2)
where ((t2.Id_table2=0) or (true))

This do not return any rows even there is row in table t1. In version 5.0.18 this sql return row correctly.

How to repeat:
Create tables from dump and execute statement:

SELECT t1.*
FROM table1 t1
left join table2 t2 ON (t1.Id_Table2=t2.Id_Table2)
where ((t2.Id_table2=0) or (true))

It won't return row in 5.0.22.
[14 Jun 2006 21:30] Martin Sedlak 
Dump of test tables

Attachment: test 20060614 2315.sql (text/plain), 2.21 KiB.
[15 Jun 2006 7:13] Sveta Smirnova 
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Your bug duplicates http://bugs.mysql.com/bug.php?id=20331. And it is solved in 5.0.23.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html