MySQL Bugs: #1591: left outer join on nested left outer join returns wrong result

Bug #1591	left outer join on nested left outer join returns wrong result
Submitted:	17 Oct 2003 17:52	Modified:	14 May 2006 20:15
Reporter:	kevin munroe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Windows (windows XP)
Assigned to:		CPU Architecture:	Any

Description:
left outer joins on nested left outer joins return wrong results
e.g. 

A LOJ (B LOJ C)

How to repeat:
I have these 3 tables A, B, C:

a1 a2 
1  Green 
1  Blue
2  Mauve
4  Orange

b1 b2
1  Pink
4  Purple   
 
c1 c2
1  Yellow
1  Black
5  Brown
 
and I execute this query on mysql
 
SELECT *
FROM `a` left outer join (`b` left outer join `c` on b.b1 = c.c1) on a.a1 = b.b1
 
server returns (not according to hand calculations):

+--------+--------+--------+--------+--------+--------+
| a1     | a2     | b1     | b2     | c1     | c2     |
+--------+--------+--------+--------+--------+--------+
| 1      | Green  | 1      | Pink   | 1      | Yellow |
| 1      | Green  | 1      | Pink   | 1      | Black  |
| 1      | Blue   | 1      | Pink   | 1      | Yellow |
| 1      | Blue   | 1      | Pink   | 1      | Black  |
| 2      | Mauve  | 1      | Pink   | [NULL] | [NULL] |
| 4      | Orange | 1      | Pink   | [NULL] | [NULL] |
| 1      | Green  | 4      | Purple | [NULL] | [NULL] |
| 1      | Blue   | 4      | Purple | [NULL] | [NULL] |
| 2      | Mauve  | 4      | Purple | [NULL] | [NULL] |
| 4      | Orange | 4      | Purple | [NULL] | [NULL] |
+--------+--------+--------+--------+--------+--------+

the correct answer is:

a1 a2 b1 b2 c1 c2
1 Blue 1 Pink 1 Yellow
1 Green 1 Pink 1 Yellow
1 Blue 1 Pink 1 Black
1 Green 1 Pink 1 Black
4 Orange 4 Purple (null) (null)
2 Mauve (null) (null) (null) (null)

This is caused by the fact that MySQL does not support nested joins as required by standard and your query 

SELECT *
FROM `a` left outer join (`b` left outer join `c` on b.b1 = c.c1) on a.a1
= b.b1;

is processed as:

SELECT *
FROM `a` left outer join `b` left outer join `c` on b.b1 = c.c1 on a.a1
= b.b1;

Consider rewriting of your query.

I had a similar problem, with an outer join and an inner join:

    select *
    from worker left outer join
    (works join project on project = pno) on wno = worker;

This seems like a serious problem, especially since doing various advanced things  with outer joins is much more common in MySQL than in other databases, due to the (old) lack of subequeries. It certainly shouldn't accept a certain syntax and then, without warning, give an incorrect result. I know that some of my applications use queries similar to this one.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Fixed in 5.1