Bug #30460 "unknown column ... in 'on clause'" in LEFT JOIN
Submitted: 16 Aug 2007 16:51 Modified: 16 Aug 2007 17:43
Reporter: Chris Waterson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1.20 OS:Any
Assigned to: CPU Architecture:Any

[16 Aug 2007 16:51] Chris Waterson 
Description:
A query that involves the cross-product of three tables is not parsing successfully.  This query parses successfully in earlier versions of MySQL (tested in 4.0 and 4.1).

How to repeat:
To reproduce:

1. Create the following tables:

     CREATE TABLE a (a_id INT NOT NULL);
     CREATE TABLE b (b_id INT NOT NULL);
     CREATE TABLE c (a_id INT NOT NULL, b_id INT NOT NULL);

2. Run this query:

     SELECT * FROM a, b LEFT JOIN c ON c.a_id = a.a_id AND c.b_id = b.b_id

Expected: query parses successfully.
Actual: "ERROR 1054 (42S22): Unknown column 'a.a_id' in 'on clause'"

Suggested fix:
Unknown.
[16 Aug 2007 17:21] MySQL Verification Team 
Thank you for the bug report. Please read the chapter join syntax with
changes done from version 5.0.12 to be SQL Standard compliance.

[miguel@skybr 5.0]$ bin/mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-beta-debug Source distribution

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

mysql> CREATE TABLE a (a_id INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql>      CREATE TABLE b (b_id INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql>      CREATE TABLE c (a_id INT NOT NULL, b_id INT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

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

mysql>
[16 Aug 2007 17:43] Chris Waterson 
Gah...sorry for the noise!