Description:
The join syntax is explained here:
http://dev.mysql.com/doc/refman/5.1/en/join.html
I think there are 3 issues:
- there is an error in the rule table_factor
- no production for 'subquery in the from clause'
- the rule for join_table is inconsistent in its notation
As a sidenote, there also seems to be a minor inconsistency/error in http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html
as compared to
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html
in that sometimes parentheses are sometimes included and sometimes excluded from the subquery production rule.
How to repeat:
http://dev.mysql.com/doc/refman/5.1/en/join.html
Suggested fix:
#1 error in the rule table_factor
The rule now reads:
table_factor:
tbl_name [[AS] alias] [index_hint_list]
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
I believe the last alternative is wrong. What is "OJ" supposed to mean? This statement for example fails with a syntax error:
select * from
OJ actor a LEFT OUTER JOIN film_actor f ON a.actor_id = f.actor_id
(actor a is a valid table_reference - leaving out the alias would also be a valid table_reference, however that would be parsed differently - OJ would be seen as table name, and actor as alias!)
It seems to me the third alternative can be removed without consequence.
#2 subquery in the from clause:
My proposal would be to add the subquery as an alternative to the table_factor rule. It would then read:
table_factor:
tbl_name [[AS] alias] [index_hint_list]
| subquery [AS] alias
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
alternatively, the term "derived_table" could be used:
table_factor:
tbl_name [[AS] alias] [index_hint_list]
| derived_table
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
where derived_table stands for
subquery [AS] alias
The documentation should contain a link to
http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html
to explain the subquery syntax in this context in more detail.
Sidenote: http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html uses an inconsistent notation for the subquery syntax. In that page, the 'subquery' is the part that appears within the parentheses, whereas in http://dev.mysql.com/doc/refman/5.1/en/subqueries.html, the subquery includes the parentheses. Personally, I think the rule should be:
subquery := '(' select-expression ')'
or
subquery := '(' query-expression ')'
#3 inconsistent notation for the rule join_table:
right now, the rule reads:
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
The inconsistency is observed in the first three alternatives. The first alternative uses an optional join_condition, whereas the next two could be written using a similar construct like so:
| table_reference STRAIGHT_JOIN table_factor [ON condition]
Personally I would prefer it if the last four alternatives would be written like so:
| table_reference LEFT|RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT|RIGHT [OUTER]] JOIN table_factor
In conclusion, the entire rule would then become:
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor [ON condition]
| table_reference LEFT|RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT|RIGHT [OUTER]] JOIN table_factor
which I personally find easier to understand.
