Description:
This issue has to do with the way in which MySQL interprets a nested simple join. Suppose you have a database with three tables t1, t2, and t3 as shown below.
table t1:
+----+----------+
| id | t1name |
+----+----------+
| 1 | t1_one |
| 2 | t1_two |
| 3 | t1_three |
+----+----------+
table t2:
+----+--------+
| id | t2name |
+----+--------+
| 1 | t2_one |
| 2 | t2_two |
+----+--------+
table t3:
+----+----------+
| id | t3name |
+----+----------+
| 1 | t3_one |
| 3 | t3_three |
+----+----------+
Now, you want to issue a query that will give you t1name, t2name, and t3name for all rows in t1 joined with t2 and t3 on the id column. You want all of the rows in t1 to appear in the result no matter what is in t2 and t3. You only want records from t2 and t3 to appear if both t2 and t3 have a match on the specified join condition. An ANSI form of the desired query is:
SELECT t1.t1name, t2.t2name, t3.t3name
FROM t1 LEFT OUTER JOIN (t2 JOIN t3 ON t2.id = t3.id)
ON t1.id = t2.id;
In response to this query, the engine should first join t2 and t3 on the id column and then do an outer join of that result with t1. The output should be:
+----------+--------+--------+
| t1name | t2name | t3name |
+----------+--------+--------+
| t1_one | t2_one | t3_one |
| t1_two | NULL | NULL |
| t1_three | NULL | NULL |
+----------+--------+--------+
I have configured three commercial databases (Oracle 9i, Informix 9.2, FrontBase 3.6) with the sample tables shown above. All three of these databases return the expected result when given the sample query.
While MySQL accepts the ANSI form of the query, the results obtained are not consistent with the results from the other three SQL engines tested. Issuing the above query to MySQL will yield:
+----------+--------+--------+
| t1name | t2name | t3name |
+----------+--------+--------+
| t1_one | t2_one | t3_one |
| t1_two | t2_one | NULL |
| t1_three | t2_one | NULL |
| t1_one | t2_two | NULL |
| t1_two | t2_two | NULL |
| t1_three | t2_two | NULL |
+----------+--------+--------+
So, even though the query is accepted by the MySQL engine, the interpretation is quite different, and, I believe, incorrect.
How to repeat:
Configure sample database as described and issue query as described.
