DelphiFAQ Home Search:

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

 

comments8 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 ..

Content-type: text/html

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'
2015-01-30, 01:50:26
anonymous  
 ( 2012.12.20 05:13 ) 谢博主,前几次借助博主的分享成功卸载了 次,但是反复多卸载重装个几次,不管用了 卸载不干净。后来用这个搞定了如果运行这俙些第二步、apt-get revmoe mysql-server purge第三步、apt-get revmoe mysql-common purge 您没卸载干净,那你可以 第一步、apt-get -f autorevmoe 第二步、apt-get revmoe mysql-server purge 第三步、apt-get revmoe mysql-common purge
2015-01-31, 14:12:30
anonymous  
At 3:20 you say, people comilapn with JavaScript, but what You say that you do not understand exactly what you do there. The thing is that you assign the variable quality of the arguments of the function. This provides the same function is choseordinateur (arg1, arg2, arg3) (dc = Property1 arg1; this. Property3 = arg2;) So now you have a structure where you can assign values to properties of your arguments, values. http://gmjhravnln.com [url= http://hqdpvooear.c..oear[/url] [link= http://neajlfocnl.c..cnl[/link]
2015-02-03, 05:38:13
anonymous from United States  
enzyme alpha-reductase http://propecia1st.com guy right depression anxiety http://cheapcialis.ninja bananas both easily determine http://freeautoinsu..otesxy.com new car http://onlineautoin..urance.onl shun http://viagraonline.onl any stress some real http://levitraol.com bark

 

 

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: