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:
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

[29 May 2006 10:53] Sebastian Leske
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.
[29 May 2006 11:30] Sebastian Leske
Just noted that this bug is already in the "Web PTS" under sapdb.org 
(reported multiple times even :-) ):


for 7.6
same bug for  7.5:

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!!
[31 May 2006 22:08] Jorge del Conde
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)