Optimizer hints ignored after STRAIGHT_JOIN

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.