MySQL Bugs: #67632: explain select 1 from (select sleep(10) from dual) as t1; runs 10 seconds in 5.6

Bug #67632	explain select 1 from (select sleep(10) from dual) as t1; runs 10 seconds in 5.6
Submitted:	19 Nov 2012 12:05	Modified:	13 Feb 2018 22:50
Reporter:	Sveta Smirnova	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.8, 5.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Found when verified bug #67631

This is more feature request.

Version 5.6 more efficiently handles subqueries in the FROM clause: 

----<q>----
Materialization of subqueries in the FROM clause is postponed until their contents are needed during query execution, which improves performance. Previously, subqueries in the FROM clause were materialized for EXPLAIN SELECT statements. This resulted in partial SELECT execution, even though the purpose of EXPLAIN is to obtain query plan information, not to execute the query. This materialization no longer occurs, so EXPLAIN is faster for such queries. For non-EXPLAIN queries, delay of materialization may result in not having to do it at all. Consider a query that joins the result of a subquery in the FROM clause to another table: If the optimizer processes that other table first and finds that it returns no rows, the join need not be carried out further and the optimizer can completely skip materializing the subquery. 
----</q>----

(http://dev.mysql.com/doc/refman/5.6/en/news-5-6-3.html)

This works fine if you access tables in the subquery like in bug #67631

But if you select from DUAL (no actual data), subquery still executes even if you just wanted to run EXPLAIN.

How to repeat:
mysql> explain select 1 from (select sleep(10) from dual) as t1;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
2 rows in set (10.00 sec)

Suggested fix:
Please fix this case for EXPLAIN too.

Posted by developer:
 
This is not a bug, but related to how MySQL optimizes queries.
If MySQL can evaluate an expression, also subqueries, it will do that,
in order to simplify queries as much as possible and potentially
reduce join order calculation as much as possible.

We might implement an option to not evaluate such constant expressions,
this could be a reasonable feature request, though.

Why this is not a verified feature request, S4?

I think it can become dangerous if it executes a function that changes the data

CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET latin1
    DETERMINISTIC
BEGIN 
delete from test.t1;
RETURN 'OK';
END

mysql> insert into t1 values(1); 
select * from t1; 
explain select * from (select cleanup()) as t1clean; 
select * from t1;
Query OK, 1 row affected (0.00 sec)
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| 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           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

Empty set (0.00 sec)