Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
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.
Attachments
Issue Links
- relates to
-
MDEV-33281 Implement optimizer hints like in MySQL 8
-
- In Progress
-
Note: MySQL also seems to do that. It doesn't even interpret the join order hint:
mysql> select STRAIGHT_JOIN /*+ JOIN_ORDER(aaa) */ * 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.