Bug #16651 MySQL 5.0.15 - cannot find table in ON clause statement
Submitted: 19 Jan 2006 20:53 Modified: 20 Jan 2006 10:04
Reporter: Adrian Samson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Linux (linux)
Assigned to: CPU Architecture:Any

[19 Jan 2006 20:53] Adrian Samson
Description:
MySQL 5.0.16 has the following bug:
The following code is not working with MySQL 5.0.16 but it is working very well with MySQL 5.0.9:

SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
LEFT JOIN table3 t3 ON t1.col1=t3.col3 WHERE ...

MySQL 5.0.16 give the error = cannot find t1.col1 in ON clause

How to repeat:
SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
LEFT JOIN table3 t3 ON t1.col1=t3.col3 WHERE ...

Suggested fix:
To make the code above work with MySQL 5.0.16 we need to switch the order of table1 and table2 in the from statement as follows:

SELECT t1.col1, t2.col2 FROM table2 t2, table1 t1
LEFT JOIN table3 t3 ON t1.col1=t3.col3 WHERE ...
[20 Jan 2006 10:04] Valeriy Kravchuk
Sorry, but it is not a bug, but intended and documented change in behaviour. Please, read the manual carefully (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"- Before MySQL 5.0.12, 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);

Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, 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);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN."