Bug #15952 LEFT JOIN incorrectly reporting 'Unknown column'

• Submitted: 23 Dec 2005 5:59
• Modified: 23 Dec 2005 9:29
• Reporter: a p
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.17
• OS: Linux (Linux Red Hat EL)
• CPU Architecture: Any

[23 Dec 2005 5:59] a p

Description:
Previously working (in MySQL 4) queries that use LEFT JOIN are now failing with MySQL 5.

It appears that the order of tables as they are specified in the FROM clause affects their visibility in the context of the LEFT JOIN. If the tables arent visible, the query fails with an error similar to the following:

ERROR 1054 (42S22): Unknown column 'mos_xgm.img_id' in 'on clause'

Specific details follow.

How to repeat:
Compare this query:

SELECT
	COUNT(1)
FROM
	mos_xgm,
	mos_xgm_img
	LEFT JOIN mos_xgm_publish ON (
	 mos_xgm.img_id = mos_xgm_publish.mp_xgm_id)
WHERE
	mos_xgm.img_id = mos_xgm_img.iim_galid

fails with "ERROR 1054 (42S22): Unknown column 'mos_xgm.img_id' in 'on clause'".

The query does not fail if I change the order of tables in the FROM clause:

SELECT
	COUNT(1)
FROM
	mos_xgm_img,
	mos_xgm
	LEFT JOIN mos_xgm_publish ON (
	 mos_xgm.img_id = mos_xgm_publish.mp_xgm_id)
WHERE
	mos_xgm.img_id = mos_xgm_img.iim_galid

Create statements for the relevant tables:

 CREATE TABLE `mos_xgm` (
  `img_id` int(11) NOT NULL auto_increment,
  `img_date_start` date default NULL,
  `img_date_finish` date default NULL,
  `img_ref` varchar(255) default NULL,
  `img_title_internal` varchar(255) default NULL,
  `img_2257` text NOT NULL,
  `img_2257_short` text NOT NULL,
  `img_notes` text,
  `img_group` varchar(255) default NULL,
  `img_pending` int(11) NOT NULL default '0',
  `img_template` int(11) default NULL,
  `img_params` text,
  PRIMARY KEY  (`img_id`),
  KEY `img_group` (`img_group`),
  FULLTEXT KEY `ft1` (`img_ref`,`img_title_internal`,`img_2257`,`img_2257_short`,`img_notes`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

|CREATE TABLE `mos_xgm_img` (
  `iim_id` int(11) NOT NULL auto_increment,
  `parent_iim_id` int(11) default NULL,
  `iim_galid` int(11) default '0',
  `iim_fullname` varchar(255) default NULL,
  `iim_published` int(11) NOT NULL default '1',
  `iim_order` int(11) default NULL,
  `iim_title` varchar(255) default NULL,
  `iim_description` text,
  `iim_content_type` varchar(255) default NULL,
  `iim_md5` varchar(255) default NULL,
  `iim_thumb_x` int(11) default NULL,
  `iim_thumb_y` int(11) default NULL,
  `iim_thumb_key` varchar(255) default NULL,
  `iim_thumb_addext` varchar(255) default NULL,
  `iim_thumbsmall_x` int(11) default NULL,
  `iim_thumbsmall_y` int(11) default NULL,
  `iim_thumbsmall_key` varchar(255) default NULL,
  `iim_thumbsmall_addext` varchar(255) default NULL,
  `iim_thumbvideo_x` int(11) default NULL,
  `iim_thumbvideo_y` int(11) default NULL,
  `iim_thumbvideo_key` varchar(255) default NULL,
  `iim_thumbvideo_addext` varchar(255) default NULL,
  `iim_small_x` int(11) default NULL,
  `iim_small_y` int(11) default NULL,
  `iim_small_key` varchar(255) default NULL,
  `iim_small_addext` varchar(255) default NULL,
  `iim_medium_x` int(11) default NULL,
  `iim_medium_y` int(11) default NULL,
  `iim_medium_key` varchar(255) default NULL,
  `iim_medium_addext` varchar(255) default NULL,
  `iim_large_x` int(11) default NULL,
  `iim_large_y` int(11) default NULL,
  `iim_large_key` varchar(255) default NULL,
  `iim_large_addext` varchar(255) default NULL,
  `iim_full_x` int(11) default NULL,
  `iim_full_y` int(11) default NULL,
  `iim_full_key` varchar(255) default NULL,
  `iim_full_addext` varchar(255) default NULL,
  `iim_access` int(11) default '0',
  `iim_rating` int(11) default NULL,
  `iim_render_headline` int(11) default '0',
  `iim_render_preview` int(11) default '0',
  `iim_render_article` int(11) default '1',
  `iim_date_imported` datetime NOT NULL default '0000-00-00 00:00:00',
  `iim_refreshed` int(11) default NULL,
  `iim_params` text,
  PRIMARY KEY  (`iim_id`),
  KEY `iim_galid` (`iim_galid`),
  KEY `parent_iim_id` (`parent_iim_id`),
  KEY `galleryquery` (`iim_galid`,`iim_published`,`iim_rating`,`iim_render_preview`,`iim_render_headline`,`iim_render_article`),
  KEY `iim_md5` (`iim_md5`,`iim_id`),
  FULLTEXT KEY `ft1` (`iim_fullname`,`iim_title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

 CREATE TABLE `mos_xgm_publish` (
  `mp_id` int(11) NOT NULL auto_increment,
  `mp_xgm_id` int(11) NOT NULL default '0',
  `mp_xgm_id_type` int(11) NOT NULL default '0',
  `mp_mos_content_id` int(11) NOT NULL default '0',
  `mp_publish_type` int(11) NOT NULL default '0',
  `mp_publish_primary` int(11) NOT NULL default '0',
  PRIMARY KEY  (`mp_id`),
  UNIQUE KEY `c1` (`mp_xgm_id`,`mp_xgm_id_type`,`mp_mos_content_id`),
  KEY `xgm` (`mp_xgm_id`,`mp_xgm_id_type`,`mp_publish_primary`),
  KEY `mos_content` (`mp_mos_content_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

[23 Dec 2005 9:29] Valeriy Kravchuk

Thank you for a problem report. Sorry, but it looks like not a bug, but documented change in behaviour since 5.0.12. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted 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.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN."

It is your case, exactly.