MySQL Bugs: #15297: Left join's ON clause recognizes only final table alias in FROM

Bug #15297	Left join's ON clause recognizes only final table alias in FROM
Submitted:	28 Nov 2005 20:28	Modified:	28 Nov 2005 20:45
Reporter:	Tom Price	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Windows (Win2K Sp4)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Consider the following query:

SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc
FROM estlvl a, estlvl b 
LEFT JOIN estuser c on a.nodeno = c.nodeno AND c.user = 'root'
WHERE a.parent=b.nodeno

This query works in MySQL 4.x but fails in MySQL 5.0.15 with the following error:

[ODBC 3.51 Driver][mysqld-5.0.15-nt]Unknown column 'a.nodeno' in 'on clause'  

A query utilizing the "b" table alias in the "on" clause does NOT produce an error (but also does not produce the needed result) i.e:

SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc, c.user
FROM estlvl a, estlvl b 
LEFT JOIN estuser c on b.nodeno = c.nodeno AND c.user = 'root'
WHERE a.parent=b.nodeno

A query reversing the table aliases in the "From" clause does NOT produce an error (and DOES produce the needed result!!) i.e:

SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc, c.user
FROM estlvl b, estlvl a 
LEFT JOIN estuser c on a.nodeno = c.nodeno AND c.user = 'root'
WHERE a.parent=b.nodeno

How to repeat:
1. Create tables estlvl and estuser as follows:

CREATE TABLE `estlvl` (
  `nodeno` int(11) unsigned NOT NULL,
  `parent` int(11) unsigned NOT NULL default '0',
  `nodedesc` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`nodeno`)
) ENGINE=MyISAM;

CREATE TABLE `estuser` (
  `nodeno` int(10) unsigned NOT NULL default '0',
  `user` char(16) character set latin1 collate latin1_bin NOT NULL default '',
  PRIMARY KEY  (`nodeno`,`user`)
) ENGINE=MyISAM;

2. Populate estlvl as follows:

insert into `estlvl` values (0,0,'Tree Root');
insert into `estlvl` values (1,0,'Parent');
insert into `estlvl` values (2,1,'Child');

3. Populate estuser as follows:

insert into `estuser` values (1,'root');
insert into `estuser` values (2,'root');

4. Execute query.

SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc, c.user
FROM estlvl a, estlvl b 
LEFT JOIN estuser c on a.nodeno = c.nodeno AND c.user = 'root'
WHERE a.parent=b.nodeno

Thank you for the bug report. Duplicate of bug:

http://bugs.mysql.com/bug.php?id=13551

Please read:

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html

#

Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with
USING, including outer join variants, are processed according to the SQL:2003
standard.
The changes include elimination of redundant output columns for NATURAL joins
and joins specified with a USING clause and proper ordering of output columns. (Bug#6136,Bug #6276,Bug #6489, Bug #6495, Bug #6558, Bug #9067, Bug #9978, Bug #10428,Bug #10646,Bug #10972.)
The precedence of the comma operator also now is lower compared to JOIN. (Bug
#4789,Bug #12065, Bug #13551.)

These changes make MySQL more compliant with standard SQL. However, they can
result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 13.2.7.1, “JOIN Syntax”.

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

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db9
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-debug

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

mysql> CREATE TABLE `estlvl` (
    ->   `nodeno` int(11) unsigned NOT NULL,
    ->   `parent` int(11) unsigned NOT NULL default '0',
    ->   `nodedesc` varchar(60) NOT NULL default '',
    ->   PRIMARY KEY  (`nodeno`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE `estuser` (
    ->   `nodeno` int(10) unsigned NOT NULL default '0',
    ->   `user` char(16) character set latin1 collate latin1_bin NOT NULL default '',
    ->   PRIMARY KEY  (`nodeno`,`user`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into `estlvl` values (0,0,'Tree Root');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `estlvl` values (1,0,'Parent');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `estlvl` values (2,1,'Child');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into `estuser` values (1,'root');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `estuser` values (2,'root');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc, c.user
    -> FROM estlvl a, estlvl b 
    -> LEFT JOIN estuser c on a.nodeno = c.nodeno AND c.user = 'root'
    -> WHERE a.parent=b.nodeno
    -> ;
ERROR 1054 (42S22): Unknown column 'a.nodeno' in 'on clause'

mysql> SELECT b.nodedesc as areaname, a.nodeno, a.nodedesc, c.user
    -> FROM (estlvl a, estlvl b) 
    -> LEFT JOIN estuser c on a.nodeno = c.nodeno AND c.user = 'root'
    -> WHERE a.parent=b.nodeno;
+-----------+--------+-----------+------+
| areaname  | nodeno | nodedesc  | user |
+-----------+--------+-----------+------+
| Tree Root |      0 | Tree Root | NULL |
| Tree Root |      1 | Parent    | root |
| Parent    |      2 | Child     | root |
+-----------+--------+-----------+------+
3 rows in set (0.00 sec)

mysql>