EXPLAIN FORMAT=JSON in MySQL

There are some things that we (MariaDB) are not happy with in MySQL/Oracle's implementation of EXPLAIN FORMAT=JSON.

Some things are already fixed, see EXPLAIN FORMAT=JSON Differences From MySQL.

This page lists things are are not fixed yet.

High priority

These will definitely be fixed

Join buffering doesn't show condition for buffered table

MySQL [dbt3sf1]> explain format=json select * from orders,customer where o_orderdate between '1995-01-01' and '1995-02-02' and c_acctbal <0 and o_custkey + 1 = c_custkey -1 \G

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "orders",
          "access_type": "range",
          "possible_keys": [
            "i_o_orderdate"
          ],
          "key": "i_o_orderdate",
          "used_key_parts": [
            "o_orderDATE"
          ],
          "key_length": "4",
          "rows": 40676,
          "filtered": 100,
          "index_condition": "(`dbt3sf1`.`orders`.`o_orderDATE` between '1995-01-01' and '1995-02-02')"
        }
      },
      {
        "table": {
          "table_name": "customer",
          "access_type": "ALL",
          "possible_keys": [
            "c_acctbal",
            "i_c_acctbal_nationkey"
          ],
          "rows": 149415,
          "filtered": 18.037,
          "using_join_buffer": "Block Nested Loop",
          "attached_condition": "((`dbt3sf1`.`customer`.`c_acctbal` < 0) and ((`dbt3sf1`.`orders`.`o_custkey` + 1) = (`dbt3sf1`.`customer`.`c_custkey` - 1)))"
        }
      }
    ]
  }
}

Nice to have

These are not show-stoppers, but are highly-desirable.

Range doesn't show the ranges that are scanned

Optimizer trace has this information, but it would be nice if EXPLAIN showed the ranges for the query plan.

MySQL [dbt3sf1]> explain format=json select * from customer where c_acctbal < -1000 \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "customer",
      "access_type": "range",
      "possible_keys": [
        "c_acctbal",
        "i_c_acctbal_nationkey"
      ],
      "key": "c_acctbal",
      "used_key_parts": [
        "c_acctbal"
      ],
      "key_length": "9",
      "rows": 1,
      "filtered": 100,
      "index_condition": "(`dbt3sf1`.`customer`.`c_acctbal` < -(1000))"
    }
  }
}

Smaller stuff

filesort priority queue optimization is not shown

Neither version of EXPLAIN in 5.6 shows the "filesort with small limit" optimization. See MDEV-6430.