MySQL Bugs: #13734: LEFT JOIN producing strange error message

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13.c	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
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 :

CREATE TABLE `t1` (
  `code` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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)

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;"