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 */
