[MDEV-8425] Views based on FROM DUAL selects always evaluate all columns Created: 2015-07-04  Updated: 2022-09-08

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None


 Description    

CREATE OR REPLACE ALGORITHM = MERGE VIEW v AS
 
SELECT 1 a, SLEEP(3) b, SLEEP(3) c FROM DUAL;
 
 
 
EXPLAIN EXTENDED
 
SELECT a FROM v;

EXPLAIN EXTENDED shows us what we want to see... but it's a lie If you execute the SELECT, it will sleep for 6 seconds, because columns b and c are evaluated.

Note that the bug will NOT happen in this case:

\W
 
 
 
CREATE OR REPLACE TABLE t (a INT) ENGINE = InnoDB;
 
INSERT INTO t VALUES (1);
 
 
 
CREATE OR REPLACE ALGORITHM = MERGE VIEW v AS
 
SELECT a, SLEEP(3) b, SLEEP(3) c FROM t;
 
 
 
EXPLAIN EXTENDED
 
SELECT a FROM v;



 Comments   
Comment by Federico Razzoli [ 2015-07-04 ]

Correction: the problem is related to derived tables, not necessarily views. In fact, you can repeat it in this way:

SELECT a FROM
 
(SELECT 1 a, SLEEP(3) b, SLEEP(3) c FROM DUAL) v;

The above query is weird, but the view example comes from a real case.

Comment by Elena Stepanova [ 2015-07-08 ]

I suppose the reason is the same in both cases: the select needs to create a temporary table, and thus evaluates all columns. It does not help that you provide ALGORITHM=MERGE for the view, you still get the warning that it cannot be used.

Still, I'll assign it to psergey in case he can see a bug in here. For example, should the EXPLAIN evaluate the columns (it also takes full 6 sec), or should the explain output show what it shows:

MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT a FROM v;
 
+------+-------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
 
| id   | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
 
+------+-------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
 
|    1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                |
 
|    2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
 
+------+-------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
 
2 rows in set, 1 warning (6.01 sec)
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+---------------------------+
 
| Level | Code | Message                   |
 
+-------+------+---------------------------+
 
| Note  | 1003 | select 1 AS `a` from dual |
 
+-------+------+---------------------------+
 
1 row in set (0.00 sec)

Comment by Federico Razzoli [ 2015-07-09 ]

What about the warning? Don't you find it misleading, because it doesn't mention columns b and c?

Comment by Sergei Petrunia [ 2015-07-09 ]

I think the warning follows the pattern of what non-merged VIEWs do in EXPLAIN:

create view v10 as select a,max(b) as m from t10;
 
explain extended select a from v10;
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
 
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
 
|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 |       |
 
|    2 | DERIVED     | t10        | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 |       |
 
+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
 

MariaDB [j1]> show warnings\G
 
*************************** 1. row ***************************
 
  Level: Note
 
   Code: 1003
 
Message: select `v10`.`a` AS `a` from `j1`.`v10`
 
1 row in set (0.00 sec)

It's not perfect but this is how EXPLAIN EXTENDED works ATM (and alas, EXPLAIN FORMAT=JSON wont be much better in this regard).

Comment by Sergei Petrunia [ 2015-07-09 ]

Re-assigning to Sanja as this is a problem with VIEWs. Views that do SELECT ... FROM dual are a special case that is handled by the SQL layer. I am not sure if it is easy to resolve this bug or not.

Generated at Thu Feb 08 07:27:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.