Bug #1677 Inaccurate Results with Inner Join and Right Join

• Submitted: 27 Oct 2003 10:46
• Modified: 5 Nov 2003 0:45
• Reporter: Dean Ellis
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.17 and 4.1.1
• OS: Linux (Linux 2.4)
• CPU Architecture: Any

[27 Oct 2003 10:46] Dean Ellis

Description:
Query of the form:

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id RIGHT JOIN t3 ON t3.id = t2.id

Produces apparently inaccurate results.

How to repeat:
CREATE TABLE lj1 ( col1 int );
CREATE TABLE lj2 ( col2 int );
CREATE TABLE lj3 ( col3 int );

INSERT INTO lj1 VALUES (1), (2), (3);
INSERT INTO lj2 VALUES (1), (2);
INSERT INTO lj3 VALUES (1), (3);

SELECT * FROM lj2 INNER JOIN lj3 ON lj2.col2 = lj3.col3 RIGHT JOIN lj1 ON lj1.col1 = lj2.col2;

SELECT * FROM lj2 INNER JOIN lj3 ON lj2.col2 = lj3.col3 RIGHT JOIN lj1 ON lj1.col1 = lj3.col3;

Both queries produce:

+------+------+------+
| col2 | col3 | col1 |
+------+------+------+
|    1 |    1 |    1 |
|    2 | NULL |    1 |
|    1 | NULL |    2 |
|    2 | NULL |    2 |
|    1 | NULL |    3 |
|    2 | NULL |    3 |
+------+------+------+

Expecting:

+------+------+------+
| col2 | col3 | col1 |
+------+------+------+
|    1 |    1 |    1 |
| NULL | NULL |    2 |
| NULL | NULL |    3 |
+------+------+------+

Suggested fix:
n/a

[3 Nov 2003 9:21] Dean Ellis

Per Serg this is processed like a nested join and the result is as expected currently.  Marking as Duplicate due to the number of existing nested join bugs.

[5 Nov 2003 0:45] Alexander Keremidarski

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of #1591 
http://bugs.mysql.com/1591