Description:
For SQL like:
SELECT * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
I think the join order of tables alias1, alias2, and alias3 is arbitrary, as long as table t_o is joined first. In MySQL 8.0.13, the join order can be changed freely as expected. But in MySQL 8.0.33, it cannot, which I think is a regression for the optimizer.
How to repeat:
CREATE TABLE t_o (a INT);
CREATE TABLE t_i (b INT);
INSERT INTO t_o VALUES (3), (9), (10);
INSERT INTO t_i VALUES (29), (8), (5);
Behavior in MySQL 8.0.13:
mysql-8.0.13 > EXPLAIN SELECT * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
We can change the join order freely with optimizer hints:
mysql-8.0.13 > EXPLAIN SELECT /*+ JOIN_PREFIX(t_o, alias2) */ * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
mysql-8.0.13 > EXPLAIN SELECT /*+ JOIN_PREFIX(t_o, alias3) */ * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
Behavior in MySQL 8.0.33:
mysql-8.0.33 > EXPLAIN SELECT * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
But we cannot change the join order any more with optimizer hints:
mysql-8.0.33 > EXPLAIN SELECT /*+ JOIN_PREFIX(t_o, alias2) */ * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
mysql-8.0.33 > EXPLAIN SELECT /*+ JOIN_PREFIX(t_o, alias3) */ * FROM (
SELECT t_o.a, alias1.b as a1_b, alias2.b as a2_b, alias3.b as a3_b
FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2
) de;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
What's more, there is a strange thing in MySQL 8.0.13, which is we also cannot change the join order if we remove the outermost derived table:
mysql-8.0.13 > EXPLAIN SELECT /*+ JOIN_PREFIX(t_o,alias2) */ * FROM t_o
LEFT JOIN t_i as alias1 ON t_o.a = alias1.b
LEFT JOIN t_i as alias2 ON t_o.a = alias2.b
LEFT JOIN t_i as alias3 ON t_o.a = alias3.b
WHERE t_o.a > 2;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t_o | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | alias1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | alias3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
Suggested fix:
This issue is due to incorrect and inconsistent settings on Table_ref::dep_tables in Query_block::simplify_joins.
Actually, tables alias1, alias2, and alias3 only depend on the table t_o and have no dependencies on each other. The optimizer could have had a larger join order search space.
