WL#7027: Deprecate EXTENDED and PARTITIONS flags of EXPLAIN

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

This task is to deprecate the EXTENDED and PARTITIONS keyword of EXPLAIN syntax. 
Current EXPLAIN has EXTENDED and PARTITIONS flags that shows additional info 
compared to regular EXPLAIN output. Those flags are supported by many 'if's in the 
code and that complicates it considerably. Another reason for deprecation that 
EXPLAIN JSON already behaves like those flags are on and prints all available 
info. So this deprecation would be good also from the consistency POV. The goal of 
this WL is to make those flags always turned on, deprecate flags themselves and 
eventually remove them from supported syntax. After deprecation regular EXPLAIN 
will behave exactly as if those flags are given - all additional columns and 
warnings would be printed automatically.

EXPLAIN by default will print additional information as if EXTENDED and
PARTITIONS flags are turned on from 5.7.  We will also issue deprecation
warnings if the EXTENDED and PARTITIONS flags are turned on in 5.7. These flags
will be removed starting from 5.8.


Approved by serverPT May 22, 2013

User Documentation
==================

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
http://dev.mysql.com/doc/refman/5.7/en/explain.html
http://dev.mysql.com/doc/refman/5.7/en/explain-output.html
http://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
Functional requirement:
----------------------
1. If Explain statement without any of EXTENDED and PARTITIONS flags, the result
will be the same as the one combined both of these flags before WL#7027, i.e.,
after WL#7027, Explain will show all additional info like both of EXTENDED and
PARTITIONS flags on before WL#7027. 

2. If Explain with any of EXTENDED and PARTITIONS flags, one deprecation warning
will be thrown out after WL#7027.

Non-functional requirements:
---------------------------
1. There's no effect on 'Explain format=JSON' statements.
Description
-----------
Currently, EXTENDED and PARTITIONS are options for Explain statement. From
WL#7027,these two options will be deprecated. If Explain statement without any
of these two options, the query result will be the same as the statement with
both before. If Explain statement with any of them, one deprecated
warning will occur respectively. MySQL will only support these two options on
semantic and all 'if's based on these two in source code will be removed.

Changes to syntax, results, errors and warnings
-----------------------------------------------
There is no change on Explain syntax and errors. However, Explain results will
show extra information just like EXTENDED and PARTITIONS are turned on. If any
of these two options are used in Explain statement, one deprecated warning will
occur.

Examples
--------

Before WL#7027:
mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1
| NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain extended select * from t;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
| filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    1
|   100.00 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                   |
+-------+------+-----------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS
`b` from `test`.`t` |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len
| ref  | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL   
| NULL |    1 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)


After WL#7027:
The explain output is the same for the above three cases except warnings issued.
The explain output is:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len
| ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL   
| NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+


Warnings:
1. explain select * from t;
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message                                                       
                   |
+-------+------+-----------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS
`b` from `test`.`t` |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. explain extended select * from t;
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                     
                     |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future
release.                 |
| Note    | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS
`b` from `test`.`t` |
+---------+------+-----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. explain partitions select * from t;
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                     
                     |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future
release.               |
| Note    | 1003 | /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS
`b` from `test`.`t` |
+---------+------+-----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)




1. Call WARN_DEPRECARED_NO_REPLACEMENT where EXTENDED_SYM or PARTITIONS_SYM is
at in sql_yacc.yy so that warnings will be thrown out when either EXTENDED or
PARTITIONS is used in explain statement.
  
2. Make DESCRIBE_EXTENDED and DESCRIBE_PARTITIONS always be true. Therefore, the
related source code in case DESCRIBE_EXTENDED or DESCRIBE_PARTITIONS is false
should be cleaned up.