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

Bug #35242	ERROR 1054 (42S22): Unknown column 't2.i2' in 'on clause'
Submitted:	12 Mar 2008 10:29	Modified:	22 Jan 2020 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

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
 	 	) ;

It does not work in windows (MySQL 5.0.51a) as well

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

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.

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"

Bug #35455 was marked as duplicate of this one.

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?

Still broken in 5.6.10

Same query syntax works fine in PostgreSQL, SQLite3, ...

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.

Fixed in MySQL 8.0.20 together with BUG#96946.

See same for docs info.

Closed.