MySQL Bugs: #15779: Unknown column ... in 'on clause'

Bug #15779	Unknown column ... in 'on clause'
Submitted:	15 Dec 2005 17:29	Modified:	15 Dec 2005 17:39
Reporter:	Julian Griffiths	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Windows & Unix)
Assigned to:		CPU Architecture:	Any

Description:
Similar (or same as) bug #13832

Selecting columns from tables with a left join produces a "Unknown column tbl.col in 'on clause'" error

This error occurs on both Windows and Unix systems.

How to repeat:
This is the shortest code that I have produced to show the problem 

create table t1 (a int);
create table t2 (b int);
create table t3 (c int);

SELECT t1.a FROM t1, t2
LEFT JOIN t3 ON t1.a=t3.c

This also causes the error,

SELECT t1.a FROM t1, t2
LEFT JOIN t3 ON t1.a=t3.c
LEFT JOIN t2 app ON t1.a=app.b

Suggested fix:
A workaround is not to have an implicit inner join [from t1, t2], but to explicitly join the tables.  This isn't ideal if you have a large system...

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 release notes for 5.0.12 regarding the join syntax
and also read the Manual about.

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

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

5.0.18>SELECT t1.a FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'
5.0.18>SELECT t1.a FROM (t1, t2) LEFT JOIN t3 ON t1.a=t3.c;
Empty set (0.00 sec)

Sounds like you're running into the 5.0.12 join changes described
here:

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

For example, changing t1, t2 to (t1, t2) causes the error
message to go away.