MySQL Bugs: #13832: "Unknown column 't1.a' in 'on clause" once again

Bug #13832	"Unknown column 't1.a' in 'on clause" once again
Submitted:	7 Oct 2005 9:01	Modified:	27 Oct 2005 2:30
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.14	OS:	Any
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
this is ok:

mysql> select * from t1 join t2 join t3 on (t1.a=t3.c);
Empty set (0.00 sec)

this is not:

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

How to repeat:
create table t1 (a int);
create table t2 (b int);
create table t3 (c int);
select * from t1 join t2 join t3 on (t1.a=t3.c);
select * from t1 join t2 left join t3 on (t1.a=t3.c);

I've come across similar with 5.0.13, which happens regardless of "join" or "left join":

SELECT
    *
    FROM    Orders AS o,
            Workflow AS wf
             JOIN XFCustomers AS xcu ON xcu.cu_id = o.cu_id
    GROUP BY  o.or_id
    LIMIT 0, 20;

Since this bug is already reported, I did not bother reducing this any further to a complete test case.

I'll add a couple of other similar test cases, verified on 5.0.15-BK (ChangeSet@1.2028.1.1, 2005-10-10 21:42:14+02:00, lars@mysql.com):

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int);
creQuery OK, 0 rows affected (0.01 sec)

mysql> create table t2 (b int);
creQuery OK, 0 rows affected (0.01 sec)

mysql> create table t3 (c int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1 join t2 join t3 on (t1.a=t3.c);
Empty set (0.00 sec)

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

So, similar messages for all kinds of joins we get...

The last example comes from http://bugs.mysql.com/bug.php?id=13890. I'll mark it a duplicate of this one.

Changing the order of the joins helps:

SELECT * FROM t2 join t1 left join t3 on t1.a = t3.c;

works (in 5.0.13).

Bug 13890 seems to be fixed in the 5.0 source tree.

Valeriy's last test contains a typo that results in confusing results:

select * from t1 right join (t2 righ join t3 on t2.b=t3.c) on t1.a = t3.c;

Inside the parentheses, "right" is misspelled as "righ". MySQL interprets this as an alias for t2, and thus the attempt to reference t2.b in the ON clause fails. Correcting the typo gives this result in 5.0.13:

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

And it gives the following result in a more recent version of 5.0 (built from source tree):

select * from t1 right join (t2 right join t3 on t2.b=t3.c) on t1.a = t3.c;
Empty set (0.00 sec)

So that case, which appears to be unrelated to the original test case for this bug, at least has been fixed.

-----------------------------------------------------------------

On another note, some clarification is in order:

The two statements below are quite different from one another:
1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c
2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c

Statement (1) above will likely continue to give an "Unknown column 't1.a' in 'on clause'" error, while statement (2) will likely function correctly at some point in the future.

This bug report is in reference to statements like statement (2), and no bug reports that use a statement like statement (1) are duplicates of this bug.

-----------------------------------------------------------------

With regards to William Finn's comment that changing the order of the joins helps, this is true but is a misleading solution. Essentially William's query becomes the following:

SELECT * FROM t2 join (t1 left join t3 on t1.a = t3.c)

This is different than what is intended by the original query:

SELECT * FROM (t1 JOIN t2) LEFT JOIN t3 ON t1.a = t3.c

Which is still different than what the server is *actually* trying to do:

SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON t1.a = t3.c)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31378

Other items as discussed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31427

Fixed in 5.0.16.

Noted in 5.0.16 changelog.

I'm not so sure this problem has been completely resolved. I have a Mysql 4.0.25 Database where the following works:

select * from  
TaskStatus as ts, 
Task as t, 
Repository as r, 
Source as s, 
SourceType as st, 
TaskType as tt, 
Media as m, 
Shipment as p, 
MediaType as mt  
LEFT JOIN Task_TaskStatus AS tts  ON (tts.taskStatusID = 125.0) AND (tts.taskID=t.ID) WHERE r.ID=t.repositoryID  AND m.mediaTypeID = mt.ID  and tt.ID=t.taskTypeID  and m.ID=t.mediaID  and m.shipmentID=p.ID  and s.ID=t.sourceID  and s.sourceTypeID=st.ID  and ts.ID=t.taskStatusID  and t.ID IN (14965, 14966, 14967, -1)  and t.isDel=0 and t.ID<>1  and r.caseID=2106 limit 1;

In 5.0.15 running this same query gives me this error:
ERROR 1054 (42S22): Unknown column 't.ID' in 'on clause'

I have tried building from source with the patch specified in this article as well as with the LATEST Bitkeeper Source (as of 11/17/05) and this select still throws the Error 1054. Any ideas?

David,

If you read the comments in this bug report, your join clearly falls into the category of statements that are affected by recent changed to the optimizer. Your query is expected not to work as written, because of the order of tables in the FROM clause and the use of a LEFT JOIN, which binds more tightly than the comma.

This bug refers to a different scenario, where joins specified using explicit keywords such as [INNER|CROSS|LEFT] JOIN.

Error 1054 is expected from your query.

David,

See this section of the manual for more information about changes
in join processing:

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

A bug like this still exists in 5.0.16-nt, but I'm not sure it is really a bug or a change in the precedence evaluation of joins between MySQL 4.1 and MySQL 5.x

This code works on the former but not on the latter:

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

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

This problem can be overcame by writing the FROM part between brackets:
mysql> select * from (t1,t3) left join t2 on (a=b);

or by reordering the tables so as the last one is the one which has the fields that are used in the ON clause:

mysql> select * from t3,t1 left join t2 on (a=b);

I wonder how is the standard precedence relations for a JOIN.

Sergio Lerner.

Hi Sergio,

your last example is not a bug. This behavior is according to
the SQL standard. According to the standard, the comma
operator has lower precedence than any of the JOIN variants.
Thus the query:
select * from t1, t2 join t3 on a=b;
actually means:
select * from t1, (t2 join t3 on a=b);
Since (according to ANSI SQL) column names in an ON
condition are resolved against the join operands (t2, t3 in
this case), then the first query above must produce a
name resolution error, because there is no column 'a' in
tables t2, t3.

On the other hand the query:
select * from (t1, t2) join t3 on a=b;
means that the whole cross-product (t1, t2) is the left
join operand. Consequently column 'a' in the ON
condition can be resolved against the table (t1, t2).

All this is described in the latest 5.0 manual:
http://dev.mysql.com/doc/refman/5.0/en/join.html

Hi guys,

I just want to thank you for the solution of this bug (I mean the William Finn's idea of changing the order of the tables, it works fine in MySQL 5.0.x).

I was messing with this error for some four days, thinking I was absolutely going mad cos the query seemed okay. Then I found this and I was ashamed how easy the solution was (by the way, man, how did you find this out...?)

Once more thanks, you have done a good job.

I had a similar problem and I found a very simple fix for this issue:

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

Solution:

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

It works for me; should work for you. 

It is not a bug. Now MySQL 5 is in compliance with some rules of SQL that I'm not very familiar with. By adding the parenthesis we are giving priority to the implicit join expressed by the comma; after that JOIN, LEFT JOIN, RIGHT JOIN works the same way it used to work in previous version.

Hi friends,

I have migrated from MySQL 4.1 to 5.1 and got some problems with queries running on my costumer's system. That system is so big and maybe this costumer's will give up to keep going with migration process. 

The developer team will not to want rewrite all system queries and I need some help in this case. I try to use old system variable but I've read on the MySQL's manual of that variable only affects the way of MySQL treats index bahaviors and sql hints. 

Do you know about other MySQL's feature that acepts this behavior of 4.1 version in 5.1?

Best regards 4 all.

Hello Mr. Wagner Bianchi,

The purpose of this incompatible change was to fix a problem
where MySQL was interpreting incorrectly the semantics of JOIN ON
and JOIN USING queries.

As described in previous posts above, the change is described in detail
in our documentation:
http://dev.mysql.com/doc/refman/5.0/en/join.html

The reason there is no way to tell the system to switch to the old 4.1
behavior is because in general 4.1 queries may produce wrong result.
Things may work correctly, but this is a coincidence. Thus, if your
developers are happy with wrong results from JOIN ON, they can stick to
4.1, otherwise it will be wiser to inspect your JOIN ON queries an
change them to be compatible with 5.1.

Best regards,
Timour

Hi Timour, how are you today?

I am thankfull for your attention and I want to apologize myself for the delay in answering your last note. Well, in that old case, the guys engaged on that JOIN ON's problem had decided to rewrite all system queries. Now all things are business as usual (BAU) and we had migrated from MySQL 4.1 to 5.1.

Best regards.