MySQL Bugs: #14633: Select Error: Unknown column 'm.id' in 'on clause'

Bug #14633	Select Error: Unknown column 'm.id' in 'on clause'
Submitted:	4 Nov 2005 10:49	Modified:	4 Nov 2005 11:15
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15-Debian_1-log	OS:	Linux (Debian Unstable)
Assigned to:		CPU Architecture:	Any

Description:
ERROR 1054 (42S22): Unknown column 'm.id' in 'on clause'

How to repeat:
DROP TABLE IF EXISTS g;
DROP TABLE IF EXISTS me;
DROP TABLE IF EXISTS m;
CREATE TABLE g (g_id int);
CREATE TABLE me (id int);
CREATE TABLE m (id int);
SELECT * FROM m, g LEFT JOIN me ON (m.id=me.id);

I see it's due to the following:
Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. 

Why was this change (that's not backwards compatible) made?

Thank you for a bug report. There were numerious bug reports on this very problem. You are right - this was an intentional change in behaviour, although, surprising to many users. It is not a bug.

It is described in the manual, by those quote you provided.

Why? To conform to the SQL 2003 Standard. That is the reason.

Is there an option to revert to the previous behaviour?