Bug #16536 LEFT JOIN "broken" when joining multiple tables

• Submitted: 16 Jan 2006 14:25
• Modified: 16 Jan 2006 16:53
• Reporter: Rudy Metzger
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.18-standard-log
• OS: Linux (Fedora Core 4)
• CPU Architecture: Any

[16 Jan 2006 14:25] Rudy Metzger

Description:
LEFT JOIN is broken when joining multiple tables.

select null from a,b left join a.id =c.id where a = b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id where a = b' at line 1

How to repeat:
bug> create table a (id int);
Query OK, 0 rows affected (0.01 sec)

bug> create table b (id int );
Query OK, 0 rows affected (0.00 sec)

bug> create table c (id int);
Query OK, 0 rows affected (0.00 sec)

bug> select null from a,b left join c on a.id =c.id where a.id = b.id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'

BUT THE FOLLOWING WORKS:
webdev1/bug> select null from b,a left join c on a.id =c.id where a.id = b.id;
Empty set (0.00 sec)

So it depends on the order how you give the tables.
NOTE: In above example it is possible to swap tables around, but this is not always possible (multiple LEFT JOINS or tuning), that's why the S1 rating

bug> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.18-standard-log |
+---------------------+
1 row in set (0.00 sec)

[16 Jan 2006 16:53] MySQL Verification Team

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Since 5.0.12 the join syntax was modified for to be more SQL Standard, please
read the join syntax's Manual section.

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 4 to server version: 5.0.19-debug

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

mysql> create table a (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table b (id int );
Query OK, 0 rows affected (0.01 sec)

mysql> create table c (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> select null from a,b left join c on a.id =c.id where a.id = b.id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'
mysql> select null from (a,b) left join c on a.id =c.id where a.id = b.id;
Empty set (0.00 sec)