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: | |
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
[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.