Details
Minor
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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 Sergei Petrunia 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)
What about the warning? Don't you find it misleading, because it doesn't mention columns b and c?
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).
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.
Correction: the problem is related to derived tables, not necessarily views. In fact, you can repeat it in this way:
The above query is weird, but the view example comes from a real case.