| Bug #111216 | The user variable has an effect on view merge | ||
|---|---|---|---|
| Submitted: | 31 May 2023 10:14 | Modified: | 2 Jun 2023 9:20 |
| Reporter: | Xiong Wang | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2023 12:39]
MySQL Verification Team
Hi Mr. Wang, Thank you very much for your bug report. However, it is not a bug. There are two algorithms that can be specified for each of the views. By default, the algorithm that is chosen, if not specified differently, is the DERIVED TABLE algorithm. That corresponds to the first EXPLAIN that you presented to us. The second algorithm is MERGE algorithm. It can be specified, but it is accepted only if a view can be resolved by that algorithm. If it is not possible, you get an error or warning, depending on the circumstances, as explained in our Reference Manual. When MERGE algorithm is chosen and accepted by our Optimiser, then the query in it is merged in the basic query and the entire query is reduced to the minimal one possible. That is the reason why you get different EXPLAIN in the second case. In short, this is expected behaviour , as it is explained in our Reference Manual. Not a bug.
[1 Jun 2023 1:37]
Xiong Wang
I still think this is a problem. Pls take a look at the following test cases: mysql> create or replace view v as select * from t; Query OK, 0 rows affected (0.13 sec) mysql> show create view v; +------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`a` AS `a`,`t`.`b` AS `b` from `t` | utf8mb4 | utf8mb4_0900_ai_ci | +------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec) mysql> explain SELECT (@row_number:=@row_number + 1) AS num, a FROM v, (SELECT @row_number:=0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 3 | DERIVED | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 4 rows in set, 3 warnings (0.01 sec) mysql> explain SELECT a FROM v, (SELECT 0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | t | NULL | index | NULL | idx | 5 | NULL | 2 | 100.00 | Using index | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 3 rows in set, 1 warning (0.00 sec) mysql> create or replace view v1 as select * from t; Query OK, 0 rows affected (0.02 sec) mysql> explain SELECT (@row_number:=@row_number + 1) AS num, a FROM v, v1, (SELECT @row_number:=0) AS t; ERROR 1052 (23000): Column 'a' in field list is ambiguous mysql> explain SELECT (@row_number:=@row_number + 1) AS num, v.a FROM v, v1, (SELECT @row_number:=0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 1 | PRIMARY | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (hash join) | | 4 | DERIVED | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 3 | DERIVED | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------------------------------+ 6 rows in set, 3 warnings (0.00 sec) mysql> explain SELECT v.a FROM v, v1, (SELECT 0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | t | NULL | index | NULL | idx | 5 | NULL | 2 | 100.00 | Using index | | 1 | PRIMARY | t | NULL | index | NULL | idx | 5 | NULL | 2 | 100.00 | Using index; Using join buffer (hash join) | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) From the above cases, views are not merged out because of an unrelated user variable. This might cause a performance regression.
[1 Jun 2023 1:39]
Xiong Wang
The above case shows user variables might change query plan even ALGORITHM=UNDEFINED.
[1 Jun 2023 12:08]
MySQL Verification Team
Hi Mr. Wang, What you describe is true. The optimiser will do its best to merge the views, that are defined by MERGE algorithm, but, there are limitations as when it is possible and when it is not possible. This is all described in our Reference Manual. Please, do not report here problems that are part of the design, as described in our Reference Manual. Not a bug.
[2 Jun 2023 9:17]
Guilhem Bichot
Hello. The reporter is pointing out this real problem: - there is a view (with no specified algorithm): create view v as select * from t; - there is an initial query, where MySQL chooses to merge the view: mysql> explain SELECT v.a FROM v; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) Note (Code 1003): /* select#1 */ select `w3`.`t`.`a` AS `a` from `w3`.`t` - Now we add a user-variable with an assignment to it, in the query, and no other change: mysql> explain SELECT (@u:=@u+1), v.a FROM v; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | t | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 2 warnings (0.00 sec) Warning (Code 1287): Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Note (Code 1003): /* select#1 */ select (@u:=((@`u`) + 1)) AS `(@u:=@u+1)`,`w3`.`v`.`a` AS `a` from `w3`.`v` We see that this user variable has an unexpected effect: it causes the view to not be merged anymore. And there is, as far as I know, no sensible explanation for this effect. The consequence is that a query may be fast (due to view merging), but if you add the user variable, it becomes slow (materialized). On the other hand, there is a deprecation warning in EXPLAIN above, so a possible answer could be "ok, but not worth fixing".
[2 Jun 2023 9:20]
Xiong Wang
Change title description
[2 Jun 2023 11:57]
MySQL Verification Team
Salut Guilhem, mon ami, This is how MERGE algorithm is working and it is documented in our manual. Simply, read the entire Reference Manual and you will find the answer. Beside that, there is a verbose warning, so there is nothing left to fix. Not a bug.
Description: mysql> create view v as select * from t; Query OK, 0 rows affected (0.30 sec) mysql> explain SELECT (@row_number:=@row_number + 1) AS num, a FROM v, (SELECT @row_number:=0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 3 | DERIVED | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 4 rows in set, 3 warnings (0.00 sec) mysql> create or replace ALGORITHM=MERGE view v as select * from t; Query OK, 0 rows affected (0.05 sec) mysql> explain SELECT (@row_number:=@row_number + 1) AS num, a FROM v, (SELECT @row_number:=0) AS t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | PRIMARY | t | NULL | index | NULL | idx | 5 | NULL | 2 | 100.00 | Using index | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 3 rows in set, 3 warnings (0.01 sec) The above same view with different ALGORITHM causes a different query plan, which might cause a big performance regression. How to repeat: CREATE TABLE t(a INT, b INT); INSERT INTO t VALUES(1,2),(3,4); CREATE VIEW v AS SELECT * FROM t; EXPLAIN SELECT (@row_number:=@row_number + 1) AS num, a FROM v,(SELECT @row_number:=0) AS t; CREATE OR REPLACE ALGORITM=MERGE VIEW v AS SELECT * FROM t; EXPLAIN SELECT (@row_number:=@row_number + 1) AS num,a FROM v, (SELECT @row_number:=0) AS t; Suggested fix: SELECT_LEX_UNIT::merge_heuristic(const LEX *lex) { if (lex->set_var_list.elements != 0) return false; ==> Here means any of variables is included in a query, any of views related with this query might not been merged out if algorithm of views is UNDEFINED or default. This is not reasonable. ... }