From 62163bbbb99eda8f31bd90d80cbc155c039110e0 Mon Sep 17 00:00:00 2001 From: tianfengli Date: Thu, 14 Sep 2023 14:55:09 +0800 Subject: [PATCH] Repeated subquery plans were printed in format=tree. --- mysql-test/t/explain_tree.test | 15 +++++++++++++++ sql/join_optimizer/explain_access_path.cc | 5 +++++ 2 files changed, 20 insertions(+) diff --git a/mysql-test/t/explain_tree.test b/mysql-test/t/explain_tree.test index cf65dfb2494..b0f8bb72a2d 100644 --- a/mysql-test/t/explain_tree.test +++ b/mysql-test/t/explain_tree.test @@ -698,3 +698,18 @@ EXPLAIN ANALYZE WITH cte AS (SELECT SUM(i) k1 FROM t1 GROUP BY j) SELECT * FROM cte LIMIT 2; DROP TABLE t1; + +--echo # +--echo # Redundant subquery plan in tree format. +--echo # +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); +CREATE TABLE t3 (e INT NOT NULL); + +EXPLAIN FORMAT=TREE + SELECT * FROM t1 AS ta WHERE ta.a IN + (SELECT c FROM t2 AS tb WHERE + (SELECT MIN(e) FROM t3 as tc WHERE tb.d=tc.e) < + SOME(SELECT e FROM t3 as tc WHERE ta.b=tc.e)); + +DROP TABLE t1, t2, t3; diff --git a/sql/join_optimizer/explain_access_path.cc b/sql/join_optimizer/explain_access_path.cc index 69c1b3e78e5..d13f7a29d15 100644 --- a/sql/join_optimizer/explain_access_path.cc +++ b/sql/join_optimizer/explain_access_path.cc @@ -244,6 +244,11 @@ static bool GetAccessPathsFromItem(Item *item_arg, const char *source_text, } else { path = subselect->unit->item->root_access_path(); } + auto duplicate = std::find_if(children->begin(), children->end(), + [path](ExplainData::Child &child) { return path == child.path;}); + // Don't add duplicate subselect. + if (duplicate != children->end()) + return false; Json_object *child_obj = new (std::nothrow) Json_object(); if (child_obj == nullptr) return true; // Populate the subquery-specific json fields. -- 2.19.1