[BP to 7.2.0 MB-53601] - Hash join should not be allowed under the inner of a nested-loop join

Hash join should not be allowed in the inner of nested-loop join, since nested-loop join requires the "reopen" logic for operators on the inner side, and hash join does not have that logic implemented.

This can only happen when the right-hand side of a join is a subquery, since the query plan for a subquery in the FROM clause is incorporated into the main query plan, it is possible to have the subquery on the inner side of a nested-loop join, and a hash join used inside the subquery plan.

UPSERT INTO default VALUES("f001", { "eid": "100", "type": "airport" });

UPSERT INTO default VALUES("p001", { "eid": "100", "type": "hotel", "status":"open" });

UPSERT INTO default (KEY k, VALUE v) SELECT  "f"|| TO_STR(d) AS k, {"type": "airport"} AS v  FROM ARRAY_RANGE(0,2) AS d;

CREATE INDEX `ix3` ON `default`(`type`);

EXPLAIN

SELECT d1.eid , d1.pa

FROM default d1

    JOIN ( SELECT d2.eid FROM default d2

           JOIN ( SELECT d3.eid, d3.pa FROM default AS d3 WHERE d3.`type` = 'airport' ) AS d10 ON d2.eid = d10.eid

    WHERE d2.type = "hotel" AND d2.status = "open") AS final USE NL ON d1.eid = final.eid

WHERE d1.`type` = 'airport' ;

when the outermost join (between d1 and final) is forced to use nested-loop join, the join inside the subquery between d2 and d10 should not use hash join.