Description:
If a field in the ORDER BY list refers to the alias of the subquery in the select list, the explain plan output when format is tree duplicate subquery plan.
How to repeat:
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30);
CREATE TABLE t2 SELECT DISTINCT a FROM t1;
ANALYZE TABLE t1, t2;
mysql-8.0.33 > EXPLAIN FORMAT=TREE SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(b) FROM t1 GROUP BY aa, b ORDER BY -aa, -b;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: -(aa), -(t1.b)
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Table scan on t1 (cost=0.75 rows=5)
-> Select #2 (subquery in projection; dependent)
-> Filter: (t2.a = t1.a) (cost=0.35 rows=1)
-> Table scan on t2 (cost=0.35 rows=4)
-> Select #2 (subquery in projection; dependent)
-> Filter: (t2.a = t1.a) (cost=0.35 rows=1)
-> Table scan on t2 (cost=0.35 rows=4)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
As you can see above, the execution plan output of the subquery is repeated twice.
Suggested fix:
The root cause is when looking for Items that were materialized in the function GetAccessPathsFromSelectList(), it collects subqueries from Temp_table_param::items_to_copy. But there could exist multiple Items which refer to the same subquery. This causes the duplicate prints.
The fix could be collecting the subqueries for deduplication and then printing their execution plans.
Description: If a field in the ORDER BY list refers to the alias of the subquery in the select list, the explain plan output when format is tree duplicate subquery plan. How to repeat: CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); CREATE TABLE t2 SELECT DISTINCT a FROM t1; ANALYZE TABLE t1, t2; mysql-8.0.33 > EXPLAIN FORMAT=TREE SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(b) FROM t1 GROUP BY aa, b ORDER BY -aa, -b; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Sort: -(aa), -(t1.b) -> Table scan on <temporary> -> Aggregate using temporary table -> Table scan on t1 (cost=0.75 rows=5) -> Select #2 (subquery in projection; dependent) -> Filter: (t2.a = t1.a) (cost=0.35 rows=1) -> Table scan on t2 (cost=0.35 rows=4) -> Select #2 (subquery in projection; dependent) -> Filter: (t2.a = t1.a) (cost=0.35 rows=1) -> Table scan on t2 (cost=0.35 rows=4) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ As you can see above, the execution plan output of the subquery is repeated twice. Suggested fix: The root cause is when looking for Items that were materialized in the function GetAccessPathsFromSelectList(), it collects subqueries from Temp_table_param::items_to_copy. But there could exist multiple Items which refer to the same subquery. This causes the duplicate prints. The fix could be collecting the subqueries for deduplication and then printing their execution plans.