Bug #15606 MySQL5 reports unknown column XXX in 'on clause' when column exists

• Submitted: 8 Dec 2005 23:14
• Modified: 8 Dec 2005 23:42
• Reporter: Daniel Grace
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.16-standard-log
• OS: Linux (Linux 2.4.21 (RHAS3))
• CPU Architecture: Any

[8 Dec 2005 23:14] Daniel Grace

Description:
Running the following query on MySQL 5.0.16 produces the error "Error #1054: Unknown column 's.id' in 'on clause'"

SELECT 1 FROM student s, slp_calendar scal, subject LEFT JOIN slp_commentary sc ON sc.sid=s.id LIMIT 1;

But on MySQL 4.1.15-standard-log using an identical table structure, the query executes correctly.

If slp_calendar and subject are removed from the query, it executes correctly on 5.0.16:

SELECT 1 FROM student s  LEFT JOIN slp_commentary sc ON sc.sid=s.id LIMIT 1;

--> 1

How to repeat:
/* Table structure */
CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL auto_increment,
`flags` int(10) unsigned NOT NULL default '0',
`sid` varchar(32) default NULL,
`fid` int(10) unsigned NOT NULL default '0',
`enrollinfo` int(10) unsigned NOT NULL default '0',
`alloc_override` float(24,2) default NULL,
`fname` varchar(64) NOT NULL default '',
`lname` varchar(64) NOT NULL default '',
`mname` varchar(64) NOT NULL default '',
`fte` tinyint(3) unsigned NOT NULL default '100',
`location` varchar(32) NOT NULL default '',
`type` enum('normal','running start') NOT NULL default 'normal',
`birthdate` date NOT NULL default '0000-00-00',
`ethnic` varchar(4) NOT NULL default '',
`district` varchar(48) NOT NULL default '',
`gender` enum('','M','F') NOT NULL default '',
`comments` mediumtext,
`tid` int(10) unsigned NOT NULL default '0',
`slpid` int(10) unsigned NOT NULL default '0',
`groups` mediumtext NOT NULL,
`cedate` date default NULL,
PRIMARY KEY (`id`),
KEY `flags` (`flags`),
KEY `fid` (`fid`),
KEY `enrollinfo` (`enrollinfo`),
KEY `location` (`location`),
KEY `type` (`type`),
KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Students';

CREATE TABLE `slp_calendar` (
`id` int(10) unsigned NOT NULL auto_increment,
`year` smallint(5) unsigned NOT NULL default '0',
`start` date NOT NULL default '0000-00-00',
`end` date NOT NULL default '0000-00-00',
`submittable` date NOT NULL default '0000-00-00',
`due` date NOT NULL default '0000-00-00',
`name` varchar(64) NOT NULL default '',
`abbv` varchar(16) NOT NULL default '',
`persubject` tinyint(3) unsigned NOT NULL default '0',
`progressmonth` date default NULL,
`slp_template` mediumtext NOT NULL,
`slp_info` mediumtext NOT NULL,
`slp_template_order` char(2) NOT NULL default 'c',
`slp_info_order` char(2) NOT NULL default 'c',
`track_contact_methods` tinyint(3) unsigned NOT NULL default '0',
`track_resources` tinyint(3) unsigned NOT NULL default '0',
`track_hours` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `year_start_end` (`year`,`start`,`end`),
KEY `year_end_start` (`year`,`end`,`start`),
KEY `year_due_submittable` (`year`,`due`,`submittable`),
KEY `year_submittable_due` (`year`,`submittable`,`due`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `subject` (
`id` int(10) unsigned NOT NULL auto_increment,
`flags` int(10) unsigned NOT NULL default '0',
`year` int(10) unsigned NOT NULL default '0',
`subj` varchar(32) NOT NULL default '',
`slp_info` mediumtext NOT NULL,
`slp_header` mediumtext NOT NULL,
`slp_footer` mediumtext NOT NULL,
`slp_template` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `year` (`year`,`subj`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `slp_commentary` (
`id` int(10) unsigned NOT NULL auto_increment,
`flags` int(10) unsigned NOT NULL default '0',
`sid` int(10) unsigned NOT NULL default '0',
`tid` int(10) unsigned NOT NULL default '0',
`year` smallint(5) unsigned NOT NULL default '0',
`_d_review` int(10) unsigned NOT NULL default '0',
`subj` varchar(32) NOT NULL default '',
`submitted` date default NULL,
`approved` date default NULL,
`comments` mediumtext NOT NULL,
`ccomments` mediumtext NOT NULL,
`scalid` int(10) unsigned NOT NULL default '0',
`hbi_progress` enum('Y','N') default NULL,
`overall_progress` enum('Y','N') default NULL,
`contact_methods` mediumtext NOT NULL,
`resources` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `review_match` (`sid`,`scalid`,`subj`),
KEY `sid` (`sid`,`year`,`subj`),
KEY `scalid` (`scalid`),
KEY `consultant_match` (`year`,`tid`,`submitted`,`approved`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='45-day reviews';

/* Query fails */
SELECT 1 FROM student s, slp_calendar scal, subject LEFT JOIN slp_commentary sc ON sc.sid=s.id LIMIT 1;

/* Query succeeds */
SELECT 1 FROM student s  LEFT JOIN slp_commentary sc ON sc.sid=s.id LIMIT 1;

[8 Dec 2005 23:42] 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 see the Manual about the join syntax, from version 5.0.12 that behavior
was changed, see the release changes notes for that version.

mysql> SELECT 1 FROM student s, slp_calendar scal, subject LEFT JOIN slp_commentary sc
    -> ON sc.sid=s.id LIMIT 1;
ERROR 1054 (42S22): Unknown column 's.id' in 'on clause'
mysql> SELECT 1 FROM (student s, slp_calendar scal, subject) LEFT JOIN slp_commentary sc
    -> ON sc.sid=s.id LIMIT 1;
Empty set (0.00 sec)