Bug #13104 LEFT OUTER JOIN and Two Other Tables

• Submitted: 10 Sep 2005 13:15
• Modified: 23 Sep 2005 10:39
• Reporter: David Wedwick
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.12-0
• OS: Linux (Linux)
• CPU Architecture: Any

[10 Sep 2005 13:15] David Wedwick

Description:
The following query:

select a.*, b.*, c.*
from <table1> a, <table2> b
left outer join <table3> c on c.key = a.key
where b.key = a.key

will produce the error "Unknown column 'a.key' in 'on clause'

How to repeat:
mysql> create table test1 (id int, name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2 (id int, name char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table test3 (id int, name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1, 'Frank'), (2, 'Betty'), (3, 'George');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test2 values (1, 'Frank'), (2, 'Betty'), (3, 'George');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test3 values (1, 'Frank'), (2, 'Betty'), (3, 'George');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a.*, b.*, c.* from test1 a, test2 b left outer join test3 c on c.id = a.id where b.id = a.id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'

Suggested fix:
Workaround:  switch the first and second tables to get the query to work properly.

mysql> select a.*, b.*, c.* from test2 b, test1 a left outer join test3 c on c.id = a.id where b.id = a.id;
+------+--------+------+--------+------+--------+
| id   | name   | id   | name   | id   | name   |
+------+--------+------+--------+------+--------+
|    1 | Frank  |    1 | Frank  |    1 | Frank  |
|    2 | Betty  |    2 | Betty  |    2 | Betty  |
|    3 | George |    3 | George |    3 | George |
+------+--------+------+--------+------+--------+
3 rows in set (0.00 sec)

[10 Sep 2005 13:19] David Wedwick

This worked OK in version 5.0.7

[10 Sep 2005 13:50] Hartmut Holzgraefe

I assume this is due t the following 5.0.12 changelog item?  (see also bug #12964)

* Natural joins and joins with USING, including outer join variants, now are
processed according to the SQL:2003 standard. (Bug #4789, Bug #6136, Bug #6276,
Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

[11 Sep 2005 3:24] David Wedwick

I suppose this problem is related to the change listed under Bugs in 5.0.12-0.  Using parentheses around the table names does allow the query to work.  But, why would rearranging the tables also work?  

I don't know anything about SQL 2003, so forgive me for asking this, but is this how the query should work and this is not a bug?

[11 Sep 2005 9:38] Hartmut Holzgraefe

MySQL versions prior to 5.0.12 where processing outer joins in the wrong order,
this has changed in 5.0.12. By using parentheses you explicitly set the order and
by rearranging tables you make the now standards-compliant ordering produce
the desired result again.

[11 Sep 2005 11:21] Andrey Hristov

Looks very similar to bug #12943, which is in process of fixing (patch committed and in code review).

[31 Oct 2005 13:58] Lutz Schwarz

Switching tables a and b in the query may be acceptable as a workaround - but not as a solution. Because it won't work if the ON clause refers to both, table a and b, as in the following example:

select a.*, b.*, c.*
from a, b
left join c on (c.key = a.key AND c.itzel = b.itzel)
where b.key = a.key;

In mysql version 4 and even 3 such queries haven't been a problem.