MySQL Bugs: #19053: #1054 - Unknown column in 'on clause'

Bug #19053	#1054 - Unknown column in 'on clause'
Submitted:	12 Apr 2006 14:40	Modified:	12 Apr 2006 16:44
Reporter:	Hernan X	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Windows (Win32)
Assigned to:		CPU Architecture:	Any

Description:
Hi, It looks like when i do a LEFT JOIN, i can only referer in the ON Clause to the  two tables i am joining, and not to any other one.

I don't know if the SQL is optimum but it looks correct, and it worked in 4.1 and it is not woking in 5.0

Thanks,

MySQL Version: Ver 14.12 Distrib 5.0.19, for Win32 (ia32)

How to repeat:
CREATE TABLE `a` (
  `id` mediumint(9) NOT NULL auto_increment,
  `foo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `b` (
  `id` mediumint(9) NOT NULL auto_increment,
  `foo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `c` (
  `id` mediumint(9) NOT NULL auto_increment,
  `foo` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

; only 4.x compatible
SELECT a.*, b.*, c.* FROM a, b LEFT JOIN c ON (c.id = a.id) WHERE a.id = b.id; 
;error in 5.x
; #1054 - Unknown column 'a.id' in 'on clause' 

; 4.x and 5.x compatible
SELECT a.*, b.*, c.* FROM a, b LEFT JOIN c ON (c.id = b.id) WHERE a.id = b.id; 

Suggested fix:
refer to table i am joining to (even though this is not a fix, because i will have to rewrite all the SQL Queries)

Thank you for the bug report. Please see the Manual regarding join
syntax it was introduced changes from 5.0.13 version for to be 
compatible with SQL Standard.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT a.*, b.*, c.* FROM a, b LEFT JOIN c
    -> ON (c.id = a.id) WHERE a.id = b.id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'
mysql>
mysql> SELECT a.*, b.*, c.* FROM (a, b) LEFT JOIN c
    -> ON (c.id = a.id) WHERE a.id = b.id;
Empty set (0.00 sec)

see FROM clause.

Ok. here's how to solve this and an excerpt from the Documentation.

if you want to know how to make that query compatible
read

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

    *

      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.

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

that is. we have to use () in the FROM.

Thanks for the fast response on fixing this bug.