MySQL Bugs: #37925: Select with comma join pretends a column does not exist

Bug #37925	Select with comma join pretends a column does not exist
Submitted:	7 Jul 2008 12:21	Modified:	7 Jul 2008 14:10
Reporter:	Arjen Lentz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	column, comma, join, parser

Description:
The query in the "how to repeat" section returns an error "user.contactID does not exist, unknown column" when clearly the column does exist in the user table. When replacing the join comma with the JOIN keyword, the query works fine.

How to repeat:
--
-- Table structure for table `contact`
--

DROP TABLE IF EXISTS `contact`;
CREATE TABLE `contact` (
  `id` int(11) NOT NULL auto_increment,
  `salutation` varchar(10) default NULL,
  `firstName` text,
  `lastName` text,
  `emailAddress` text,
  `phone` varchar(255) default NULL,
  `mobile` varchar(255) default NULL,
  `position` text,
  `mailingListSubscription` int(11) default NULL,
  `status` varchar(255) default NULL,
  `companyID` int(11) default NULL,
  `companySiteID` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=511 DEFAULT CHARSET=utf8;

--
-- Table structure for table `company`
--

DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `id` int(11) NOT NULL auto_increment,
  `company` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=743 DEFAULT CHARSET=utf8;

--
-- Table structure for table `companysite`
--

DROP TABLE IF EXISTS `companysite`;
CREATE TABLE `companysite` (
  `id` int(11) NOT NULL auto_increment,
  `companyID` int(11) default NULL,
  `phone` varchar(255) default NULL,
  `streetAddress` text,
  `suburb` text,
  `state` text,
  `postcode` text,
  `country` text,
  `fax` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=437 DEFAULT CHARSET=utf8;

--
-- Table structure for table `custom_user`
--

DROP TABLE IF EXISTS `custom_user`;
CREATE TABLE `custom_user` (
  `id` int(11) NOT NULL auto_increment,
  `contactID` int(11) default NULL,
  `isTeacher` tinyint(4) default '0',
  `teacherStatus` varchar(50) default NULL,
  `website` varchar(255) default NULL,
  `area` text,
  `detailOrder` smallint(6) default '10',
  `pageNameLink` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=403 DEFAULT CHARSET=utf8;

--
-- Table structure for table `custom_state`
--

DROP TABLE IF EXISTS `custom_state`;
CREATE TABLE `custom_state` (
  `id` int(11) NOT NULL auto_increment,
  `countryID` int(11) default NULL,
  `stateName` varchar(255) default NULL,
  `shortName` varchar(128) default NULL,
  `tax` double(9,2) default NULL,
  `zone` smallint(6) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `contactID` int(11) NOT NULL default '0',
  `active` int(11) default NULL,
  `addSection` tinyint(1) default NULL,
  `allowUserAccessEditing` int(11) default NULL,
  `lockAccessDetails` int(11) default NULL,
  `locked` int(11) default NULL,
  `loginPassword` text,
  `loginUsername` varchar(255) default NULL,
  `passwordReset` int(11) default NULL,
  `addSectionAssign` tinyint(1) default NULL,
  `addIncludesAssign` tinyint(1) default NULL,
  `addIncludes` tinyint(1) default NULL,
  `allowUserSwitching` tinyint(4) default NULL,
  `editUserDetails` tinyint(1) default NULL,
  `editUserDetailsAssign` tinyint(1) default NULL,
  `editUserGroups` int(11) default NULL,
  `editUserGroupsAssign` int(11) default NULL,
  `editUserPrivileges` tinyint(1) default NULL,
  `editUserPrivilegesAssign` tinyint(1) default NULL,
  `editSiteSettings` tinyint(1) default NULL,
  `editSiteSettingsAssign` tinyint(1) default NULL,
  `viewStats` tinyint(1) default NULL,
  `viewStatsAssign` tinyint(1) default NULL,
  `komodoUser` tinyint(1) default NULL,
  `exportStaticPages` tinyint(1) default NULL,
  `exportStaticPagesAssign` tinyint(1) default NULL,
  `editTemplates` tinyint(1) default NULL,
  `editTemplatesAssign` tinyint(1) default NULL,
  `productManagement` tinyint(1) default NULL,
  `productManagementAssign` tinyint(1) default NULL,
  `kToolbarShowCheckboxes` tinyint(1) default NULL,
  `kToolbarShowTables` tinyint(1) default NULL,
  `kToolbarShowLinks` tinyint(1) default NULL,
  `kToolbarSpellChecking` tinyint(1) default NULL,
  `kToolbarShowLockedItems` tinyint(1) default NULL,
  `kToolbarShowDraftItems` tinyint(1) default NULL,
  `kToolbarShowDateBasedItems` tinyint(1) default NULL,
  `designer` tinyint(1) default NULL,
  `designerAssign` tinyint(1) default NULL,
  `isUserGroup` tinyint(1) default NULL,
  `userGroupID` int(11) default NULL,
  `displayTos` tinyint(1) default NULL,
  `tosDisplayReason` text,
  `tosLastAcceptedDate` varchar(14) default NULL,
  `tosLastAcceptedVersion` varchar(14) default NULL,
  KEY `user_loginUsername` (`loginUsername`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# no table data needed

# now for the query that exhibits the problem:

SELECT *, contact.phone AS personalPhone, contact.companyID AS contactCompanyID
FROM user, contact
LEFT JOIN company ON contact.companyID=company.id
LEFT JOIN companysite ON company.id=companysite.companyID
LEFT JOIN custom_user ON user.contactID=custom_user.contactID
LEFT OUTER JOIN custom_state ON custom_state.stateName=companysite.state
WHERE
contact.id=user.contactID
AND custom_user.isTeacher='1'
AND user.active=1
AND custom_user.pageNameLink='blah'
LIMIT 1

# same query just with JOIN rather than comma is accepted by the parser.

SELECT *, contact.phone AS personalPhone, contact.companyID AS contactCompanyID
FROM user JOIN contact
LEFT JOIN company ON contact.companyID=company.id
LEFT JOIN companysite ON company.id=companysite.companyID
LEFT JOIN custom_user ON user.contactID=custom_user.contactID
LEFT OUTER JOIN custom_state ON custom_state.stateName=companysite.state
WHERE
contact.id=user.contactID
AND custom_user.isTeacher='1'
AND user.active=1
AND custom_user.pageNameLink='blah'
LIMIT 1

Suggested fix:
Probable parser bug.

Possibly a regression, the query works in 4.x (an existing production app was migrated from 4.x to 5.0, then triggering this error)

Being aware of
 - http://bugs.mysql.com/13551
 - http://bugs.mysql.com/14817

and the information from the Docs (http://dev.mysql.com/doc/refman/5.0/en/join.html):
'However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.'
[...]
'Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted 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.'

[end of quotes]

That still leaves us with a unclear error reporting. Technically it may be correct based on parser internals, but users should not need to care about such black magic.

Thank you for the bug report. From 5.0.12 the join syntax was changed according SQL:2003 standard. Please see:

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

see below the change in FROM clause:

mysql 5.0 > # now for the query that exhibits the problem:
mysql 5.0 >
mysql 5.0 > SELECT *, contact.phone AS personalPhone, contact.companyID AS contactCompanyID
    -> FROM user, contact
    -> LEFT JOIN company ON contact.companyID=company.id
    -> LEFT JOIN companysite ON company.id=companysite.companyID
    -> LEFT JOIN custom_user ON user.contactID=custom_user.contactID
    -> LEFT OUTER JOIN custom_state ON custom_state.stateName=companysite.state
    -> WHERE
    -> contact.id=user.contactID
    -> AND custom_user.isTeacher='1'
    -> AND user.active=1
    -> AND custom_user.pageNameLink='blah'
    -> LIMIT 1;
ERROR 1054 (42S22): Unknown column 'user.contactID' in 'on clause'
mysql 5.0 > # now for the query that exhibits the problem:
mysql 5.0 >
mysql 5.0 > SELECT *, contact.phone AS personalPhone, contact.companyID AS contactCompanyID
    -> FROM (user, contact)
    -> LEFT JOIN company ON contact.companyID=company.id
    -> LEFT JOIN companysite ON company.id=companysite.companyID
    -> LEFT JOIN custom_user ON user.contactID=custom_user.contactID
    -> LEFT OUTER JOIN custom_state ON custom_state.stateName=companysite.state
    -> WHERE
    -> contact.id=user.contactID
    -> AND custom_user.isTeacher='1'
    -> AND user.active=1
    -> AND custom_user.pageNameLink='blah'
    -> LIMIT 1;
Empty set (0.03 sec)