MySQL Bugs: #15800: Join causes Unknown column ... in 'on clause'

Bug #15800	Join causes Unknown column ... in 'on clause'
Submitted:	16 Dec 2005 2:22	Modified:	16 Dec 2005 2:31
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The code:

SELECT  products.name
FROM products, classifications
LEFT JOIN group_control_map
ON group_control_map.membercontrol = 3
   AND group_id NOT IN(6,3,10,4,1,7,14,13,2,5,12,8)
   AND group_control_map.product_id = products.id
WHERE group_id IS NULL
      AND classifications.id = products.classification_id
      AND classifications.name = 'Unclassified'
ORDER BY name

Causes syntax errors (Unknown column 'products.id' in 'on clause').  The following query works as a replacement for the above...

SELECT  products.name
FROM products
LEFT JOIN classifications
ON classifications.id = products.classification_id
   AND classifications.name = 'Unclassified'
LEFT JOIN group_control_map
ON group_control_map.membercontrol = 3
   AND group_id NOT IN(6,3,10,4,1,7,14,13,2,5,12,8)
   AND group_control_map.product_id = products.id
WHERE group_id IS NULL
ORDER BY name

This may be a duplicate of another similar bug, but I'm not sure.  I'd rather report it and let you make that call.  A similar bug has been filed in the Bugzilla database for tracking purpoases.

How to repeat:
See above.

Suggested fix:
Unknown.

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). It
is an intended change in 5.0.12 to become closer to the standard:

"Beginning with MySQL 5.0.12, natural joins and joins with USING, including
outer join variants, are processed according to the SQL:2003 standard. Redundant
output columns specified in USING clauses are eliminated, and output columns are
ordered correctly.

This means that some queries that appeared to work correctly prior to 5.0.12
must be rewritten to comply with the standard. For example, a query of the form

SELECT t1.id,t2.id,t3.id 
    FROM t1,t2 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;

will be interpreted in MySQL 5.0.12 and later as

SELECT t1.id,t2.id,t3.id 
    FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id)  )
    WHERE t1.id=t2.id;

Such a query must now be written like so:

SELECT t1.id,t2.id,t3.id 
    FROM (t1,t2) 
    LEFT JOIN t3 ON (t3.id=t1.id)
    WHERE t1.id=t2.id;"