Bug #30899 STRAIGHT_JOIN isn't identical in syntax to JOIN
Submitted: 7 Sep 2007 15:09 Modified: 7 Sep 2007 15:45
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[7 Sep 2007 15:09] Baron Schwartz
Description:
From http://dev.mysql.com/doc/refman/5.0/en/join.html:

"STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order."

There is a syntactical difference though.  It can only be used with an ON clause or without any clause at all, not with a USING clause.

This is documented in the formal syntax at the top of the page, but the paragraph quoted contradicts it.

How to repeat:
With a STRAIGHT_JOIN between the last two tables and an ON clause:

mysql> select count(*) from sakila.film join sakila.film_actor using(film_id) STRAIGHT_JOIN sakila.actor on film_actor.actor_id = actor.actor_id;
+----------+
| count(*) |
+----------+
|     5462 | 
+----------+
1 row in set (0.08 sec)

With a STRAIGHT_JOIN between the last two tables and a USING clause:

mysql> select count(*) from sakila.film join sakila.film_actor using(film_id) STRAIGHT_JOIN sakila.actor using(actor_id);
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 'using(actor_id)' at line 1
[7 Sep 2007 15:39] Paul DuBois
Changed "identical to" to "similar to" to remove the contradiction.
[7 Sep 2007 15:45] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.