MySQL Bugs: #35268: Parser can't handle STRAIGHT

Bug #35268	Parser can't handle STRAIGHT_JOIN with USING
Submitted:	13 Mar 2008 12:52	Modified:	28 Nov 2020 14:58
Reporter:	Martin Hansson	Email Updates:
Status:	Closed	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

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.

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)

Verified as described by using actual 5.1 bzr tree (5.1.32).

Same problem with

  SELECT * FROM t1 NATURAL STRAIGHT_JOIN t2;

and still reproducible with 5.6.10

NATURAL INNER JOIN also gives a syntax error even though just "JOIN" and "INNER JOIN" should be the same thing ...

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

8.0:

mysql> SELECT * FROM t1 STRAIGHT_JOIN t2 USING( a );
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   30 |  300 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 NATURAL INNER JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   30 |  300 |
+------+------+------+
3 rows in set (0.01 sec)