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

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);

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: {

Resolve join conditions during explain and update main.hash_join.

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

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.

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

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.

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.

Thank you, Jon.