Bug #16820 'Unknown column' error in LEFT JOIN caused by additional tables in WHERE clause
Submitted: 26 Jan 2006 20:59 Modified: 26 Jan 2006 21:28
Reporter: Andrei Nazarenko Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18-max OS:Linux (SuSE Linux 10.0)
Assigned to: Miguel Solorzano CPU Architecture:Any

[26 Jan 2006 20:59] Andrei Nazarenko
Description:
When performing a LEFT JOIN and there are more than 1 tables present in the WHERE clause, the query fails with an error "Unknown column", although the column does exist.

The issue is best illustrated with the following testcase.

How to repeat:
CREATE DATABASE test;

USE test;

CREATE TABLE my_case (
  objid int(11) unsigned NOT NULL default '0',
  id int(11) unsigned NOT NULL default '0',
  title varchar(90) collate latin1_german2_ci NOT NULL default '',
  case2type int(11) unsigned NOT NULL default '0',
  case2attr int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (objid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE my_attr (
  objid int(11) unsigned NOT NULL default '0',
  attr1 varchar(7) collate latin1_german2_ci NOT NULL default '',
  attr2 varchar(30) collate latin1_german2_ci NOT NULL default '',
  attr3 varchar(10) collate latin1_german2_ci NOT NULL default '',
  PRIMARY KEY  (objid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

CREATE TABLE my_type (
  objid int(11) unsigned NOT NULL default '0',
  ctype varchar(50) collate latin1_german2_ci NOT NULL default '',
  PRIMARY KEY  (objid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO my_case VALUES (1, 5000, 'Testing', 1, 1);
INSERT INTO my_attr VALUES (1, 'value1', 'value2', 'value3');
INSERT INTO my_type VALUES (1, 'Type1');

-------------------------------------------------------------------------------------------

Now try the following query:

SELECT DISTINCT
			my_case.*,
			my_attr.*,
			my_type.*
		FROM
			my_case,
			my_type
		LEFT JOIN
			my_attr
		ON
			my_attr.objid = my_case.case2attr
		WHERE
			my_case.case2type = my_type.objid
			AND my_case.id > 1000

It fails with the following message:

#1054 - Unknown column 'my_case.case2attr' in 'on clause' 

If you try one of these two queries:

SELECT DISTINCT
			my_case.*,
			my_attr.*
		FROM
			my_case
		LEFT JOIN
			my_attr
		ON
			my_attr.objid = my_case.case2attr
		WHERE
			my_case.id > 1000

or 

SELECT DISTINCT
			my_case.*,
			my_type.*
		FROM
			my_case,
			my_type
		WHERE
			my_case.case2type = my_type.objid
			AND my_case.id > 1000

Suggested fix:
None
[26 Jan 2006 21:25] Miguel Solorzano
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 about the join syntax, it was introduced since
version 5.0.12 changes for to be SQL Standard compliance:

mysql> SELECT DISTINCT
    -> my_case.*,
    -> my_attr.*,
    -> my_type.*
    -> FROM
    -> my_case,
    -> my_type
    -> LEFT JOIN
    -> my_attr
    -> ON
    -> my_attr.objid = my_case.case2attr
    -> WHERE
    -> my_case.case2type = my_type.objid
    -> AND my_case.id > 1000;
ERROR 1054 (42S22): Unknown column 'my_case.case2attr' in 'on clause'
mysql> SELECT DISTINCT
    -> my_case.*,
    -> my_attr.*,
    -> my_type.*
    -> FROM
    -> (my_case,
    -> my_type)
    -> LEFT JOIN
    -> my_attr
    -> ON
    -> my_attr.objid = my_case.case2attr
    -> WHERE
    -> my_case.case2type = my_type.objid
    -> AND my_case.id > 1000;
+-------+------+---------+-----------+-----------+-------+--------+--------+--------+-------+-------+
| objid | id   | title   | case2type | case2attr | objid | attr1  | attr2  | attr3  | objid | ctype |
+-------+------+---------+-----------+-----------+-------+--------+--------+--------+-------+-------+
|     1 | 5000 | Testing |         1 |         1 |     1 | value1 | value2 | value3 |     1 | Type1 |
+-------+------+---------+-----------+-----------+-------+--------+--------+--------+-------+-------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.00 sec)
[26 Jan 2006 21:28] Andrei Nazarenko
Sorry for a false alarm. Thanks for a speedy response!