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

[17 Oct 2003 17:52] kevin munroe
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)
[19 Oct 2003 10:02] Alexander Keremidarski
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.
[25 Nov 2003 7:13] Thomas Padron-McCarthy
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.
[14 May 2006 20:15] Hartmut Holzgraefe
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