Bug #60201 „unknown column in on clause” with implicit cross join and inner join

• Submitted: 22 Feb 2011 10:15
• Modified: 22 Feb 2011 10:32
• Reporter: Christian Kruse
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S2 (Serious)
• Version: Ver 14.14 Distrib 5.1.50, 5.1.54
• OS: Any (Gentoo Linux, Mac OS x)
• CPU Architecture: Any
• Tags: column, cross join, implicit, INNER JOIN, ON clause, UNKNOWN

[22 Feb 2011 10:15] Christian Kruse

Description:
Given the three tables:

create table t1 (i integer not null);
create table t2 (i2 integer not null);
create table t3 (i3 integer not null);

Query the following statement:

select t1.i,t2.i2 from t2,t1 inner join t3 on t3.i3 = t2.i2;

The following error is raised:

ERROR 1054 (42S22): Unknown column 't2.i2' in 'on clause'

Using a left join instead of an implicit cross join succeeds:

select t1.i,t2.i2 from t2 left join t1 on 1=1 inner join t3 on t3.i3 = t2.i2;

An explicit cross join works, too:

select t1.i,t2.i2 from t2 cross join t1 inner join t3 on t3.i3 = t2.i2;

All the queries should be equivalent.

How to repeat:
create table t1 (i integer not null);
create table t2 (i2 integer not null);
create table t3 (i3 integer not null);

select t1.i,t2.i2 from t2,t1 inner join t3 on t3.i3 = t2.i2;

Suggested fix:
Do not raise an „unknown column” error

[22 Feb 2011 10:30] Christian Kruse

Ok, after talking to a friend of mine I now know that this is standard compliant behaviour. He explained me that there is no order defined for implicit joins and therefore the implicit join is not in the same join chain as the inner join and therefore access to the implicit joined table is restricted in the ON clause. Please just close the bug and ignore it :-)