Bug #16190 Unknown column in 'on clause'
Submitted: 4 Jan 2006 15:36 Modified: 4 Jan 2006 15:55
Reporter: Igor Rud Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18 OS:Linux (Red Hat 9)
Assigned to: MySQL Verification Team CPU Architecture:Any

[4 Jan 2006 15:36] Igor Rud
Description:
I am trying to construct the following query: 1-st and 2-nd tables are joined; after that left join operation is applied to 3-rd table. In this situation fields of 1-st table are not visible in 'on clause' (fields of 2-nd table are visible).

How to repeat:
CREATE TABLE additional_information (
  subscriber_id bigint(20) NOT NULL,
  info varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO additional_information VALUES (1,'info1');
INSERT INTO additional_information VALUES (2,'info2');
INSERT INTO additional_information VALUES (3,'info3');

CREATE TABLE messages (
  message_id bigint(20) NOT NULL,
  subscriber_id bigint(20) NOT NULL,
  message varchar(50) default NULL,
  PRIMARY KEY  (message_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO messages VALUES (1,1,'test1');
INSERT INTO messages VALUES (2,2,'test2');
INSERT INTO messages VALUES (3,3,'test3');

CREATE TABLE subscribers (
  id bigint(20) NOT NULL,
  `name` varchar(50) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO subscribers VALUES (1,'John');
INSERT INTO subscribers VALUES (2,'Denis');
INSERT INTO subscribers VALUES (3,'Ron');

select * from subscribers a, messages b left join additional_information c on a.id=c.subscriber_id where a.id=b.subscriber_id;
ERROR 1054: Unknown column 'a.id' in 'on clause'

select * from subscribers a, messages b left join additional_information c on id=c.subscriber_id where a.id=b.subscriber_id;
ERROR 1054: Unknown column 'id' in 'on clause'

select * from subscribers a, messages b left join additional_information c on a.name='John' where a.id=b.subscriber_id;
ERROR 1054: Unknown column 'a.name' in 'on clause'

select * from subscribers a, messages b left join additional_information c on name='John' where a.id=b.subscriber_id;
ERROR 1054: Unknown column 'name' in 'on clause'

The same query works without giving any error in 4.0.18 and 4.1.11
[4 Jan 2006 15:55] MySQL Verification Team
Please read:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Where you will notice changes performed in join's syntax since
5.0.12.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug

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

mysql> select * from subscribers a, messages b left join additional_information c on
    -> a.id=c.subscriber_id where a.id=b.subscriber_id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'
mysql> select * from (subscribers a, messages b) left join additional_information c on
    -> a.id=c.subscriber_id where a.id=b.subscriber_id;
+----+-------+------------+---------------+---------+---------------+-------+
| id | name  | message_id | subscriber_id | message | subscriber_id | info  |
+----+-------+------------+---------------+---------+---------------+-------+
|  1 | John  |          1 |             1 | test1   |             1 | info1 |
|  2 | Denis |          2 |             2 | test2   |             2 | info2 |
|  3 | Ron   |          3 |             3 | test3   |             3 | info3 |
+----+-------+------------+---------------+---------+---------------+-------+
3 rows in set (0.01 sec)