Bug #13734 | LEFT JOIN producing strange error message | ||
---|---|---|---|
Submitted: | 4 Oct 2005 10:04 | Modified: | 4 Oct 2005 10:24 |
Reporter: | Fred Testard | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.13.c | OS: | Windows (WinXP) |
Assigned to: | CPU Architecture: | Any |
[4 Oct 2005 10:04]
Fred Testard
[4 Oct 2005 10:24]
Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: JOIN behaviour was changed in 5.0.12 to conform to SQL: 2003 standard. See http://dev.mysql.com/doc/mysql/en/upgrading-from-4-1.html for details: "Note: As of MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. This change may necessitate that certain queries be rewritten. For example, the following query will work as written before 5.0.12, but as of 5.0.12 will fail with an Unknown column 't1.id' in 'on clause' error: SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id; To rewrite the query, use parentheses to group the tables in the inner join: SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id; For that particular query, it is also possible to rewrite it as a natural join: SELECT t1.id,t2.id,t3.id FROM t1,t2 NATURAL LEFT JOIN t3 WHERE t1.id=t2.id;"