MySQL Bugs: #30842: multiple ON clauses allowed in LEFT JOIN

Bug #30842	multiple ON clauses allowed in LEFT JOIN
Submitted:	5 Sep 2007 20:15	Modified:	6 Sep 2007 14:27
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
An apparently invalid query is accepted by the parser and results in a different query plan than intended, as though there is an invisible scoping around several table references.

select *
from t1
   left join t2
   left join t3
   on b = c
   on b = c;

The query ought to be invalid because there is no join condition following the first LEFT JOIN.  From http://dev.mysql.com/doc/refman/5.0/en/join.html:

join_table:
...
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
...

This makes it look like the join_condition is mandatory.  But I can get the parser to accept the query anyway.

How to repeat:
drop table if exists t1, t2, t3;

create table t1(a int not null primary key);
create table t2(b int not null primary key);
create table t3(c int not null primary key);

insert into t1 values(1), (2), (3);
insert into t2 values(1), (2);
insert into t3 values(1);

select *
from t1
   left join t2
   left join t3
   on b = c
   on b = c;

Result:
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 | 
| 2 |    1 |    1 | 
| 3 |    1 |    1 | 
+---+------+------+

It looks like the wrong results.  I should get some NULLs, right?  The above case looks nonsensical because the two ON clauses are identical.  It looks like I forgot to specify conditions between two of the joins.  If I remedy that,

select *
from t1
   left join t2
   left join t3
   on a = b
   on b = c;

(This is similar to an actual case that came up on the mailing list a while ago) This query gives an error, which is good:

ERROR 1054 (42S22): Unknown column 'a' in 'on clause'

But it's not an error that looks right.  If I'm confused or new to MySQL, I think "of course there's an 'a' column!"  If I reverse the ON clauses, the JOIN appears to work:

select *
from t1
   left join t2
   left join t3
   on b = c
   on a = b;

+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 | 
| 2 |    2 | NULL | 
| 3 | NULL | NULL | 
+---+------+------+

As far as I know, this isn't a valid JOIN, but it works.  I can also coerce the parser into accepting an ambiguous column reference by taking advantage of the fact that the first table appears to be "hidden" from the ON clauses:

drop table if exists t1, t2, t3;

create table t1(a int not null primary key);
create table t2(b int not null primary key);
create table t3(c int not null primary key, a int not null);

insert into t1 values(1), (2), (3);
insert into t2 values(1), (2);
insert into t3 values(1,1);

select *
from t1
   left join t2
   left join t3
   on a = b
   on b = c;

+---+------+------+------+
| a | b    | c    | a    |
+---+------+------+------+
| 1 |    1 |    1 |    1 | 
| 2 |    1 |    1 |    1 | 
| 3 |    1 |    1 |    1 | 
+---+------+------+------+

Again I think this should give an error.  It looks like there is actually a cross product between some of the tables.  Adding another row into t2 and t3 shows this (notice I'm using the first SELECT again):

insert into t2 values(5);
insert into t3 values(5, 5);

select *
from t1
   left join t2
   left join t3
   on a = b
   on b = c;

+---+------+------+------+
| a | b    | c    | a    |
+---+------+------+------+
| 1 |    1 |    1 |    1 | 
| 1 |    5 |    5 |    5 | 
| 2 |    1 |    1 |    1 | 
| 2 |    5 |    5 |    5 | 
| 3 |    1 |    1 |    1 | 
| 3 |    5 |    5 |    5 | 
+---+------+------+------+

The EXPLAIN EXTENDED warning is as follows:

select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`b` = `test`.`t3`.`c`) and (`test`.`t3`.`a` = `test`.`t2`.`b`))) where 1

Suggested fix:
Don't allow a LEFT JOIN without an ON or USING clause right after it.

I forgot to say my version:

mysql> select version();
+----------------------------+
| version()                  |
+----------------------------+
| 5.0.38-Ubuntu_0ubuntu1-log | 
+----------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. Looks like that query is valid SQL query
MS SQL Server and Oracles agrees with MySQL too:

mysql> select *
    -> from t1
    ->    left join t2
    ->    left join t3
    ->    on b = c
    ->    on b = c;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 | 
| 2 |    1 |    1 | 
| 3 |    1 |    1 | 
+---+------+------+
3 rows in set (0.00 sec)

-------------------------------------

1> select *
2> from t1
3>    left join t2
4>    left join t3
5>    on b = c
6>    on b = c
7> go
a           b           c
----------- ----------- -----------
          1           1           1
          2           1           1
          3           1           1

(3 rows affected)
1>
-------------------------------------
SQL> select *
  2  from t1
  3     left join t2
  4     left join t3
  5     on b = c
  6     on b = c;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1

SQL>

Should we change it to a doc bug, and update the documentation to indicate that the join_condition is optional for a LEFT JOIN?

Or should something else in the docs be changed to indicate that a LEFT JOIN without a join_condition may be rewritten to an INNER JOIN without a join_condition?