DelphiFAQ Home Search:

mysql message ERROR 1054 (42S22): Unknown column in 'on clause'

 

comments5 comments. Current rating: 5 stars (3 votes). Leave comments and/ or rate it.

Question:

I migrated a mysql application from mysql 4 to mysql 5. I found that some queries with left outer joins do not work any more.

Imagine 3 tables t1,t2,t3 with attributes t1.a, t2.b and t3.c

The following query worked in mysql 4, is valid SQL and does not work in mysql 5:


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


Answer:

This is a known bug in mysql 5. The work around is to specify t1 join t2 instead of using the , (comma).

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

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

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

select * from t1 join t2 join t3 on t1.a=t3.c;
//.. this works ..

select * from t1 join t2 left outer join t3 on t1.a=t3.c;
//.. this works ..

Comments:

2007-01-10, 20:52:24
anonymous from United States  
I had the same problem and the fix is very simple.

All you have to do is adding parentheses to the implicit join after FROM

select * from (t1, t2) join t3 on t1.a=t3.c;
select * from (t1, t2) left outer join t3 on t1.a=t3.c;

It should work.

This is because MySQL is now in compliance with some new rules of SQL language. I have seen some other solutions but I believe this is the easier.
2007-07-09, 12:24:29
anonymous from United States  
rating
2008-05-30, 14:50:21   (updated: 2008-05-30, 14:51:23)
anonymous from Chile  
Muchas Gracias por la info.
Thank you for the info.
:D
2008-11-06, 12:37:08
anonymous from United States  
rating
You saved the day! My webhosting environment was upgraded and a google search took me here. Bravo! I wonder why mysql is now requiring parenthesis in 5.X? Weird.
2012-02-23, 07:43:00
anonymous  
rating
Thanks kindly. I confirm the adding parantheses helps to solve the issue when MySQL gives the error message ERROR 1054 (42S22): Unknown column 'on clause'

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: