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
MySQL 5.0.13.rc returns an error message "Unknown column 'a.code' in 'on clause'" even when the column "a.code" exists when the table definition (a) is not the last table in the FROM clause table list.

Used to work in 4.1.11

How to repeat:
Taking a simple table :

  `code` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`code`)

The query : 

SELECT  a.code, b.code, c.code
  FROM t1 a, t1 b
  LEFT JOIN t1 c ON c.code = b.code

works properly, but

SELECT  a.code, b.code, c.code
  FROM t1 a, t1 b
  LEFT JOIN t1 c ON c.code = a.code

returns "Unknown column 'a.code' in 'on clause'".

Suggested fix:
Changing the order of tables in the FROM clause (not sure that works if multiple LEFT JOINs)
[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;"