Bug #114714 Duplicate subquery explain plan output when explain format is tree
Submitted: 20 Apr 3:54 Modified: 22 Apr 9:32
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[20 Apr 3:54] Hope Lee
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.
[20 Apr 3:55] Hope Lee
Bugfix Duplicate subquery explain plan output when explain format is tree

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bugfix-Duplicate-subquery-explain-plan-output-when-e.patch (application/octet-stream, text), 4.09 KiB.

[22 Apr 9:32] MySQL Verification Team
Hello Lee,

Thank you for the report and contribution.

regards,
Umesh