Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.2.0
-
Security Level: Public
-
Untriaged
-
1
-
No
Description
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.