MySQL Bugs: #14458: problem with JOINs when there are more than 1 table in FROM

Bug #14458	problem with JOINs when there are more than 1 table in FROM
Submitted:	29 Oct 2005 6:39	Modified:	29 Oct 2005 16:14
Reporter:	Walery Studennikov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Linux (Linux (Fedora Core 4))
Assigned to:		CPU Architecture:	Any

Description:
I'm using MySQL-server-5.0.15-0.glibc23.i386.rpm under Fedora Core 4.

There are problems with joins in select statements when there are more than 1 table in FROM clause.

Some SELECT statements with JOINS failed to run.
See example below:

How to repeat:
CREATE TABLE `testaa` (
  `id` int(11) NOT NULL default '0',
  `name` char(64) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `testaa` VALUES (1,'Alligator Descartes'),(3,'Jochen Wiedmann'),(2,'Tim Bunce'),(4,'Andreas K?nig'),(5,NULL),(6,'?'),(7,'?');

CREATE TABLE `testac` (
  `object_id` int(11) default NULL,
  `object_title` varchar(64) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `testac` VALUES (162,'test');

CREATE TABLE `testae` (
  `object_id` int(11) default NULL,
  `object_title` varchar(64) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `testae` VALUES (162,'test');

select * from testaa aa, testac ac join testae ae on ae.object_id=aa.id where aa.id=ac.object_id;

--> ERROR 1054 (42S22): Unknown column 'aa.id' in 'on clause'

I can confirm - I've hit the same bug in cacti.net application in simple query:

select *
from graph_local,graph_templates_graph
left join graph_templates on graph_local.graph_template_id=graph_templates.id
 where graph_local.id=graph_templates_graph.local_graph_id

Unknown column 'graph_local.graph_template_id' in on clause

mysql 5.0.15 on PLD/Linux.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html) carefully:

"Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was intrepreted 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."