Description:
The documentation (see https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html) clearly states that hash join is visible only in TREE format. However, this makes other formats inconsistent with what optimizer is actually planning for the execution of a query. For example in MySQL Workbench (which uses JSON format) even though a query uses hash join the visual and tabular explain plans show Block Nested Loop (BNL) instead. It would be nice to have the same operations in the different formats of explain.
How to repeat:
1. Take the example from documentation:
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
2. Run EXPLAIN on the following query:
SELECT *
FROM t1
JOIN t2
ON t1.c1=t2.c1;
You get the following explain plan:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.0191 sec)
Note (code 1003): /* select#1 */ select `dept_emp`.`t1`.`c1` AS `c1`,`dept_emp`.`t1`.`c2` AS `c2`,`dept_emp`.`t2`.`c1` AS `c1`,`dept_emp`.`t2`.`c2` AS `c2` from `dept_emp`.`t1` join `dept_emp`.`t2` where (`dept_emp`.`t2`.`c1` = `dept_emp`.`t1`.`c1`)
And it's the same in MySQL Workbench (see attached visual explain).
However, in TREE explain format for the same query you get this:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.0042 sec)
Suggested fix:
Have hash join appear in traditional and JSON formats of explain plan.
