Bug #69795 EXPLAIN FORMAT=JSON doesn't show Using filesort for UNIONs
Submitted: 19 Jul 2013 17:54 Modified: 16 Jun 2016 20:32
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: explain, EXPLAIN FORMAT=JSON

[19 Jul 2013 17:54] Sergey Petrunya
Description:
EXPLAIN FORMAT=JSON does not show "Using filesort" for result of UNION. 

How to repeat:
create table t1 (a int);
insert into t1 values (1),(2),(3);

create table t2 (a int);
insert into t2 values (1),(2),(3);

## Let's try a UNION
explain select a from t1 union select a from t2;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL            |
|  2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)

## The same UNION with ORDER BY:

explain select a from t1 union select a from t2 order by a;
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                            |
|  2 | UNION        | t2         | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL                            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary; Using filesort |
+----+--------------+------------+------+---------------+------+---------+------+------+---------------------------------+
3 rows in set (0.01 sec)

## Now, let's try the same with format=json
## Both queries will produce the same JSON output:

explain format=json select a from t1 union select a from t2;
explain format=json select a from t1 union select a from t2 order by a;
| {
  "query_block": {
    "union_result": {
      "using_temporary_table": true,
      "table_name": "<union1,2>",
      "access_type": "ALL",
      "query_specifications": [
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 1,
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows": 3,
              "filtered": 100
            }
          }
        },
        {
          "dependent": false,
          "cacheable": true,
          "query_block": {
            "select_id": 2,
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "rows": 3,
              "filtered": 100
            }
          }
        }
      ]
    }
  }
} |

Suggested fix:
Make EXPLAIN FORMAT=JSON show "Using filesort" when appropriate
[20 Jul 2013 8:20] MySQL Verification Team
Hello Sergey,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh
[16 Jun 2016 20:32] Sergey Petrunya
Still repeatable on 5.7.12