1.sql

MariaDB [test]> explain extended with CTE1 as (select /*+ QB_NAME(aaa) NO_RANGE_OPTIMIZATION(t3@aaa ) */ t3.a from t3 where t3.a<3) select * from CTE1 a, CTE1 b;
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|    1 | SIMPLE      | t3    | index | a             | a    | 5       | NULL | 1000 |   100.00 | Using where; Using index                                     |
|    1 | SIMPLE      | t3    | index | a             | a    | 5       | NULL | 1000 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
2 rows in set, 1 warning (0.003 sec)

Note (Code 1003): with CTE1 as (select /*+ QB_NAME(`aaa`) */ `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 3)select /*+ NO_RANGE_OPTIMIZATION(`t3`@`aaa`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t3` join `test`.`t3` where `test`.`t3`.`a` < 3 and `test`.`t3`.`a` < 3
MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> explain extended with CTE1 as (select /*+ QB_NAME(aaa) NO_RANGE_OPTIMIZATION(t3@aaa s ) */ t3.a from t3 where t3.a<3) select * from CTE1 a, CTE1 b;
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|    1 | SIMPLE      | t3    | range | a             | a    | 5       | NULL | 2    |   100.00 | Using where; Using index                                     |
|    1 | SIMPLE      | t3    | range | a             | a    | 5       | NULL | 2    |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
2 rows in set, 3 warnings (0.001 sec)

Warning (Code 4205): Unresolved index name `t3`@`aaa` `s` for NO_RANGE_OPTIMIZATION hint
Warning (Code 4205): Unresolved index name `t3`@`aaa` `s` for NO_RANGE_OPTIMIZATION hint
Note (Code 1003): with CTE1 as (select /*+ QB_NAME(`aaa`) */ `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 3)select `test`.`t3`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t3` join `test`.`t3` where `test`.`t3`.`a` < 3 and `test`.`t3`.`a` < 3