Bug #34482 Unknown column on LEFT JOIN when using 2 tables in FROM clause
Submitted: 12 Feb 2008 7:08 Modified: 12 Feb 2008 12:53
Reporter: Daniel DeLorme Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.0.51a-log OS:Linux (ubuntu 6.06)
Assigned to: CPU Architecture:Any
Tags: left join, Unknown column

[12 Feb 2008 7:08] Daniel DeLorme
Description:
In a query where the FROM clause contains 2 or more tables, it seems like only the last table can be referenced in the JOIN clauses. Trying to do a LEFT JOIN using a column from the first table(s) results in a 'Unknown column' parse error.

How to repeat:
create table t1 (a int);
create table t2 (b int);
create table t3 (c int);

select * from t1, t2 left join t3 on t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'

Note that it works when the tables are inversed:
select * from t2, t1 left join t3 on t1.a=t3.c;
Empty set (0.00 sec)
[12 Feb 2008 10:49] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about how JOIN works since version 5.0.12 at http://dev.mysql.com/doc/refman/5.0/en/join.html
[12 Feb 2008 12:45] Daniel DeLorme
My apologies; I never expected that the JOIN syntax had changed so much in 5.0
[12 Feb 2008 12:53] Daniel DeLorme
In my defense I *did* search the bug database.

You might want to set "Not a bug" as selected by default when searching for bugs.