Bug #35268 Parser can't handle STRAIGHT_JOIN with USING
Submitted: 13 Mar 2008 12:52 Modified: 4 Feb 2009 15:11
Reporter: Martin Hansson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: parser, straight_join, syntax
Triage: Triaged: D3 (Medium)

[13 Mar 2008 12:52] Martin Hansson
Description:
The STRAIGHT_JOIN keyword should be usable wherever JOIN can be used. Currently, this is not the case.

SELECT * FROM t1 STRAIGHT_JOIN t2 ON ( t1.a = t2.a ); # works
SELECT * FROM t1 STRAIGHT_JOIN t2; # works

SELECT * FROM t1 STRAIGHT_JOIN t2 USING( a ); # parse error

How to repeat:
CREATE TABLE t1( a INT );
CREATE TABLE t2( a INT );

SELECT * FROM t1 STRAIGHT_JOIN t2 USING( a );

Suggested fix:
Add the corresponding rule to the parser.
[31 Mar 2008 17:45] Baron Schwartz
I found the same thing on 5.0.40:

mysql> explain select * from film join film_actor using(film_id) straight_join actor using(actor_id)\G
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

mysql> explain select * from film join film_actor using(film_id) straight_join actor on actor.actor_id = film_actor.actor_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1022
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: sakila.film.film_id
         rows: 2
        Extra: 
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.film_actor.actor_id
         rows: 1
        Extra: 
3 rows in set (0.00 sec)
[4 Feb 2009 15:11] Susanne Ebrecht
Verified as described by using actual 5.1 bzr tree (5.1.32).
[13 Mar 2013 9:41] Hartmut Holzgraefe
Same problem with

  SELECT * FROM t1 NATURAL STRAIGHT_JOIN t2;

and still reproducible with 5.6.10
[13 Mar 2013 14:30] Hartmut Holzgraefe
NATURAL INNER JOIN also gives a syntax error even though just "JOIN" and "INNER JOIN" should be the same thing ...