Bug #35242 ERROR 1054 (42S22): Unknown column 't2.i2' in 'on clause'
Submitted: 12 Mar 2008 10:29 Modified: 22 Jan 4:38
Reporter: corne smiesing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.45 and 5.0.51a, 5.0, 5.1, 6.0 BK OS:Any (and windows)
Assigned to: CPU Architecture:Any
Tags: exist, INNER JOIN, SELECT
Triage: Triaged: D2 (Serious)

[12 Mar 2008 10:29] corne smiesing
Description:
using a table in a subquery inner join statement doesn't work because the table cannot be found. The example explains itself.
there is a work around using a where.

SELECT t1.i1
 FROM t1
 INNER JOIN t2 ON t2.i2 = t1.i1
 AND exists (
 	SELECT 1 FROM t3
 	 INNER JOIN t4 ON t4.i4 = t3.i3 AND t2.i2 = t3.i3
 	 	) ;

t2 can not be found, resulting in a mysql error:
ERROR 1054 (42S22): Unknown column 't2.i2' in 'on clause'

How to repeat:
create table t1 (i1 int);
create table t2 (i2 int );
create table t3 (i3 int );
create table t4 (i4 int );

doesn't work in 5.0.45:

SELECT t1.i1
 FROM t1
 INNER JOIN t2 ON t2.i2 = t1.i1
 AND exists (
 	SELECT 1 FROM t3
 	 INNER JOIN t4 ON t4.i4 = t3.i3 AND t2.i2 = t3.i3
 	 	) ;
 	 	
Does work in 5.0.45:

SELECT t1.i1
 FROM t1
 INNER JOIN t2 ON t2.i2 = t1.i1
 AND exists (
 	SELECT 1 FROM t3
 	 INNER JOIN t4 ON t4.i4 = t3.i3 
  WHERE t2.i2 = t3.i3
 	 	) ;
[12 Mar 2008 11:58] corne smiesing
It does not work in windows (MySQL 5.0.51a) as well
[12 Mar 2008 13:08] 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 since "Join Processing Changes in MySQL 5.0.12" at http://dev.mysql.com/doc/refman/5.0/en/join.html
[12 Mar 2008 14:36] corne smiesing
Reading the information about the new join handling doesn't make clear to me that there is something wrong with my SQL statement. I am not sure it is correct either because it is not a simple part to read.
[14 Mar 2008 11:21] Sveta Smirnova
Thank you for the feedback.

I thought as follows.

There is explanation in manual:

> # Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
>
> Example:
>
> CREATE TABLE t1 (i1 INT, j1 INT);
> CREATE TABLE t2 (i2 INT, j2 INT);
> CREATE TABLE t3 (i3 INT, j3 INT);
> INSERT INTO t1 VALUES(1,1);
> INSERT INTO t2 VALUES(1,1);
> INSERT INTO t3 VALUES(1,1);
> SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
>
> Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:
>
> SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
>
> Alternatively, avoid the use of the comma operator and use JOIN instead:
>
> SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
>
> This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

So it could be this case. And this change was really introduced in version 5.0.12

But in your case, there is reference to outer query, so syntax can be correct. So I change status of the report to "Verified"
[22 Mar 2008 8:05] Sveta Smirnova
Bug #35455 was marked as duplicate of this one.
[7 Apr 2008 16:40] kris gale
i confirm this behavior as well, which reared its ugly head when going back to make changes to code on my localhost (laptop) running mysql 5.1.19 ; the production environment is mysql 4.1.12

here's the query that brought me here:

SELECT a.id, a.w, s.id AS sctn, s.verbose, au.name_, au.email_, a.headline_, a.kicker_, a.text_, a.detail_, a.caption_ FROM pwx_article a, pwx_section s LEFT JOIN pwx_author au ON (au.id = a.author_id) WHERE s.id = a.section_id AND a.id = 80 LIMIT 1

in a nutshell, i've got articles by section and optionally assigned an author so the author can be null / not present.

this works fine on mysql 4 but as of mysql 5 i see the same error message as the person who filed this bug report... in my case:

Unknown column 'a.author_id' in 'on clause'

now, you may claim this is not a 'bug' (to 'fix' this i would change my FROM clause to '...FROM (pwx_article a, pwx_section s) LEFT JOIN...') i have to beg the question as to whether or not this change in parser behavior is still compliant with ANSI SQL.

is it?
[12 Mar 2013 11:19] Hartmut Holzgraefe
Still broken in 5.6.10

Same query syntax works fine in PostgreSQL, SQLite3, ...
[7 Apr 2017 17:05] Andew Thornton
We've hit this bug as well.  Our statements are generated by an ORM, so not so easy to fix.
Any chance it will ever be fixed?  Other SQL Servers seem fine with such a query.
[22 Jan 4:38] Jon Stephens
Fixed in MySQL 8.0.20 together with BUG#96946.

See same for docs info.

Closed.