Bug #67022 EXPLAIN EXTENDED FORMAT= .. not allowed

• Submitted: 30 Sep 2012 11:38
• Modified: 4 Oct 2012 21:29
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.6.7
• OS: Any
• CPU Architecture: Any

[30 Sep 2012 11:38] Peter Laursen

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

"As of MySQL 5.6.5, the FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format. With FORMAT = JSON, the output includes extended and partition information."

How to repeat:
This can be seen here:

EXPLAIN FORMAT=json SELECT * FROM t LIMIT 10

/*returns 

{                                                                                                                                                                
  "query_block": {                                                                                                                                               
    "select_id": 1,                                                                                                                                              
    "table": {                                                                                                                                                   
      "table_name": "t",                                                                                                                                         
      "access_type": "ALL",                                                                                                                                      
      "rows": 1,                                                                                                                                                 
      "filtered": 100                                                                                                                                            
    }                                                                                                                                                            
  }                                                                                                                                                              
}   
*/

.. but:

EXPLAIN EXTENDED FORMAT=json SELECT * FROM t LIMIT 10;
-- Error Code: 1064 -- You have an error in your SQL syntax ...

.. and even worse:

EXPLAIN EXTENDED FORMAT=traditional SELECT * FROM t LIMIT 10;
-- Error Code: 1064 -- You have an error in your SQL syntax

Suggested fix:
The page at http://dev.mysql.com/doc/refman/5.6/en/explain.html does not tell that FORMAT and EXTENDED keywrods cannot be used at the same time

If FORMAT is specified as 'traditional' I consider this a plain bug. For FORMAT specified to 'json' this could be consiered a documentation issue.  But why not allow "EXPLAIN EXTENDED FORMAT=json ... ". It does no harm

[30 Sep 2012 11:41] Peter Laursen

An older report of mine that also is an example of that MySQL developers seem completely to have forgotten about EXPLAIN EXTENDED:

http://bugs.mysql.com/bug.php?id=62617

(and changing category to 'parser')

[2 Oct 2012 23:13] MySQL Verification Team

Thank you for the bug report.

[3 Oct 2012 16:09] Gleb Shchepa

This behavior is documented at the beginning of http://dev.mysql.com/doc/refman/5.6/en/explain.html :

: 13.8.2. EXPLAIN Syntax
: 
: EXPLAIN [explain_type] explainable_stmt
: 
: explain_type:
:     EXTENDED
:   | PARTITIONS
:   | FORMAT = format_name

I.e. "EXTENDED", "PARTITIONS" and "FORMAT=..." options are mutually exclusive.

[3 Oct 2012 18:38] Peter Laursen

OK .. point taken.  But why?

[4 Oct 2012 21:29] Omer Barnir

Based on above explanation - closing as 'Not a Bug'