Bug #109820 optimizer hints in definition of views seem to be ignored
Submitted: 27 Jan 2023 8:54 Modified: 27 Jan 2023 10:11
Reporter: Brian Yue (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.29, 8.0.32 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (x86-64)
Tags: Optimizer hints, VIEW

[27 Jan 2023 8:54] Brian Yue
Description:
Hello,
  Here we find that optimizer hints in definition of views are ignored, as a result the execution plan is different when we query the view, comparing to the execution plan when we query the select statement.

How to repeat:
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values (1), (2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain select /*+ NO_MERGE() */ * from (select *from t1) dt;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> create view v_1 as select /*+ NO_MERGE() */ * from (select *from t1) dt;
Query OK, 0 rows affected (0.01 sec)

#
# Here the result of explain is expected to be the same with above, actually it's not
#
mysql> explain select * from v_1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
[27 Jan 2023 10:11] MySQL Verification Team
Hello Brian Yue,

Thank you for the report and feedback.

regards,
Umesh
[28 Jan 2023 12:55] huahua xu
Hi Brian Yue,

This may be helpful for you:

mysql>  create ALGORITHM = TEMPTABLE view v_1 as select * from (select *from t1) dt;

mysql>  explain select * from v_1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+