Description:
- MySQL 8.0.16 added the TREE explain format.
- MySQL 8.0.32 added the explain_format variable and by default it was set to TRADITIONAL.
- MySQL 9.5 changed the explain_format variable to be TREE by default.
Overall this is a win as the new format gives more and better to understand information.
However from a usability standpoint it doesn't work well with MySQL Client and MySQL Shell. Other commandline clients may also be affected.
Example:
```
mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TRADITIONAL SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 2 warnings (0.001 sec)
Note (Code 1249): Select 3 was reduced during optimization
Note (Code 1003): /* select#1 */ select 1 AS `1` union all /* select#2 */ select 2 AS `2`
mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Append (cost=0..0 rows=2)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
Note (Code 1249): Select 3 was reduced during optimization
mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1)\G
*************************** 1. row ***************************
EXPLAIN: -> Append (cost=0..0 rows=2)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
1 row in set, 1 warning (0.001 sec)
Note (Code 1249): Select 3 was reduced during optimization
```
Note that with a traditional explain plan `EXPLAIN <query>;` just works.
With the TREE format `EXPLAIN <query>;` this results in overly wide output that causes line wrapping to kick in.
With the TREE format `EXPLAIN <query>\G` (verical output) this results in a misformed tree, where the first item (`Append` in this case) is indented too much because `EXPLAIN:` is printed on the same line.
Note also that there is a warning/note outside of the explain that has lost its context.
To compare this with the tree format of PostgreSQL:
```
postgres=# EXPLAIN SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1);
QUERY PLAN
--------------------------------------------------------
Append (cost=0.00..0.04 rows=2 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
-> Result (cost=0.01..0.02 rows=1 width=4)
One-Time Filter: (InitPlan 1).col1
InitPlan 1
-> Result (cost=0.00..0.01 rows=1 width=1)
(6 rows)
```
- Just `EXPLAIN <query>;` without any overly long lines etc.
- No warnings/notes outside of the plan
Note that while this bug is for FORMAT=TREE, this also affects FORMAT=JSON, but there it is less noticeable.
How to repeat:
See description
Suggested fix:
## 1. Quick hack
```
mysql -h 127.0.0.1 -u root --show-warnings -Be 'EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1)' 2>/dev/null | sed 's/\\n/\n/g'
EXPLAIN
-> Append (cost=0..0 rows=2)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
Note (Code 1249): Select 3 was reduced during optimization
```
1. This disabled the table drawing (`--silent`/`-s`)
2. The items of the tree have the correct indentation
3. This still has a title (`EXPLAIN`), which is nice.
4. This doesn't fix the note, which isn't part of the plan.
## 2. Quick hack, extended
The client (MySQL Client, MySQL Shell, etc) could detect the explain statement and handle the output differently.
Explain here for how the output could look like.
```
mysql-9.6.0 [(none)]> \W
Show warnings enabled.
mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1);
EXPLAIN
-> Append (cost=0..0 rows=2)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
-> Stream results (cost=0..0 rows=1)
-> Rows fetched before execution (cost=0..0 rows=1)
1 row in set, 1 warning (0.001 sec)
Note (Code 1249): Select 3 was reduced during optimization
```
## 3. Fixing this in a more generic way
1. Add a flag to the output as send by the server like RESULTSET_NO_TABLE_FORMAT
2. Then the client can disable table formatting for this resultset.
This would work for EXPLAIN with FORMAT=TREE, but also with `SHOW CREATE TABLE`.
## 4. The note/warning needs separate handling.
That's not for the client to do. I think the notes/warnings made sense for FORMAT=TRADITIONAL, but not for FORMAT=TREE (except for the code=1003 one).
Description: - MySQL 8.0.16 added the TREE explain format. - MySQL 8.0.32 added the explain_format variable and by default it was set to TRADITIONAL. - MySQL 9.5 changed the explain_format variable to be TREE by default. Overall this is a win as the new format gives more and better to understand information. However from a usability standpoint it doesn't work well with MySQL Client and MySQL Shell. Other commandline clients may also be affected. Example: ``` mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TRADITIONAL SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 2 warnings (0.001 sec) Note (Code 1249): Select 3 was reduced during optimization Note (Code 1003): /* select#1 */ select 1 AS `1` union all /* select#2 */ select 2 AS `2` mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Append (cost=0..0 rows=2) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.001 sec) Note (Code 1249): Select 3 was reduced during optimization mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1)\G *************************** 1. row *************************** EXPLAIN: -> Append (cost=0..0 rows=2) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) 1 row in set, 1 warning (0.001 sec) Note (Code 1249): Select 3 was reduced during optimization ``` Note that with a traditional explain plan `EXPLAIN <query>;` just works. With the TREE format `EXPLAIN <query>;` this results in overly wide output that causes line wrapping to kick in. With the TREE format `EXPLAIN <query>\G` (verical output) this results in a misformed tree, where the first item (`Append` in this case) is indented too much because `EXPLAIN:` is printed on the same line. Note also that there is a warning/note outside of the explain that has lost its context. To compare this with the tree format of PostgreSQL: ``` postgres=# EXPLAIN SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1); QUERY PLAN -------------------------------------------------------- Append (cost=0.00..0.04 rows=2 width=4) -> Result (cost=0.00..0.01 rows=1 width=4) -> Result (cost=0.01..0.02 rows=1 width=4) One-Time Filter: (InitPlan 1).col1 InitPlan 1 -> Result (cost=0.00..0.01 rows=1 width=1) (6 rows) ``` - Just `EXPLAIN <query>;` without any overly long lines etc. - No warnings/notes outside of the plan Note that while this bug is for FORMAT=TREE, this also affects FORMAT=JSON, but there it is less noticeable. How to repeat: See description Suggested fix: ## 1. Quick hack ``` mysql -h 127.0.0.1 -u root --show-warnings -Be 'EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1)' 2>/dev/null | sed 's/\\n/\n/g' EXPLAIN -> Append (cost=0..0 rows=2) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) Note (Code 1249): Select 3 was reduced during optimization ``` 1. This disabled the table drawing (`--silent`/`-s`) 2. The items of the tree have the correct indentation 3. This still has a title (`EXPLAIN`), which is nice. 4. This doesn't fix the note, which isn't part of the plan. ## 2. Quick hack, extended The client (MySQL Client, MySQL Shell, etc) could detect the explain statement and handle the output differently. Explain here for how the output could look like. ``` mysql-9.6.0 [(none)]> \W Show warnings enabled. mysql-9.6.0 [(none)]> EXPLAIN FORMAT=TREE SELECT 1 UNION ALL SELECT 2 WHERE (SELECT 1=1); EXPLAIN -> Append (cost=0..0 rows=2) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Stream results (cost=0..0 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) 1 row in set, 1 warning (0.001 sec) Note (Code 1249): Select 3 was reduced during optimization ``` ## 3. Fixing this in a more generic way 1. Add a flag to the output as send by the server like RESULTSET_NO_TABLE_FORMAT 2. Then the client can disable table formatting for this resultset. This would work for EXPLAIN with FORMAT=TREE, but also with `SHOW CREATE TABLE`. ## 4. The note/warning needs separate handling. That's not for the client to do. I think the notes/warnings made sense for FORMAT=TRADITIONAL, but not for FORMAT=TREE (except for the code=1003 one).