Bug #80559 EXPLAIN CREATE TABLE .. AS SELECT .. does not work
Submitted: 29 Feb 2016 14:32 Modified: 29 Feb 2016 15:17
Reporter: Justin Swanhart Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[29 Feb 2016 14:32] Justin Swanhart
Description:
explain create table p_idx (primary key(p_mfgr, p_partkey)) as select p_mfgr, p_partkey from part;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table p_idx select p_mfgr, p_partkey from part' at line 1

explain create table p_idx (primary key(p_mfgr, p_partkey)) as select p_mfgr, p_partkey from part;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table p_idx (primary key(p_mfgr, p_partkey)) as select p_mfgr, p_partkey ' at line 1

EXPLAIN for INSERT .. SELECT works though:
explain insert into p_idx select p_mfgr, p_partkey from part;+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | INSERT      | p_idx | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |   NULL |     NULL | NULL        |
|  1 | SIMPLE      | part  | NULL       | index | NULL          | P_MFGR | 13      | NULL | 993167 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
2 rows in set (0.00 sec)

How to repeat:
see above

Suggested fix:
support explain CREATE ... SELECT
[29 Feb 2016 14:37] Justin Swanhart
CREATE .. SELECT .. is a combination of DDL and DML.  The statement should be explainable because it contains DML.  If you want to mark it as FR you can, but this IS A BUG (do not mark NOT A BUG!) even though CREATE is not documented as being explainable, CREATE .. SELECT is DML.
[29 Feb 2016 14:43] Roy Lyseng
IMHO CREATE ... SELECT is a DDL statement with an embedded DML statement.
[29 Feb 2016 15:16] Sinisa Milivojevic
Justin,

I thought of making this "!Bug", but you wrote to us that we should not dare doing something like that. However, on the other hand, I agree with Roy totally.

On further reflection , I must admit that this is not documented well, so I am verifying this one as a documentation bug !!!!
[29 Feb 2016 15:17] Justin Swanhart
Well yeah, which I why I said it is a combination.  It really should not be hard to support.  It should work just like INSERT (show an ALL for the table to be created) and plan the SELECT.  If I get around to writing a patch I will.

This is not a DOC bug though.  The documentation is correct.  Changing to "parser".