Description:
In an explain expanded query, join order hints are printed with query block and qualified tables:
/*+ JOIN_PREFIX(@`select#1` `t2`@`qb2`,`t4`@`qb1`,`ta3`,`ta4`) */
It is not a valid syntax. See the definition in the manual:
https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html#optimizer-hints-join-order
hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)
How to repeat:
(From opt_hints_join_order.test)
CREATE TABLE t1(f1 INT(11) NOT NULL);
INSERT INTO t1 VALUES (10);
CREATE TABLE t2
(
f1 INT(11) NOT NULL AUTO_INCREMENT,
f2 INT(11) DEFAULT NULL,
PRIMARY KEY (f1),
KEY (f2)
);
INSERT INTO t2 VALUES (1, 7), (2, 1), (4, 7);
CREATE TABLE t4(f1 INT DEFAULT NULL);
INSERT INTO t4 VALUES (2);
ANALYZE TABLE t1, t2, t4;
explain SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
show warnings;
The expanded query is:
/* select#1 */ select /*+ JOIN_PREFIX(@`select#1` `t2`@`qb2`,`t4`@`qb1`,`ta3`,`ta4`) */ count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2` `ta3` join `test`.`t2` `ta4` semi join (`test`.`t4`) semi join (`test`.`t2`) where ((`test`.`ta3`.`f2` = `test`.`t2`.`f2`) and (`test`.`ta4`.`f1` = `test`.`t4`.`f1`))
Replace the original query with hints in the expanded query, then get parse error:
explain SELECT /*+ JOIN_PREFIX(@`select#1` `t2`@`qb2`,`t4`@`qb1`,`ta3`,`ta4`) */
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
show warnings;
| Warning | 1064 | Optimizer hint syntax error near '`qb2`,`t4`@`qb1`,`ta3`,`ta4`) */ COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4 ' at line 1 |
Note that you need Bug#98521 patch to input @`select#1` as part of the hint, or assign the first query block a name instead of reference by sys name.
Suggested fix:
It is well known that an expanded query is not guaranteed to be a valid SQL. For example it presents semijoin. In this perspective it is not a bug.
However, the hint syntax error does reveal some problems with complex queries. Some tables may come from a flatten subquery thus need qualification by query block name. QB-level hints also need to show query block names because there might be other ones from other query blocks.
This situation conflicts with the manual.
There are alternative ways to get better consistency:
1) Print the hints contextually in the select clause of the query block it belongs, to explore the second form.
2) Merge the two forms into a single one:
hint_name([@query_block_name] tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)
I would suggest the second way, because it is natural and simpler. The new form is self-contained, thus can be the base of an Oracle-like Outline or SPM. For instance in https://oracle-base.com/articles/misc/outlines
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "E"@"SEL$1" NONE NONE)
Note that composing rewrite rules for the Rewriter plugin is really not that easy for complex queries. While for simple queries it brings reparsing overhead.
