MySQL Bugs: #30899: STRAIGHT_JOIN isn't identical in syntax to JOIN

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

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

Changed "identical to" to "similar to" to remove the contradiction.

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.