Bug #32700 STRAIGHT_JOIN doesn't force join order
Submitted: 25 Nov 2007 15:24 Modified: 28 Jul 2008 19:25
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.45, 4.1, 5.1, 6.0 BK OS:Linux (5.0.45-Debian_1ubuntu3-log)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: qc

[25 Nov 2007 15:24] Baron Schwartz
Description:
STRAIGHT_JOIN seems to force only the first table to be as specified.  The documentation (http://dev.mysql.com/doc/refman/5.0/en/select.html) says

"STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause."

How to repeat:
mysql> EXPLAIN
    -> SELECT STRAIGHT_JOIN f.film_id
    -> FROM sakila.film AS f
    ->    INNER JOIN sakila.film_actor AS fa
    ->       ON f.film_id=fa.film_id AND fa.actor_id = 1
    ->    INNER JOIN sakila.actor AS a USING(actor_id);
+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key                | key_len | ref                    | rows | Extra       |
+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | a     | const  | PRIMARY                | PRIMARY            | 2       | const                  |    1 | Using index |
|  1 | SIMPLE      | f     | index  | PRIMARY                | idx_fk_language_id | 1       | NULL                   | 1022 | Using index |
|  1 | SIMPLE      | fa    | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY            | 4       | const,sakila.f.film_id |    1 | Using index |
+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
3 rows in set (0.00 sec)

The tables should be joined in the order f, fa, a.  They are joined in the order a, f, fa.

Without the STRAIGHT_JOIN hint, they are joined in the order a, fa, f.  So the hint does change the order, but not fully.
[26 Nov 2007 21:56] Sveta Smirnova
Thank you for the report.

Verified as described. Same results with next statement too:

EXPLAIN
SELECT STRAIGHT_JOIN f.film_id
FROM film AS f, film_actor AS fa, actor AS a
WHERE f.film_id=fa.film_id AND fa.actor_id = 1 AND a.actor_id = fa.actor_id;
[26 Nov 2007 21:57] Sveta Smirnova
test case

Attachment: bug32700.test (application/octet-stream, text), 384.62 KiB.

[26 Jun 2008 22:10] Sergey Petrunya
The STRAIGHT_JOIN syntax is an optimizer hint which allows to specify which join order should be used.

Constant tables are really not part of join order - they are read at the optimization phase, all references to their columns are substituted with their values and so the table is never accessed again (note that this doesn't apply to const tables that are on the inner side of outer joins).

Considering the above, it would be misleading if EXPLAIN showed constant tables in the middle of join order. In other words, we do not consider the observed behavior to be a bug.

Documentation team: could you please reflect this (STRAIGHT_JOIN directive doesn't apply to const tables, EXPLAIN output shows them as first tables within their SELECT always). After that the bug should be set to "Not a bug".
[28 Jul 2008 19:25] Paul DuBois
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

I've updated the description or STRAIGHT_JOIN in the SELECT section.