Bug #53426 Not able to parse a ISO9075 compliant 3-table join
Submitted: 5 May 2010 8:01 Modified: 5 May 2010 8:10
Reporter: Ole John Aske Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.44 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D5 (Feature request)

[5 May 2010 8:01] Ole John Aske
Description:
MySQL is not able to parse to ISO9075 compliant 3-table join :

SELECT * FROM A JOIN B JOIN C ON B.pk = C.pk ON A.pk = B.pk;

However, if we put the right hand side <joined_table> in paranthesis 
the query is correctly parsed:

OK: SELECT * FROM A JOIN (B JOIN C ON B.pk = C.pk) ON A.pk = B.pk;

Or if we change the join type to an outer join:

OK: SELECT * FROM A LEFT JOIN B JOIN C ON B.pk = C.pk ON A.pk = B.pk;

The root cause here is MySQL extension of defining CROSS and INNER JOIN to be syntactic equivalent. Instead cross or inner join type is decided based on the presence of a <join_condition>.

This creates an ambiguous grammar where it is impossible to decide whether 

A JOIN B JOIN C <join_condition> should be parsed as either (in ISO9075 terms):

 1. A INNER JOIN (B CROSS JOIN C) <join_condition>
 2. A CROSS JOIN (B INNER JOIN C <join_condition>)

IMHO:
 Being able to unambiguously parse a ISO9075 <joined-table> had been a nicer
'feature' than providing the mySQL extension/confusion of INNER JOIN and CROSS JOIN being syntactical equivalent.

NOTE1:

My In order to unambiguously express the 3 table inner join we have to add parantheses. However the MySQL documentation specifies : 'In general, parentheses can be ignored in join expressions containing only inner join operations' (http://dev.mysql.com/doc/refman/5.1/en/join.html)

How to repeat:
CREATE TABLE A (pk INT PRIMARY KEY) ENGINE = MyISAM;
CREATE TABLE B LIKE A;
CREATE TABLE C LIKE A;

SELECT * FROM A JOIN B JOIN C ON B.pk = C.pk ON A.pk = B.pk;

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 'ON A.pk = B.pk' at line 1

Suggested fix:
Deprecate the MySQL extension of CROSS JOIN and INNER JOIN being syntactical equivalent. Change to pure ISO9075 behavior where :

- CROSS JOIN shall not have a <join_condition>
- [INNER] JOIN requires a <join_condition>

The code change itself is rather minimal:

=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy     2010-03-12 13:57:24 +0000
+++ sql/sql_yacc.yy     2010-05-05 07:38:45 +0000
@@ -682,10 +682,10 @@ bool my_yyoverflow(short **a, YYSTYPE **

 %pure_parser                                    /* We have threads */
 /*
-  Currently there are 169 shift/reduce conflicts.
+  Currently there are 167 shift/reduce conflicts.
   We should not introduce new conflicts any more.
 */
-%expect 169
+%expect 167

 /*
    Comments for TOKENS.
@@ -1280,7 +1280,6 @@ bool my_yyoverflow(short **a, YYSTYPE **

 %left   JOIN_SYM INNER_SYM STRAIGHT_JOIN CROSS LEFT RIGHT
 /* A dummy token to force the priority of table_ref production in a join. */
-%left   TABLE_REF_PRIORITY
 %left   SET_VAR
 %left   OR_OR_SYM OR_SYM OR2_SYM
 %left   XOR
@@ -8669,8 +8668,8 @@ join_table:
             so that [INNER | CROSS] JOIN is properly nested as other
             left-associative joins.
           */
-          table_ref normal_join table_ref %prec TABLE_REF_PRIORITY
-          { MYSQL_YYABORT_UNLESS($1 && ($$=$3)); }
+          table_ref CROSS JOIN_SYM table_ref
+          { MYSQL_YYABORT_UNLESS($1 && ($$=$4)); }
         | table_ref STRAIGHT_JOIN table_factor
           { MYSQL_YYABORT_UNLESS($1 && ($$=$3)); $3->straight=1; }
         | table_ref normal_join table_ref
@@ -8796,7 +8795,6 @@ join_table:
 normal_join:
           JOIN_SYM {}
         | INNER_SYM JOIN_SYM {}
-        | CROSS JOIN_SYM {}
         ;

 /* Warning - may return NULL in case of incomplete SELECT */
[5 May 2010 8:10] Sveta Smirnova
Thank you for the reasonable feature request.
[5 May 2010 8:27] Ole John Aske
Please also note my comment under 'NOTE' about bug in the documentation!
[7 May 2010 20:22] Peter Gulutzan
The MySQL Reference Manual says:
"In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN
(they can replace each other). In standard SQL, they are not
equivalent. INNER JOIN is used with an ON clause, CROSS JOIN
is used otherwise." 
http://dev.mysql.com/doc/refman/5.0/en/join.html
Clearly, at the time that was written, it was known that a
deviation from the standard was happening. So MySQL's current
behaviour is deliberate as well as documented. 

On the other hand, the statement "In general, parentheses
can be ignored ..." can't be interpreted as a promise that
parentheses will never be required.

I have to think, then, that MySQL should avoid this feature
request unless a reason exists that is more compelling than
"parentheses are required".