MySQL Bugs: #38705: compound LEFT JOIN - ERROR 1054: Unknown column 'inMediaType' in 'on clause'

Bug #38705	compound LEFT JOIN - ERROR 1054: Unknown column 'inMediaType' in 'on clause'
Submitted:	10 Aug 2008 19:14	Modified:	10 Aug 2008 19:36
Reporter:	Mike Hunter	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.67-community-nt-log	OS:	Windows (2000 sp4)
Assigned to:		CPU Architecture:	Any
Tags:	left join

Description:
Trying to perform a compound JOIN and LEFT JOIN, but an error message is returned saying "Unknown column 'inMediaType' in 'on clause'" even though the field is declared as a key.

I had the same problem in v4.1, reported it, and you ended up fixing it in a later release.  The same query works in v4.1.22, and I would like to upgrade, but this is holding me back.  I've tried earlier versions of v5.0, but the same results.

When I take out the first JOIN, it works as expected.

[MySQL][ODBC 3.51 Driver][mysqld-5.0.51b-community-nt-log]Unknown column 'inMediaType' in 'on clause'
-------------------------------------------------------------------------------
From MySQL Query Browser: "Unknown column 'inMediaType' in 'on clause' [1054]"

From MySQL console:
-------------------------------------------------------------------------------
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.67-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use backupdb
Database changed
mysql> SELECT inVolID, inWriteDate, inReturnDate, inOSscanlabel, mtType, inDescription, plOther01
    ->   FROM Inventory, Pool
    ->     LEFT JOIN MediaTypes ON (inMediaType = mtIndex)
    ->   WHERE plOffsiteStorageIdx = 9
    ->     AND plEnabled = 'Y'
    ->     AND inPoolIdx = plPoolIdx
    ->     AND plRotateOffsite = 'Y'
    ->     AND inLocationIdx = 4
    ->   ORDER BY inReturnDate, inVolID;
ERROR 1054 (42S22): Unknown column 'inMediaType' in 'on clause'
mysql>
-------------------------------------------------------------------------------

#=============================================================================

mysql> SELECT inVolID, inWriteDate, inReturnDate, inOSscanlabel, mtType, inDescription
    ->   FROM Inventory
    ->     LEFT JOIN MediaTypes ON (inMediaType = mtIndex)
    ->   WHERE inLocationIdx = 4
    ->   ORDER BY inReturnDate, inVolID;
+---------+-------------+--------------+---------------+--------+--------------------------------------------------------+
| inVolID | inWriteDate | inReturnDate | inOSscanlabel | mtType | inDescription                                          |
+---------+-------------+--------------+---------------+--------+--------------------------------------------------------+
| A1200   | 2008-08-01  | 2008-08-16   | 011020014     | 8mm    | NULL                                                   |
| A1201   | 2008-08-01  | 2008-08-16   | 011020015     | 8mm    | NULL                                                   |
| A1202   | 2008-08-01  | 2008-08-16   | 011020016     | 8mm    | NULL                                                   |
| A1203   | 2008-08-01  | 2008-08-16   | 011020017     | 8mm    |                                                        |
| A1204   | 2008-08-01  | 2008-08-16   | 011020018     | 8mm    |                                                        |
| A1205   | 2008-08-01  | 2008-08-16   | 011020019     | 8mm    | Volume # 1  of  2,                 Set:  A1205,  A1206 |
| A1206   | 2008-08-01  | 2008-08-16   | 011020020     | 8mm    | Volume # 2  of  2,                 Set:  A1205,  A1206 |
| 030053  | 2008-07-26  | 2008-10-04   | 011020107     | LTO3   | NULL                                                   |
| 030054  | 2008-07-27  | 2008-10-04   | 011020108     | LTO3   | NULL                                                   |
| 030055  | 2008-07-30  | 2008-10-04   | 011020109     | LTO3   | NULL                                                   |
| 030056  | 2008-08-01  | 2008-10-04   | 011020110     | LTO3   | NULL                                                   |
| 020175  | 2008-07-27  | 2008-12-05   | NULL          | LTO3   | NULL                                                   |
| 020176  | 2008-07-28  | 2008-12-05   | NULL          | LTO3   | NULL                                                   |
| 020177  | 2008-07-29  | 2008-12-05   | NULL          | LTO3   | NULL                                                   |
| 020178  | 2008-07-27  | 2008-12-05   | NULL          | LTO3   | NULL                                                   |
| 020179  | 2008-07-31  | 2008-12-05   | NULL          | LTO3   | NULL                                                   |
+---------+-------------+--------------+---------------+--------+--------------------------------------------------------+
16 rows in set (0.22 sec)

mysql>

How to repeat:
Here are the SQL statements for the SELECT and table definitions:

SELECT inVolID, inWriteDate, inReturnDate, inOSscanlabel, mtType, inDescription, plOther01
  FROM Inventory, Pool
    LEFT JOIN MediaTypes ON (inMediaType = mtIndex)
  WHERE plOffsiteStorageIdx = 9
    AND plEnabled = 'Y'
    AND inPoolIdx = plPoolIdx
    AND plRotateOffsite = 'Y'
    AND inLocationIdx = 4
  ORDER BY inReturnDate, inVolID;

#==============================================================================

CREATE TABLE `inventory` (
  `inTapeIdx` int(11) NOT NULL auto_increment,
  `inVolID` varchar(30) default NULL,
  `inWriteDate` date default NULL,
  `inOffsiteDate` date default NULL,
  `inReturnDate` date default NULL,
  `inLocationIdx` int(11) default '11',
  `inSysID` int(11) NOT NULL default '0',
  `inSlot` tinyint(3) default '0',
  `inPoolIdx` int(11) NOT NULL default '0',
  `inNotes` mediumtext,
  `inNotesArNode` int(11) default '0',
  `inChangedBy` varchar(20) default NULL,
  `inChangedIP` varchar(50) default NULL,
  `inChangedDate` datetime default NULL,
  `inUsed` varchar(10) default NULL,
  `inPrevSlot` tinyint(3) default '0',
  `inRackNumber` int(11) default '0',
  `inDateAdded` datetime default NULL,
  `inManufacturer` varchar(80) default NULL,
  `inStorageWritten` varchar(10) default NULL,
  `inInternalVolID` varchar(20) default NULL,
  `inPurchasedFrom` varchar(50) default NULL,
  `inReplace` enum('Y','N') default 'N',
  `inHold` enum('Y','N') default 'N',
  `inRetire` enum('Y','N') default 'N',
  `inBadTapeCount` tinyint(3) unsigned default '0',
  `inLastBTCUpdate` datetime default NULL,
  `inMediaType` int(11) default '0',
  `inOSscanlabel` varchar(50) default NULL,
  `inDescription` varchar(100) default NULL,
  `inTapeContentsDate` datetime default NULL,
  `inTapeContents` mediumtext,
  PRIMARY KEY  (`inTapeIdx`),
  UNIQUE KEY `inVolID` (`inVolID`),
  KEY `inWriteDate` (`inWriteDate`),
  KEY `inOffsiteDate` (`inOffsiteDate`),
  KEY `inReturnDate` (`inReturnDate`),
  KEY `inLocationIdx` (`inLocationIdx`),
  KEY `inSysID` (`inSysID`),
  KEY `inPoolIdx` (`inPoolIdx`),
  KEY `inRackNumber` (`inRackNumber`),
  KEY `inDateAdded` (`inDateAdded`),
  KEY `inManufacturer` (`inManufacturer`),
  KEY `inReplace` (`inReplace`),
  KEY `inHold` (`inHold`),
  KEY `inMediaType` (`inMediaType`),
  KEY `inOSscanlabel` (`inOSscanlabel`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#------------------------------------------------------------------------------

CREATE TABLE `pool` (
  `plPoolIdx` int(11) NOT NULL auto_increment,
  `plPoolName` varchar(15) NOT NULL default '',
  `plPoolDescr` varchar(255) default NULL,
  `plPoolSysID` int(11) NOT NULL default '0',
  `plDaysOffsite` smallint(5) unsigned default '0',
  `plTapeRetension` smallint(5) unsigned default '0',
  `plTextOverRetension` varchar(10) NOT NULL default '',
  `plDaysPull` smallint(5) unsigned default '0',
  `plWkDayPickedUp` tinyint(3) unsigned default '0',
  `plOffsiteStorageIdx` int(11) default NULL,
  `plContactIdx` int(11) default NULL,
  `plMediaType` int(11) default '0',
  `plLastUpdated` datetime default NULL,
  `plLastUpdatedByUser` varchar(20) default '',
  `plEnabled` enum('N','Y') default 'Y',
  `plAutoProcess` enum('N','Y') default 'N',
  `plOffsiteRotationType` enum('D','W','N') default 'D',
  `plOffsiteNumberOfDays` smallint(5) unsigned default '0',
  `plWhenFinalizeTapes` enum('N','S') default 'N',
  `plWeeklyProcessSubmit` datetime default NULL,
  `plWeeklyProcessSchedDate` datetime default NULL,
  `plWeeklyProcess` datetime default NULL,
  `plWeeklyProcessByUser` varchar(50) default NULL,
  `plWeeklyProcessUID` varchar(19) default '',
  `plFinalizeProcessSubmit` datetime default NULL,
  `plFinalizeProcess` datetime default NULL,
  `plFinalizeProcessByUser` varchar(50) default NULL,
  `plFinalizeProcessUID` varchar(19) default '',
  `plSetPrefix` enum('N','Y') default 'N',
  `plPrefix` varchar(5) default '',
  `plHidePrefix` enum('N','Y') default 'N',
  `plSetPostfix` enum('N','Y') default 'N',
  `plPostfix` varchar(5) default '',
  `plHidePostfix` enum('N','Y') default 'N',
  `plRotateOffsite` enum('N','Y') default 'Y',
  `plNotifyOffsite` enum('N','Y') default 'N',
  `plNotifyOffsiteEmail` mediumtext,
  `plNotifyOffsiteBody` mediumtext,
  `plOther01` varchar(50) default '',
  `plOther02` varchar(50) default '',
  `plOther03` varchar(50) default '',
  `plLifeCycle` tinyint(3) unsigned default '5',
  `plLifeCycleAction` text,
  `plBillStorageOverage` enum('N','Y') default 'Y',
  `plNotes` mediumtext,
  `plNotesArNode` int(11) default '0',
  `plMinScratch` tinyint(3) unsigned default '10',
  PRIMARY KEY  (`plPoolIdx`),
  KEY `plPoolName` (`plPoolName`),
  KEY `plPoolSysID` (`plPoolSysID`),
  KEY `plOffsiteStorageIdx` (`plOffsiteStorageIdx`),
  KEY `plContactIdx` (`plContactIdx`),
  KEY `plEnabled` (`plEnabled`),
  KEY `plOffsiteRotationType` (`plOffsiteRotationType`),
  KEY `plMediaType` (`plMediaType`),
  KEY `plBillStorageOverage` (`plBillStorageOverage`),
  KEY `plAutoProcess` (`plAutoProcess`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#------------------------------------------------------------------------------

CREATE TABLE `mediatypes` (
  `mtIndex` int(11) NOT NULL auto_increment,
  `mtType` varchar(8) NOT NULL default '',
  `mtDescription` varchar(50) NOT NULL default '',
  `mtRawSize` varchar(10) default NULL,
  `mtCompressedSize` varchar(10) default NULL,
  `mtMinimumSize` varchar(10) default NULL,
  PRIMARY KEY  (`mtIndex`),
  UNIQUE KEY `mtType` (`mtType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank you for the bug report. Could you please read the Manual regarding JOIN syntax introduced since 5.0.12 version (see below FROM clause):

mysql 6.0 > SELECT inVolID, inWriteDate, inReturnDate, inOSscanlabel, mtType, inDescription,
    -> plOther01
    -> FROM Inventory, Pool
    -> LEFT JOIN MediaTypes ON (inMediaType = mtIndex)
    -> WHERE plOffsiteStorageIdx = 9
    -> AND plEnabled = 'Y'
    -> AND inPoolIdx = plPoolIdx
    -> AND plRotateOffsite = 'Y'
    -> AND inLocationIdx = 4
    -> ORDER BY inReturnDate, inVolID;
ERROR 1054 (42S22): Unknown column 'inMediaType' in 'on clause'
mysql 6.0 > SELECT inVolID, inWriteDate, inReturnDate, inOSscanlabel, mtType, inDescription,
    -> plOther01
    -> FROM (Inventory, Pool)
    -> LEFT JOIN MediaTypes ON (inMediaType = mtIndex)
    -> WHERE plOffsiteStorageIdx = 9
    -> AND plEnabled = 'Y'
    -> AND inPoolIdx = plPoolIdx
    -> AND plRotateOffsite = 'Y'
    -> AND inLocationIdx = 4
    -> ORDER BY inReturnDate, inVolID;
Empty set (0.09 sec)