MySQL Bugs: #62617: EXPLAIN EXTENDED wiht DELETE, UPDATE etc.

Bug #62617	EXPLAIN EXTENDED wiht DELETE, UPDATE etc.
Submitted:	4 Oct 2011 15:34	Modified:	28 Jan 2013 18:48
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.3, 5.6.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
The passage here does not tell that EXPLAIN EXTENDED only works with SELECT. It is a little unclear (but I guess I would read it as it is supported).

http://dev.mysql.com/doc/refman/5.6/en/explain.html

"When you precede a statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information. 

As of MySQL 5.6.3, EXPLAIN provides information about SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. Before MySQL 5.6.3, EXPLAIN provides information only about SELECT statements."

How to repeat:
-- EXPLAIN EXTENDED + SHOW WARNINGS only works for SELECT

CREATE TABLE tabl (id INT);
EXPLAIN EXTENDED SELECT * FROM tabl; 
SHOW WARNINGS; -- expected output; the optimized query displays
EXPLAIN EXTENDED DELETE FROM tabl; 
SHOW WARNINGS; --  An empty result set is returned. Server does not realize that this query comes immediately after EXPLAIN EXTENDED.

Suggested fix:
If EXPLAIN EXTENDED DELETE is not supposed to be supported (what I think it should!) EXPLAIN EXTENDED DELETE etc. should return an warning/error.  And docs should be updated to state clearly if constructs like EXPLAIN EXTENDED DELETE .. are supported or not.

Thank you for the problem report. Verified with current mysql-trunk:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-m5 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> explain delete from mysql.host;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL |    0 | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

mysql> explain extended delete from mysql.host;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL |    0 |     NULL | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------+
1 row in set (0.00 sec)

mysql> show warnings\G
Empty set (0.00 sec)

Also see http://bugs.mysql.com/bug.php?id=67022

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

"
EXPLAIN EXTENDED can be used with SELECT, DELETE, INSERT, REPLACE,
and UPDATE statements. However, the following SHOW WARNINGS statement
displays a nonempty result only for SELECT statements.
"