MySQL Bugs: #17495: INNER JOIN ON Clause 'Unkown Column' error

Bug #17495	INNER JOIN ON Clause 'Unkown Column' error
Submitted:	16 Feb 2006 21:37	Modified:	16 Feb 2006 22:51
Reporter:	Kim Albee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Linux - Fedora Core 3)
Assigned to:		CPU Architecture:	Any

Description:
Looked like this should be fixed in an early december release - referring to bug #15229 that appeared to have a fix that would be distributed in early Dec., But the bug in this case is still present.

select distinct a.* from a, b inner join c on(a.id=c.b_fk and (c.name like '%UN'));

INNER JOIN sql with "on" clause gets SQL Error: ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'

This same query works in mySQL 4.0.22.

How to repeat:
here is how this can be recreated:

mysql> create temporary table a(id int, name varchar(20));
mysql> create temporary table b(b_id int, name varchar(20));
mysql> create temporary table c(c_id int, b_fk int, name varchar(30));

then run this query:

select distinct a.* from a, b inner join c on(a.id=c.b_fk and (c.name like '%UN'));

This exact same sequence can be run (and works) in mySQL version 4.0.22.

Than you for the bug report. Please read:

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

Note: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard. The following list provides more detail about several effects of the 5.0.12 change in join processing. The term “previously” means “prior to MySQL 5.0.12.”

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

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

mysql> create temporary table a(id int, name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table b(b_id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table c(c_id int, b_fk int, name varchar(30));
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select distinct a.* from a, b inner join c on(a.id=c.b_fk and (c.name like
    -> '%UN'));
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'
mysql> 
mysql> select distinct a.* from (a, b) inner join c on(a.id=c.b_fk and (c.name like
    -> '%UN'));
Empty set (0.00 sec)

The page i was referred to (http://dev.mysql.com/doc/refman/5.0/en/join.html) does not even have an example of the issue I describe and how to fix it-- in fact, one of the comments on the page shows the same syntax I am using on my inner join.

Please provide more information if this is not a bug as to how INNER JOIN's should be formatted.  Use my *simple* example and correct it so it works in 5.0.18, please.  It would be greatly appreciated.

Please see my last lines showing you as it works on 5.0.XX:

mysql> select distinct a.* from (a, b) inner join c on(a.id=c.b_fk and (c.name
like
    -> '%UN'));
Empty set (0.00 sec)

notice: from (a, b)