Description:
If the user has used some optimizer hints on the subquery, the optimizer hints won't take effect if the subquery gets transformed.
How to repeat:
CREATE TABLE t1 (
a INT,
KEY i1 (a)
);
CREATE TABLE t2 (
b INT,
c INT
);
CREATE TABLE t3 (
d INT,
KEY i3 (d)
);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1, 9), (2, 7), (3, 10), (2, 5);
INSERT INTO t3 VALUES (9), (5), (7);
ANALYZE TABLE t1, t2, t3;
mysql > EXPLAIN SELECT * FROM (SELECT /*+ JOIN_ORDER(t1, t2, t3) */ * FROM t1, t2, t3 WHERE a = b AND c = d) as derived;
Actual results:
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t3 | NULL | index | i3 | i3 | 5 | NULL | 3 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | t1 | NULL | ref | i1 | i1 | 5 | test.t2.b | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
Expected results:
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | index | i1 | i1 | 5 | NULL | 4 | 100.00 | Using index |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | t3 | NULL | ref | i3 | i3 | 5 | test.t2.c | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+
The above SQL is a simple example of this issue. There should be more cases like that in MySQL 8.0.23 because it begins supporting more transformations on subquery.
For the scenarios, in which a new query block is created or the old query block is abandoned in the codes, it should have this kind of issue if the optimizer hints don't get inherited.
But the index hint can take effect, which is inconsistent with the above behavior:
mysql > EXPLAIN SELECT * FROM (SELECT /*+ NO_INDEX(t3 i3) NO_INDEX(t1 i1) */ * FROM t1, t2, t3 WHERE a = b AND c = d) as derived;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
I guess it's related to where different kinds of optimizer hints are placed in the implementation.
Description: If the user has used some optimizer hints on the subquery, the optimizer hints won't take effect if the subquery gets transformed. How to repeat: CREATE TABLE t1 ( a INT, KEY i1 (a) ); CREATE TABLE t2 ( b INT, c INT ); CREATE TABLE t3 ( d INT, KEY i3 (d) ); INSERT INTO t1 VALUES (1), (2), (3), (4); INSERT INTO t2 VALUES (1, 9), (2, 7), (3, 10), (2, 5); INSERT INTO t3 VALUES (9), (5), (7); ANALYZE TABLE t1, t2, t3; mysql > EXPLAIN SELECT * FROM (SELECT /*+ JOIN_ORDER(t1, t2, t3) */ * FROM t1, t2, t3 WHERE a = b AND c = d) as derived; Actual results: +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t3 | NULL | index | i3 | i3 | 5 | NULL | 3 | 100.00 | Using index | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | t1 | NULL | ref | i1 | i1 | 5 | test.t2.b | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ Expected results: +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | NULL | index | i1 | i1 | 5 | NULL | 4 | 100.00 | Using index | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | t3 | NULL | ref | i3 | i3 | 5 | test.t2.c | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+--------------------------------------------+ The above SQL is a simple example of this issue. There should be more cases like that in MySQL 8.0.23 because it begins supporting more transformations on subquery. For the scenarios, in which a new query block is created or the old query block is abandoned in the codes, it should have this kind of issue if the optimizer hints don't get inherited. But the index hint can take effect, which is inconsistent with the above behavior: mysql > EXPLAIN SELECT * FROM (SELECT /*+ NO_INDEX(t3 i3) NO_INDEX(t1 i1) */ * FROM t1, t2, t3 WHERE a = b AND c = d) as derived; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ I guess it's related to where different kinds of optimizer hints are placed in the implementation.