Description:
Previously, a non-global hint refers to either the current query block
or an aliased query block. Aliases with prefix "select#" are also allowed, which
is confusing because they are also used as system generated names.
In addition, contextualization of hints is sensitive to the parse order of query blocks. In other words, the QB_NAME is designed to alias a query block so that it can be referred to elsewhere, however, it sometimes does not work.
Note that the query blocks are internally identified with a number
(SELECT_LEX::select_number), with which system names are defined. That
system name could be explored to refer to any query block in the query.
The contextualize of hints could also be divided into two phases: the first phase handles QB_NAME, and the second handles other hints. Then the implicit dependency is gone.
How to repeat:
create table t1 (a INT primary key, b int, c int, key(b), key(c));
insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8);
insert into t1 select (a+8), b, c from t1;
analyze table t1;
create table t2 like t1;
EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1;
show warnings\G
explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1;
show warnings\G
explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2;
show warnings\G
explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2);
show warnings\G
Expected output:
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 2 warnings (0.01 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3227
Message: Hint QB_NAME(`select#0`) is ignored because it uses a reserved name
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select 1 AS `1`
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | b,c | NULL | NULL | NULL | 16 | 23.44 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`select#1` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1))
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| 1 | PRIMARY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
| 2 | UNION | t1 | NULL | ALL | b,c | NULL | NULL | NULL | 16 | 23.44 | Using where |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`qb2` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) union all /* select#2 */ select /*+ QB_NAME(`qb2`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2))
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2);
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | PRIMARY,b,c | NULL | NULL | NULL | 16 | 28.22 | Using where |
| 2 | SUBQUERY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ QB_NAME(`qb1`) NO_INDEX_MERGE(`t1`@`qb1` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1) or (`test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2)))))
1 row in set (0.00 sec)
Actual output:
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ QB_NAME(`select#0`) */ 1 AS `1`
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| 1 | SIMPLE | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3127
Message: Query block name `select#1` is not found for NO_INDEX_MERGE hint
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1))
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2;
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
| 1 | PRIMARY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
| 2 | UNION | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3127
Message: Query block name `qb2` is not found for NO_INDEX_MERGE hint
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) union all /* select#2 */ select /*+ QB_NAME(`qb2`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2))
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) >
mysql [localhost] {msandbox} (test) > explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2);
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| 1 | PRIMARY | t1 | NULL | index_merge | PRIMARY,b,c | b,c,PRIMARY | 5,5,4 | NULL | 5 | 100.00 | Using union(b,c,PRIMARY); Using where |
| 2 | SUBQUERY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
2 rows in set, 2 warnings (0.01 sec)
mysql [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3127
Message: Query block name `qb1` is not found for NO_INDEX_MERGE hint
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select /*+ QB_NAME(`qb1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1) or (`test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2)))))
2 rows in set (0.00 sec)
Suggested fix:
Disallow prefix "select#" in query block alias.
Allow referencing by system generated name.
A patch will be provided separately.
Description: Previously, a non-global hint refers to either the current query block or an aliased query block. Aliases with prefix "select#" are also allowed, which is confusing because they are also used as system generated names. In addition, contextualization of hints is sensitive to the parse order of query blocks. In other words, the QB_NAME is designed to alias a query block so that it can be referred to elsewhere, however, it sometimes does not work. Note that the query blocks are internally identified with a number (SELECT_LEX::select_number), with which system names are defined. That system name could be explored to refer to any query block in the query. The contextualize of hints could also be divided into two phases: the first phase handles QB_NAME, and the second handles other hints. Then the implicit dependency is gone. How to repeat: create table t1 (a INT primary key, b int, c int, key(b), key(c)); insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8); insert into t1 select (a+8), b, c from t1; analyze table t1; create table t2 like t1; EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1; show warnings\G explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1; show warnings\G explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2; show warnings\G explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2); show warnings\G Expected output: mysql [localhost] {msandbox} (test) > EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 2 warnings (0.01 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Warning Code: 3227 Message: Hint QB_NAME(`select#0`) is ignored because it uses a reserved name *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select 1 AS `1` 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | b,c | NULL | NULL | NULL | 16 | 23.44 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`select#1` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2; +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | PRIMARY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | | 2 | UNION | t1 | NULL | ALL | b,c | NULL | NULL | NULL | 16 | 23.44 | Using where | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ NO_INDEX_MERGE(`t1`@`qb2` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) union all /* select#2 */ select /*+ QB_NAME(`qb2`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2)) 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2); +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | PRIMARY | t1 | NULL | ALL | PRIMARY,b,c | NULL | NULL | NULL | 16 | 28.22 | Using where | | 2 | SUBQUERY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ QB_NAME(`qb1`) NO_INDEX_MERGE(`t1`@`qb1` `b`, `c`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1) or (`test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2))))) 1 row in set (0.00 sec) Actual output: mysql [localhost] {msandbox} (test) > EXPLAIN SELECT /*+ QB_NAME(`select#0`) */ 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ QB_NAME(`select#0`) */ 1 AS `1` 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@`select#1` b,c) */ a from t1 where b = 1 or c = 1; +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Warning Code: 3127 Message: Query block name `select#1` is not found for NO_INDEX_MERGE hint *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t1@qb2 b,c) */ a from t1 where b = 1 or c = 1 union all select /*+ QB_NAME(qb2) */ a from t1 where b = 2 or c = 2; +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | PRIMARY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | | 2 | UNION | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | +----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+-------------------------------+ 2 rows in set, 2 warnings (0.00 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Warning Code: 3127 Message: Query block name `qb2` is not found for NO_INDEX_MERGE hint *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1)) union all /* select#2 */ select /*+ QB_NAME(`qb2`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2)) 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > mysql [localhost] {msandbox} (test) > explain select /*+ QB_NAME(qb1) */ a from t1 where b = 1 or c = 1 or a = (select /*+ NO_INDEX_MERGE(t1@qb1 b,c) */ min(a) from t1 where b = 2 or c = 2); +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | PRIMARY | t1 | NULL | index_merge | PRIMARY,b,c | b,c,PRIMARY | 5,5,4 | NULL | 5 | 100.00 | Using union(b,c,PRIMARY); Using where | | 2 | SUBQUERY | t1 | NULL | index_merge | b,c | b,c | 5,5 | NULL | 4 | 100.00 | Using union(b,c); Using where | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 2 warnings (0.01 sec) mysql [localhost] {msandbox} (test) > show warnings\G *************************** 1. row *************************** Level: Warning Code: 3127 Message: Query block name `qb1` is not found for NO_INDEX_MERGE hint *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select /*+ QB_NAME(`qb1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`b` = 1) or (`test`.`t1`.`c` = 1) or (`test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` where ((`test`.`t1`.`b` = 2) or (`test`.`t1`.`c` = 2))))) 2 rows in set (0.00 sec) Suggested fix: Disallow prefix "select#" in query block alias. Allow referencing by system generated name. A patch will be provided separately.