Bug #8974 Wrong query results in OUTER JOIN + WHERE with conditions on inner table
Submitted: 5 Mar 2005 9:20 Modified: 8 Mar 2005 2:04
Reporter: Sergey Petrunya Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-bk, 4.1-bk OS:
Assigned to: CPU Architecture:Any

[5 Mar 2005 9:20] Sergey Petrunya
Description:
Queries with OUTER JOINs and conditions on inner table in WHERE produce incorrect results.

How to repeat:
create table t60 (a int, b int );
create table t61 (a int, b int );

insert into t60 values (1,1);
insert into t61 values (2,2);

insert into t60 values (1,1);
insert into t61 values (2,4);

select * from t60 left join t61 on t60.a=t61.a where t61.b = 3 or t61.b is null;

The last query will return:
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    1 | NULL | NULL |
|    1 |    1 | NULL | NULL |
+------+------+------+------+

Expected result: empty set.
[5 Mar 2005 12:34] Sergei Golubchik
Why do you think it's a bug ?
All rows satisfy WHERE clause - in particular "or t61.b is null" part.
I'd say results are correct.
[8 Mar 2005 2:04] Sergey Petrunya
Pasted wrong queries into the bug report form, and now can't replicate the problem.