Bug #365 RIGHT JOIN after LEFT JOIN works incorrectly

• Submitted: 3 May 2003 10:06
• Modified: 13 May 2003 5:11
• Reporter: SINISA MILIVOJEVIC
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.13
• OS: Any (all)
• CPU Architecture: Any

[3 May 2003 10:06] SINISA MILIVOJEVIC

Description:
RIGHT JOIN, when it comes after LEFT JOIN, returns wrong results.

It returns:

  t3.id1  t1.id1  t2.id1
  1       1       1
  1       1       1
  1       2       NULL
  1       3       NULL
  3       1       NULL
  3       2       NULL
  3       3       NULL
  4       1       NULL
  4       2       NULL
  4       3       NULL

But should return (this is returned if tables are inverted and LEFT is used):

  t3.id1  t1.id1  t2.id1
  1       1       1
  1       1       1
  3       3       NULL
  4       NULL    NULL

How to repeat:

  CREATE TABLE t1 (id1 INT);

  INSERT INTO t1 (id1) VALUES (1);
  INSERT INTO t1 (id1) VALUES (2);
  INSERT INTO t1 (id1) VALUES (3);

  CREATE TABLE t2 (id2 INT, id1 INT);

  INSERT INTO t2 (id2, id1) VALUES (1, 1);
  INSERT INTO t2 (id2, id1) VALUES (2, 1);
  INSERT INTO t2 (id2, id1) VALUES (3, 2);

  SELECT t1.id1, t2.id1
  FROM t1
  LEFT JOIN t2 ON t2.id1 = t1.id1;

  CREATE TABLE t3 (id3 INT, id1 INT);

  INSERT INTO t3 (id3, id1) VALUES (1, 1);
  INSERT INTO t3 (id3, id1) VALUES (2, 3);
  INSERT INTO t3 (id3, id1) VALUES (3, 4);

  SELECT t3.id1, t1.id1, t2.id1
  FROM t1
  LEFT JOIN t2 ON t2.id1 = t1.id1
  RIGHT JOIN t3 ON t3.id1 = t1.id1;

[13 May 2003 5:11] Michael Widenius

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

MySQL 4.x doesn't support a combination of LEFT and RIGHT join in the same query.
This is on the TODO for MySQL 5.0.