MySQL Bugs: #20122: LEFT JOIN produces spurious result rows if WHERE-condition contains OR

Bug #20122	LEFT JOIN produces spurious result rows if WHERE-condition contains OR
Submitted:	29 May 2006 10:53	Modified:	30 Sep 2008 19:53
Reporter:	Sebastian Leske	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MaxDB	Severity:	S3 (Non-critical)
Version:	7.6.00 Build 012-123-102-632	OS:	Linux (SuSE Linux 9.3 (x86-64))
Assigned to:		CPU Architecture:	Any

Description:
A LEFT JOIN produces result rows containing NULLs, instead of suppressing them.

To reproduce:

create table a (a1 int, a2 int)
create table b (b1 int, b2 int)

insert into a values (1,1)
insert into a values (2,33)
insert into b values (1,3)
insert into b values (2,4)

Now the query

select a.a1, b.b2 from a left join b on a.a1=b.b1 where (b.b2<4)

produces one result row, namely (1,3), as expected. The LEFT JOIN produces two result rows, but one is filtered out because it does not satisfy the WHERE-condition.

The query

select a.a1, b.b2 from a left join b on a.a1=b.b1 where (b.b2<4 or b.b2<4)

however, produces two result rows, (1,3) and (2, NULL).
I would have expected the same result as the first SELECT, seeing that the WHERE-condition is logically equivalent.

I am actually not completely sure which of the two results is "correct" according to SQL standards, but it is at the least not correct to get different results for two logically equivalent statements.

How to repeat:
Follow the instructions in the description.

Just noted that this bug is already in the "Web PTS" under sapdb.org 
(reported multiple times even :-) ):

http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1138366

for 7.6
http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1126744
same bug for  7.5:
http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1126748

Incidentally: I cannot understand why the bugs from the PTS at sapdb.org where not copied into the bug tracking system at bugs.mysql.com. The system at bugs.mysql.com apparently only contains 70 bugs for MaxDB, while the Web PTS contains over 11,000!!

Thanks for your bug report.  This is indeed a bug in maxdb, and mysql treats it nicely:

mysql> select a.a1, b.b2 from a left join b on a.a1=b.b1 where (b.b2<4 or b.b2<4)
    -> ;
+------+------+
| a1   | b2   |
+------+------+
|    1 |    3 | 
+------+------+
1 row in set (0.00 sec)

mysql>