MySQL Bugs: #20421: Strange behaviour of a server from the order of tables in FROM

Bug #20421	Strange behaviour of a server from the order of tables in FROM
Submitted:	13 Jun 2006 9:46	Modified:	13 Jun 2006 11:17
Reporter:	Valeriy Peshkoff	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	FreeBSD (FreeBSD 4.9-RELEASE)
Assigned to:		CPU Architecture:	Any

Description:
I've got error
#1054 - unknown column 'PT.ID' on 'on clause'
when query loooks like:

SELECT PT.ID, PT.AllowChangeData, PTPD.Data_id, T.ID, T.Title
FROM  PageTemplate PT, Templates T
LEFT JOIN PageTemplate_PageData PTPD on (PTPD.PageTemplate_id=PT.ID AND PTPD.page_id='1')
WHERE T.id=PT.ApplyTemplate_id AND PT.Parent_id=0 AND PT.OrderInTemplate=1 AND PT.AssemblyTemplate_id=7

When i change tables order on FROM clause, query return what i expect

SELECT PT.ID, PT.AllowChangeData, PTPD.Data_id, T.ID, T.Title
FROM  Templates T, PageTemplate PT
LEFT JOIN PageTemplate_PageData PTPD on (PTPD.PageTemplate_id=PT.ID AND PTPD.page_id='1')
WHERE T.id=PT.ApplyTemplate_id AND PT.Parent_id=0 AND PT.OrderInTemplate=1 AND PT.AssemblyTemplate_id=7

on 4.1.0-alpha-log it works in both variant

How to repeat:
show create table for used tables:

CREATE TABLE `PageTemplate` (
  `ID` int(11) NOT NULL auto_increment,
  `Parent_id` int(11) NOT NULL default '0',
  `AssemblyTemplate_id` int(11) NOT NULL default '0',
  `OrderInTemplate` smallint(6) NOT NULL default '0',
  `ApplyTemplate_id` int(11) NOT NULL default '0',
  `AllowChangeData` enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (`ID`)
) TYPE=ISAM CHARSET=latin1 PACK_KEYS=1

CREATE TABLE `Templates` (
  `ID` int(11) NOT NULL auto_increment,
  `Title` varchar(255) default NULL,
  `FileName` varchar(255) default NULL,
  `Lang_id` char(3) NOT NULL default 'ru',
  `TypeID` varchar(255) default NULL,
  `Body` text,
  PRIMARY KEY  (`ID`)
) TYPE=ISAM CHARSET=latin1 PACK_KEYS=1

CREATE TABLE `PageTemplate_PageData` (
  `Page_id` int(11) NOT NULL default '0',
  `PageTemplate_id` int(11) NOT NULL default '0',
  `Data_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Page_id`,`PageTemplate_id`)
) TYPE=ISAM CHARSET=latin1 PACK_KEYS=1

Suggested fix:
Maybe it's not a bug but a feature...

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:

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"- Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted 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.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator."

Looks like exactly your case described...