From b89390c271c20294bf1c87d5e67c25f6b3a4c8b1 Mon Sep 17 00:00:00 2001 From: Hope Lee Date: Sat, 20 Apr 2024 11:49:56 +0800 Subject: [PATCH] Bugfix Duplicate subquery explain plan output when explain format is tree 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. Analysis -------- 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. Fix --- Collect the subqueries for deduplication and then printing their execution plans. --- mysql-test/r/group_by.result | 13 +++++++++++++ mysql-test/t/group_by.test | 2 ++ sql/join_optimizer/explain_access_path.cc | 18 ++++++++++++++++-- 3 files changed, 31 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 51b07c2dfb9..68e0c68fa2a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2107,6 +2107,19 @@ aa b COUNT( b) 3 30 1 2 20 2 1 10 1 +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 + -> 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) + +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 DROP TABLE t1, t2; # # Bug#52051: Aggregate functions incorrectly returns NULL from outer diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index aaec8469421..080b5236f3e 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1342,6 +1342,8 @@ SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) FROM t1 GROUP BY aa, b ORDER BY -aa, -b; 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 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; DROP TABLE t1, t2; diff --git a/sql/join_optimizer/explain_access_path.cc b/sql/join_optimizer/explain_access_path.cc index 69c1b3e78e5..e5d804cf139 100644 --- a/sql/join_optimizer/explain_access_path.cc +++ b/sql/join_optimizer/explain_access_path.cc @@ -275,17 +275,31 @@ static bool GetAccessPathsFromSelectList(JOIN *join, if (GetAccessPathsFromItem(item, "projection", children)) return true; } + // Temp_table_param::items_to_copy may contain multiple Items which refer to + // the same subquery. Collect the subqueries for deduplication and then print + // the execution plan. + std::vector subqs; // Look for any Items that were materialized into fields during execution. for (uint table_idx = join->primary_tables; table_idx < join->tables; ++table_idx) { QEP_TAB *qep_tab = &join->qep_tab[table_idx]; if (qep_tab != nullptr && qep_tab->tmp_table_param != nullptr) { for (Func_ptr &func : *qep_tab->tmp_table_param->items_to_copy) { - if (GetAccessPathsFromItem(func.func(), "projection", children)) - return true; + WalkItem(func.func(), enum_walk::POSTFIX, [&subqs](Item *item) { + if (item->type() != Item::SUBSELECT_ITEM) return false; + Item_subselect *subselect = down_cast(item); + for (auto *existed_subq : subqs) { + if (existed_subq == subselect) return false; + } + subqs.push_back(subselect); + return false; + }); } } } + for (auto *subq : subqs) { + if (GetAccessPathsFromItem(subq, "projection", children)) return true; + } return false; } -- 2.19.1.6.gb485710b