Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
None
Description
New-style optimizer hints are ignored if a query has STRAIGHT_JOIN hint before them:
CREATE TABLE t1 (f1 INT NOT NULL); |
INSERT INTO t1 VALUES (1),(2),(3); |
|
|
CREATE TABLE t2 (f1 INT NOT NULL); |
INSERT INTO t2 VALUES (3),(1),(8),(3); |
|
|
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN /*+ NO_BNL()*/ * FROM t1, t2; |
|
|
DROP TABLE t1, t2; |
Compare this with the case
EXPLAIN EXTENDED SELECT /*+ NO_BNL()*/ STRAIGHT_JOIN * FROM t1, t2; |
where the STRAIGHT_JOIN hint follows new-style hints. Here new-style hint is parsed and applied.
Interaction with other old-style hints should be also tested.
There are two issues, not to be confused
1. Hint comment /*+ .... */ is interpreted only after the SQL "Verb" : SELECT/UPDATE/DELETE/etc. In other parts of the query it is simply ignored with no warning. It's the same in MySQL. SELECT STRAIGHT_JOIN /*+ anything here is ignored */
2. Use of STRAIGHT_JOIN causes any join order hints (JOIN_PREFIX, JOIN_SUFFIX, JOIN_FIXED_ORDER, JOIN_ORDER ) to be ignored. It is parsed, but not interpreted. Even /*+ JOIN_PREFIX(no_such_table) */ is accepted. It is like that in MySQL, too.
Decision
We do not consider either to be a bug. Just need to note in the documentation.
Attachments
Issue Links
- relates to
-
MDEV-33281 Implement optimizer hints like in MySQL 8
-
- In Progress
-
Activity
Currently, new-style optimizer hints are interpreted only if they follow one of the keywords: SELECT, INSERT, UPDATE, DELETE, otherwise they are treated as regular comments. So, if a query contains both old-style and new-style hints, the latter are effectively ignored.
However, if old-style hints follow new-style ones, both are taken into account.
MySQL follows this rule as we do.
I am not sure we should treat it as a bug, probably more important question is interaction and possible conflicts between old-style and new-style hints in the same statement.
Note: MySQL also seems to do that. It doesn't even interpret the join order hint:
mysql> select /*+ JOIN_ORDER(aaa) */ STRAIGHT_JOIN * from ten limit 1;(no warnings)It's not a parser issue, other hints are interpreted:
mysql> select /*+ NO_MRR(aaa) */ STRAIGHT_JOIN * from ten limit 1;(a warning is given)The explanation about the JOIN Order hints is that they are intrepreted "late" in make_join_statistics() where the optimizer calls hints API to add join table dependencies coming from hints. I suppose for SELECT STRAIGHT_JOIN this is simply not done as in that case the join optimizer takes a shortcut and just builds the join order from the list of join tables.