MySQL Bugs: #14567: SELECT fails according to table order in FROM claus

Bug #14567	SELECT fails according to table order in FROM claus
Submitted:	2 Nov 2005 10:11	Modified:	2 Nov 2005 10:44
Reporter:	NOT_FOUND NOT_FOUND	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15-nt	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
following sql statement fails with:
Unknown column 'aziende.uniqueid' in 'on clause'

SELECT 
	aziende.*, 
	app_esito.uniqueid EsUid 
FROM 
	aziende, esito 
LEFT JOIN 
	app_esito ON app_esito.uniqueid = aziende.uniqueid 
WHERE 
	1=1 
	AND aziende.esito_contatto = esito.esiti and codicedb = 'SUCCESSO' 
	AND ucase(ifnull(agenzia_albacom,''))='TELEMARK' 
	AND ucase(SuperEsito)='APPUNTAMENTO'

However it works if you either
(1) remove the prefix "aziende" from the LEFT JOIN ON ...
- or-
(2) invert the tables in the FROM clause (i.e. put "esito, aziende" instead of "aziende,esito")

ps
"UniqueID" is a field in the "aziende" table. as implied it is a auto-incrementing unique identifier..

The sql statment works fine in MySQL v. 4

How to repeat:
you would need to have my db.. can post if required..

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) carefully:

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

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

Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, 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);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN."

This is exactly your case. Moreover, there are a lot of similar bug reports already, so, please, use the search next time.

Thank you for your prompt answer, Valeriy..

I *did* try to search but it's very difficult to set up the keywords for this bug!

SELECT FAILS is too generic, UNKNOWN COLUMN also...

Anyway my compliments for the fantastic work you are all doing!