Bug #111564 Contribution by Tencent: explain format=tree lost the subquery in the hash join

• Submitted: 26 Jun 2023 12:33
• Modified: 10 Aug 2023 0:58
• Reporter: Wen He (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, explain, hash join, sub-query

[26 Jun 2023 12:33] Wen He

Description:
Plan of subquery in hash join condition is missing.

How to repeat:
create table t1 (a int not null, b int not null);
create table t2 (a decimal(15,2) not null, b int not null);

explain format=tree
select * from t1, t2 where t2.a = (SELECT MIN(t2.a) FROM t2 WHERE t1.a = t2.a);

explain format=tree
select * from t1 left join t2 on t1.b = t2.b AND t2.a < (SELECT MIN(t2.a) FROM t2 WHERE t1.a = t2.a);

[26 Jun 2023 13:01] Wen He

Suggested fix:
diff --git a/sql/join_optimizer/explain_access_path.cc b/sql/join_optimizer/explain_access_path.cc
index 69c1b3e78e5..a044ef18424 100644
--- a/sql/join_optimizer/explain_access_path.cc
+++ b/sql/join_optimizer/explain_access_path.cc
@@ -1277,6 +1277,14 @@ static std::unique_ptr<Json_object> SetObjectMembers(
       error |= AddMemberToObject<Json_string>(obj, "join_algorithm", "hash");
       children->push_back({path->hash_join().outer});
       children->push_back({path->hash_join().inner, "Hash"});
+      const RelationalExpression *join_predicate =
+          path->hash_join().join_predicate->expr;
+      for (Item_eq_base *cond : join_predicate->equijoin_conditions) {
+        GetAccessPathsFromItem(cond, "condition", children);
+      }
+      for (Item *cond : join_predicate->join_conditions) {
+        GetAccessPathsFromItem(cond, "extra conditions", children);
+      }
       break;
     }
     case AccessPath::FILTER: {

[26 Jun 2023 13:16] Wen He

Resolve join conditions during explain and update main.hash_join.

Attachment: patch.txt (text/plain), 3.80 KiB.

[26 Jun 2023 13:48] MySQL Verification Team

Hi Mr. He,

Thank you for your bug report.

We managed to repeat the behaviour :

ysql: [Warning] Using a password on the command line interface can be insecure.
EXPLAIN
-> Inner hash join (t2.a = (select #2))  (cost=0.7 rows=1)\n    -> Table scan on t2  (cost=0.35 rows=1)\n    -> Hash\n        -> Table scan on t1  (cost=0.35 rows=1)\n
EXPLAIN
-> Left hash join (t2.b = t1.b), extra conditions: (t2.a < (select #2))  (cost=0.7 rows=1)\n    -> Table scan on t1  (cost=0.35 rows=1)\n    -> Hash\n        -> Table scan on t2  (cost=0.35 rows=1)\n

Thank you for your test case and even more, thank you on your patch.

Verified as reported.

[7 Jul 2023 9:20] MySQL Verification Team

Hello Wen He,

Please note that Development wants to use your contribution but we noted that you have added it as a Suggested fix/comment instead of uploading the patch via "Contribution" tab. Kindly request you to upload the patch via "Contribution" tab so that we can use it. Thank you.

regards,
Umesh

[8 Jul 2023 0:55] Wen He

Resolve join conditions during explain and update main.hash_join.

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

Contribution: patch.txt (text/plain), 3.80 KiB.

[10 Aug 2023 0:58] Jon Stephens

Documented fix as follows in the MySQL 8.2.0 changelog:

    EXPLAIN FORMAT=TREE lost the subquery in a hash join.

    Our thanks to Wen He and the Tencent team for the contribution.

Closed.

[10 Aug 2023 12:21] MySQL Verification Team

Thank you, Jon.