Description:
Traditional and json explain plans show different results than tree plan, regarding which table is hashed in inner hash join.
How to repeat:
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
-- Example that works fine, with LEFT join:
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
/*
It looks like table t2 is hashed.
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
*/
EXPLAIN FORMAT=TREE
SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
/*
Yes, table t2 is hashed, indeed. Plan obtained using FORMAT=JSON also proves that.
+-----------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------+
| -> Left hash join (t2.c1 = t1.c1) (cost=2.9 rows=25)
-> Table scan on t1 (cost=0.75 rows=5)
-> Hash
-> Table scan on t2 (cost=0.15 rows=5)
|
+-----------------------------------------------------------+
*/
-- Example with erroreous plan. Now let's change join type to INNER, and TABLE/TREE plans do not match:
EXPLAIN
SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
/*
Traditional and JSON plans show that table t2 is hashed.
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
*/
-- But TREE plan claims that table t1 is hashed, not t2.
EXPLAIN FORMAT=TREE
SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
/*
+----------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------+
| -> Inner hash join (t2.c1 = t1.c1) (cost=3.5 rows=5)
-> Table scan on t2 (cost=0.07 rows=5)
-> Hash
-> Table scan on t1 (cost=0.75 rows=5)
|
+----------------------------------------------------------+
*/
Description: Traditional and json explain plans show different results than tree plan, regarding which table is hashed in inner hash join. How to repeat: CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -- Example that works fine, with LEFT join: EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; /* It looks like table t2 is hashed. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ */ EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; /* Yes, table t2 is hashed, indeed. Plan obtained using FORMAT=JSON also proves that. +-----------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------+ | -> Left hash join (t2.c1 = t1.c1) (cost=2.9 rows=25) -> Table scan on t1 (cost=0.75 rows=5) -> Hash -> Table scan on t2 (cost=0.15 rows=5) | +-----------------------------------------------------------+ */ -- Example with erroreous plan. Now let's change join type to INNER, and TABLE/TREE plans do not match: EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1; /* Traditional and JSON plans show that table t2 is hashed. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ */ -- But TREE plan claims that table t1 is hashed, not t2. EXPLAIN FORMAT=TREE SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1; /* +----------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------+ | -> Inner hash join (t2.c1 = t1.c1) (cost=3.5 rows=5) -> Table scan on t2 (cost=0.07 rows=5) -> Hash -> Table scan on t1 (cost=0.75 rows=5) | +----------------------------------------------------------+ */