Bug #16915 order in FROM confuses JOIN
Submitted: 30 Jan 2006 21:04 Modified: 30 Jan 2006 21:37
Reporter: Jacek Becla Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15-standard OS:
Assigned to: MySQL Verification Team CPU Architecture:Any

[30 Jan 2006 21:04] Jacek Becla
Description:
Hi,

Order of tables in FROM clause confuses JOIN: in some cases an error is returned (incorrectly)

How to repeat:
create table X (x int);
create table Y (y int);
create table Z (z int);

select X.* from X, Y  left join Z on Z.z=X.x;
ERROR 1054 (42S22): Unknown column 'X.x' in 'on clause'

mysql> select X.* from Y, X  left join Z on Z.z=X.x;
Empty set (0.00 sec)
[30 Jan 2006 21:05] Jacek Becla
(added version)
[30 Jan 2006 21:37] MySQL Verification Team
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: http://dev.mysql.com/doc/refman/5.0/en/join.html
there you will see that from 5.0.12 were introduced changes in
the join syntax for to be SQL Standard compliance.

mysql> create table X (x int);
Query OK, 0 rows affected (1.41 sec)

mysql> create table Y (y int);
Query OK, 0 rows affected (0.22 sec)

mysql> create table Z (z int);
Query OK, 0 rows affected (0.19 sec)

mysql> select X.* from X, Y  left join Z on Z.z=X.x;
ERROR 1054 (42S22): Unknown column 'X.x' in 'on clause'

mysql> select X.* from (X, Y)  left join Z on Z.z=X.x;
Empty set (0.19 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.16 sec)

mysql>