Bug #24623 Bug in LEFTJOIN ON-Statement
Submitted: 27 Nov 2006 16:07 Modified: 27 Nov 2006 19:55
Reporter: Dirk Ruetz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.27-community OS:
Assigned to: CPU Architecture:Any

[27 Nov 2006 16:07] Dirk Ruetz
Description:
MySQL results the error
#1054 - Unknown column 'xxcmstable.area' in 'on clause'

But the field is available

The statement works fine unter 4.1.x, e.g.  4.1.11-standard

If I cut the line 

How to repeat:
SELECT DISTINCT `xxcmstable`. * 
FROM `content_1` AS `xxcmstable` , `input_field` AS `xxrelfield` 
LEFT JOIN `input_field` AS `xxifield_title_13` ON `xxifield_title_13`.`name` = 'title'
AND `xxifield_title_13`.`area` = `xxcmstable`.`area` 
LIMIT 0 , 100 

Suggested fix:
ON statement doesn't interpret the tablename rewrite AS (?)
[27 Nov 2006 19:55] MySQL Verification Team
Thank you for the bug report. Please read:

http://dev.mysql.com/doc/refman/5.0/en/join.html

 Join Processing Changes in MySQL 5.0.12

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard. 
.....