Bug #16756 INNER JOIN order in query breaks SELECT
Submitted: 24 Jan 2006 19:57 Modified: 25 Jan 2006 0:54
Reporter: Joe Presbrey Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 + 5.1.5 OS:Linux (RHEL4)
Assigned to: CPU Architecture:Any

[24 Jan 2006 19:57] Joe Presbrey 
Description:
Running the following query against MySQL 5.0.18-0 incorrectly returns "unknown column".  I also tested this on 5.1.5-alpha-max-log to see if it had already been fixed but it failed there as well.  Switching the order of the INNER JOIN clauses fixes the bug in both releases.

SELECT Comments.CommentId, Item.ItemId FROM Comments
        INNER JOIN ItemMap ON Item.ItemId=ItemMap.Item2Id
        INNER JOIN Item ON Item.ItemId=Comments.ItemId;
ERROR 1054 (42S22): Unknown column 'Item.ItemId' in 'on clause'

Switiching the order of the INNER JOIN clauses fixes the bug:
SELECT Comments.CommentId, Item.ItemId FROM Comments
        INNER JOIN Item ON Item.ItemId=Comments.ItemId
        INNER JOIN ItemMap ON Item.ItemId=ItemMap.Item2Id;
Empty set (0.00 sec)

How to repeat:
CREATE TABLE `Item` ( `ItemId` int(11) NOT NULL );
CREATE TABLE `ItemMap` ( `Item1Id` int(11) NOT NULL default '0', `Item2Id` int(11) NOT NULL default '0');
CREATE TABLE `Comments` ( `CommentId` int(11) NOT NULL, `ItemId` int(11) NOT NULL default '0' );

SELECT Comments.CommentId, Item.ItemId FROM Comments
        INNER JOIN ItemMap ON Item.ItemId=ItemMap.Item2Id
        INNER JOIN Item ON Item.ItemId=Comments.ItemId;

ERROR 1054 (42S22): Unknown column 'Item.ItemId' in 'on clause'
[25 Jan 2006 0:54] MySQL Verification Team 
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 Section: http://dev.mysql.com/doc/refman/5.0/en/join.html
taking note about the changes introduced since version 5.0.12 for to be SQL
Standard compliance. The fix you had mentioned is the correct way for that
query.