MySQL Bugs: #20684: After upgrade from 4.1.20 to 5.0.22, my query stop working

Bug #20684	After upgrade from 4.1.20 to 5.0.22, my query stop working
Submitted:	25 Jun 2006 14:08	Modified:	25 Jun 2006 20:51
Reporter:	IVO GELOV	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL-max-5.0.22-i686-glibc23	OS:	Linux (Fedora Core 4)
Assigned to:		CPU Architecture:	Any

Description:
I have a query with several LEFT JOINs. Here it is:
SELECT HOLDS.*,NAME,M_SKIN.M_SKIN,M_SUP.M_SUP,M_BOLT.M_BOLT,TYPE_TEXT.TYPE_TEXT,TYPE_FRICT.TYPE_FRICT,TYPE_SHAPE.TYPE_SHAPE,TYPE_BOLT.TYPE_BOLT,TYPE_BOX.TYPE_BOX,TYPE_BAG.TYPE_BAG,PICT FROM HOLDS,GRID LEFT JOIN M_SKIN ON M_SKIN.ID=HOLDS.M_SKIN LEFT JOIN M_SUP ON M_SUP.ID=HOLDS.M_SUP LEFT JOIN M_BOLT ON M_BOLT.ID=HOLDS.M_BOLT LEFT JOIN TYPE_TEXT ON TYPE_TEXT.ID=TEXTURE LEFT JOIN TYPE_FRICT ON TYPE_FRICT.ID=HOLDS.TYPE_FRICT LEFT JOIN TYPE_SHAPE ON TYPE_SHAPE.ID=HOLDS.TYPE_SHAPE LEFT JOIN TYPE_BOLT ON TYPE_BOLT.ID=HOLDS.TYPE_BOLT LEFT JOIN TYPE_BOX ON TYPE_BOX.ID=HOLDS.TYPE_BOX LEFT JOIN TYPE_BAG ON TYPE_BAG.ID=HOLDS.TYPE_BAG WHERE GRID.ID=FIRMA
It works fine in 4.1.12, 4.1.17, 4.1.20, but strangely stop working when I upgraded to 5.0.22. The error is:
Unknown column 'HOLDS.M_SKIN' in 'on clause'

How to repeat:
I can send dump of my tables by email, if you need them.
The problem persists on all of my MySQL installations:
5.0.19 on Windows XP SP2
5.0.22 on Fedora Core 2
5.0.22 on Fedora Core 4

Suggested fix:
I tried to exchange left and right parts of ON clauses - it worked for M_SKIN, M_SUP, M_BOLT but did not worked for TEXTURE - it says "Unknown column TEXTURE" no matter left or right of the "=" sign it is written.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (especially section 13.2.7.1. JOIN Syntax). Since 5.0.12:

...
FROM HOLDS,GRID LEFT JOIN M_SKIN ON M_SKIN.ID=HOLDS.M_SKIN 

is treated differently than before (and strictly according to the SQL 2003 Standard). It is clearly documented in the manual:

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

Thank you, Valeriy.
It is my fault that I have not read the manual !
Excuse me for losing your time and thanks a lot
for your good explanations.
Bye.