Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
Description
Is the following a bug? Our NO_MERGE and derived_merge=off behaviors are consistent with each other, but MariaDB differs from MySQL.
In MySQL 9 (and 8.4), NO_MERGE/derived_merge=off both disable VIEW merging to the parent query as shown:
mysql> create view v2 as select * from t2;
|
Query OK, 0 rows affected (0.012 sec)
|
|
|
mysql> set optimizer_switch='derived_merge=off';
|
Query OK, 0 rows affected (0.003 sec)
|
|
|
mysql> explain select * from (select * from t2) as v2;
|
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
|
| 2 | DERIVED | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
|
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|
2 rows in set, 1 warning (0.005 sec)
|
|
|
mysql> show warnings;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select `v2`.`a` AS `a`,`v2`.`b` AS `b` from (/* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`) `v2` |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
mysql> set optimizer_switch='derived_merge=on';
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
mysql> explain select * from (select * from t2) as v2;
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|
1 row in set, 1 warning (0.001 sec)
|
However in MariaDB, they don't:
MariaDB [test]> create view v2 as select * from t2;
|
Query OK, 0 rows affected (0.006 sec)
|
|
|
MariaDB [test]> explain select * from v2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
1 row in set (0.005 sec)
|
|
|
MariaDB [test]> set optimizer_switch='derived_merge=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> explain select * from v2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
1 row in set (0.002 sec)
|