Re: LATERAL quals revisited - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: LATERAL quals revisited |
| Date | |
| Msg-id | 13247.1376924781@sss.pgh.pa.us Whole thread Raw |
| In response to | LATERAL quals revisited (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
Some time ago, I wrote: > I've been studying the bug reported at > http://www.postgresql.org/message-id/20130617235236.GA1636@jeremyevans.local > ... > After some contemplation, I think that the most practical way to fix > this is for deconstruct_recurse and distribute_qual_to_rels to > effectively move such a qual to the place where it logically belongs; > that is, rather than processing it when we look at the lower WHERE > clause, set it aside for a moment and then add it back when looking at > the ON clause of the appropriate outer join. This should be reasonably > easy to do by keeping a list of "postponed lateral clauses" while we're > scanning the join tree. Here's a draft patch for this. The comments need a bit more work probably, but barring objection I want to push this in before this afternoon's 9.3rc1 wrap. regards, tom lane diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 98f601c..e055088 100644 *** a/src/backend/optimizer/plan/initsplan.c --- b/src/backend/optimizer/plan/initsplan.c *************** int from_collapse_limit; *** 36,47 **** int join_collapse_limit; static void extract_lateral_references(PlannerInfo *root, RelOptInfo *brel, Index rtindex); static void add_lateral_info(PlannerInfo *root, Relids lhs, Relids rhs); static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, ! Relids *qualscope, Relids *inner_join_rels); static SpecialJoinInfo *make_outerjoininfo(PlannerInfo *root, Relids left_rels, Relids right_rels, Relids inner_join_rels, --- 36,56 ---- int join_collapse_limit; + /* Elements of the postponed_qual_list used during deconstruct_recurse */ + typedef struct PostponedQual + { + Node *qual; /* a qual clause waiting to be processed */ + Relids relids; /* the set of baserels it references */ + } PostponedQual; + + static void extract_lateral_references(PlannerInfo *root, RelOptInfo *brel, Index rtindex); static void add_lateral_info(PlannerInfo *root, Relids lhs, Relids rhs); static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, ! Relids *qualscope, Relids *inner_join_rels, ! List **postponed_qual_list); static SpecialJoinInfo *make_outerjoininfo(PlannerInfo *root, Relids left_rels, Relids right_rels, Relids inner_join_rels, *************** static void distribute_qual_to_rels(Plan *** 53,59 **** Relids qualscope, Relids ojscope, Relids outerjoin_nonnullable, ! Relids deduced_nullable_relids); static bool check_outerjoin_delay(PlannerInfo *root, Relids *relids_p, Relids *nullable_relids_p, bool is_pushed_down); static bool check_equivalence_delay(PlannerInfo *root, --- 62,69 ---- Relids qualscope, Relids ojscope, Relids outerjoin_nonnullable, ! Relids deduced_nullable_relids, ! List **postponed_qual_list); static bool check_outerjoin_delay(PlannerInfo *root, Relids *relids_p, Relids *nullable_relids_p, bool is_pushed_down); static bool check_equivalence_delay(PlannerInfo *root, *************** add_lateral_info(PlannerInfo *root, Reli *** 630,644 **** List * deconstruct_jointree(PlannerInfo *root) { Relids qualscope; Relids inner_join_rels; /* Start recursion at top of jointree */ Assert(root->parse->jointree != NULL && IsA(root->parse->jointree, FromExpr)); ! return deconstruct_recurse(root, (Node *) root->parse->jointree, false, ! &qualscope, &inner_join_rels); } /* --- 640,662 ---- List * deconstruct_jointree(PlannerInfo *root) { + List *result; Relids qualscope; Relids inner_join_rels; + List *postponed_qual_list = NIL; /* Start recursion at top of jointree */ Assert(root->parse->jointree != NULL && IsA(root->parse->jointree, FromExpr)); ! result = deconstruct_recurse(root, (Node *) root->parse->jointree, false, ! &qualscope, &inner_join_rels, ! &postponed_qual_list); ! ! /* Shouldn't be any leftover quals */ ! Assert(postponed_qual_list == NIL); ! ! return result; } /* *************** deconstruct_jointree(PlannerInfo *root) *** 656,668 **** * *inner_join_rels gets the set of base Relids syntactically included in * inner joins appearing at or below this jointree node (do not modify * or free this, either) * Return value is the appropriate joinlist for this jointree node * * In addition, entries will be added to root->join_info_list for outer joins. */ static List * deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, ! Relids *qualscope, Relids *inner_join_rels) { List *joinlist; --- 674,689 ---- * *inner_join_rels gets the set of base Relids syntactically included in * inner joins appearing at or below this jointree node (do not modify * or free this, either) + * *postponed_qual_list: list of PostponedQual structs, which we can add + * quals to if they turn out to belong to a higher join level * Return value is the appropriate joinlist for this jointree node * * In addition, entries will be added to root->join_info_list for outer joins. */ static List * deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, ! Relids *qualscope, Relids *inner_join_rels, ! List **postponed_qual_list) { List *joinlist; *************** deconstruct_recurse(PlannerInfo *root, N *** 685,690 **** --- 706,712 ---- else if (IsA(jtnode, FromExpr)) { FromExpr *f = (FromExpr *) jtnode; + List *child_postponed_quals = NIL; int remaining; ListCell *l; *************** deconstruct_recurse(PlannerInfo *root, N *** 707,713 **** sub_joinlist = deconstruct_recurse(root, lfirst(l), below_outer_join, &sub_qualscope, ! inner_join_rels); *qualscope = bms_add_members(*qualscope, sub_qualscope); sub_members = list_length(sub_joinlist); remaining--; --- 729,736 ---- sub_joinlist = deconstruct_recurse(root, lfirst(l), below_outer_join, &sub_qualscope, ! inner_join_rels, ! &child_postponed_quals); *qualscope = bms_add_members(*qualscope, sub_qualscope); sub_members = list_length(sub_joinlist); remaining--; *************** deconstruct_recurse(PlannerInfo *root, N *** 729,734 **** --- 752,774 ---- *inner_join_rels = *qualscope; /* + * Try to process any quals postponed by children. If they need + * further postponement, add them to my output postponed_qual_list. + */ + foreach(l, child_postponed_quals) + { + PostponedQual *pq = (PostponedQual *) lfirst(l); + + if (bms_is_subset(pq->relids, *qualscope)) + distribute_qual_to_rels(root, pq->qual, + false, below_outer_join, JOIN_INNER, + *qualscope, NULL, NULL, NULL, + NULL); + else + *postponed_qual_list = lappend(*postponed_qual_list, pq); + } + + /* * Now process the top-level quals. */ foreach(l, (List *) f->quals) *************** deconstruct_recurse(PlannerInfo *root, N *** 737,748 **** distribute_qual_to_rels(root, qual, false, below_outer_join, JOIN_INNER, ! *qualscope, NULL, NULL, NULL); } } else if (IsA(jtnode, JoinExpr)) { JoinExpr *j = (JoinExpr *) jtnode; Relids leftids, rightids, left_inners, --- 777,790 ---- distribute_qual_to_rels(root, qual, false, below_outer_join, JOIN_INNER, ! *qualscope, NULL, NULL, NULL, ! postponed_qual_list); } } else if (IsA(jtnode, JoinExpr)) { JoinExpr *j = (JoinExpr *) jtnode; + List *child_postponed_quals = NIL; Relids leftids, rightids, left_inners, *************** deconstruct_recurse(PlannerInfo *root, N *** 771,780 **** case JOIN_INNER: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners); rightjoinlist = deconstruct_recurse(root, j->rarg, below_outer_join, ! &rightids, &right_inners); *qualscope = bms_union(leftids, rightids); *inner_join_rels = *qualscope; /* Inner join adds no restrictions for quals */ --- 813,824 ---- case JOIN_INNER: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners, ! &child_postponed_quals); rightjoinlist = deconstruct_recurse(root, j->rarg, below_outer_join, ! &rightids, &right_inners, ! &child_postponed_quals); *qualscope = bms_union(leftids, rightids); *inner_join_rels = *qualscope; /* Inner join adds no restrictions for quals */ *************** deconstruct_recurse(PlannerInfo *root, N *** 784,793 **** case JOIN_ANTI: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners); rightjoinlist = deconstruct_recurse(root, j->rarg, true, ! &rightids, &right_inners); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); nonnullable_rels = leftids; --- 828,839 ---- case JOIN_ANTI: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners, ! &child_postponed_quals); rightjoinlist = deconstruct_recurse(root, j->rarg, true, ! &rightids, &right_inners, ! &child_postponed_quals); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); nonnullable_rels = leftids; *************** deconstruct_recurse(PlannerInfo *root, N *** 795,804 **** case JOIN_SEMI: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners); rightjoinlist = deconstruct_recurse(root, j->rarg, below_outer_join, ! &rightids, &right_inners); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); /* Semi join adds no restrictions for quals */ --- 841,852 ---- case JOIN_SEMI: leftjoinlist = deconstruct_recurse(root, j->larg, below_outer_join, ! &leftids, &left_inners, ! &child_postponed_quals); rightjoinlist = deconstruct_recurse(root, j->rarg, below_outer_join, ! &rightids, &right_inners, ! &child_postponed_quals); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); /* Semi join adds no restrictions for quals */ *************** deconstruct_recurse(PlannerInfo *root, N *** 807,816 **** case JOIN_FULL: leftjoinlist = deconstruct_recurse(root, j->larg, true, ! &leftids, &left_inners); rightjoinlist = deconstruct_recurse(root, j->rarg, true, ! &rightids, &right_inners); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); /* each side is both outer and inner */ --- 855,866 ---- case JOIN_FULL: leftjoinlist = deconstruct_recurse(root, j->larg, true, ! &leftids, &left_inners, ! &child_postponed_quals); rightjoinlist = deconstruct_recurse(root, j->rarg, true, ! &rightids, &right_inners, ! &child_postponed_quals); *qualscope = bms_union(leftids, rightids); *inner_join_rels = bms_union(left_inners, right_inners); /* each side is both outer and inner */ *************** deconstruct_recurse(PlannerInfo *root, N *** 853,859 **** ojscope = NULL; } ! /* Process the qual clauses */ foreach(l, (List *) j->quals) { Node *qual = (Node *) lfirst(l); --- 903,934 ---- ojscope = NULL; } ! /* ! * Try to process any quals postponed by children. If they need ! * further postponement, add them to my output postponed_qual_list. ! */ ! foreach(l, child_postponed_quals) ! { ! PostponedQual *pq = (PostponedQual *) lfirst(l); ! ! if (bms_is_subset(pq->relids, *qualscope)) ! distribute_qual_to_rels(root, pq->qual, ! false, below_outer_join, j->jointype, ! *qualscope, ! ojscope, nonnullable_rels, NULL, ! NULL); ! else ! { ! /* ! * We should not be postponing any quals past an outer join. ! * If this Assert fires, pull_up_subqueries() messed up. ! */ ! Assert(j->jointype == JOIN_INNER); ! *postponed_qual_list = lappend(*postponed_qual_list, pq); ! } ! } ! ! /* Process the JOIN's qual clauses */ foreach(l, (List *) j->quals) { Node *qual = (Node *) lfirst(l); *************** deconstruct_recurse(PlannerInfo *root, N *** 861,867 **** distribute_qual_to_rels(root, qual, false, below_outer_join, j->jointype, *qualscope, ! ojscope, nonnullable_rels, NULL); } /* Now we can add the SpecialJoinInfo to join_info_list */ --- 936,943 ---- distribute_qual_to_rels(root, qual, false, below_outer_join, j->jointype, *qualscope, ! ojscope, nonnullable_rels, NULL, ! postponed_qual_list); } /* Now we can add the SpecialJoinInfo to join_info_list */ *************** make_outerjoininfo(PlannerInfo *root, *** 1154,1160 **** * the appropriate list for each rel. Alternatively, if the clause uses a * mergejoinable operator and is not delayed by outer-join rules, enter * the left- and right-side expressions into the query's list of ! * EquivalenceClasses. * * 'clause': the qual clause to be distributed * 'is_deduced': TRUE if the qual came from implied-equality deduction --- 1230,1237 ---- * the appropriate list for each rel. Alternatively, if the clause uses a * mergejoinable operator and is not delayed by outer-join rules, enter * the left- and right-side expressions into the query's list of ! * EquivalenceClasses. Alternatively, if the clause needs to be treated ! * as belonging to a higher join level, just add it to postponed_qual_list. * * 'clause': the qual clause to be distributed * 'is_deduced': TRUE if the qual came from implied-equality deduction *************** make_outerjoininfo(PlannerInfo *root, *** 1170,1175 **** --- 1247,1254 ---- * equal qualscope) * 'deduced_nullable_relids': if is_deduced is TRUE, the nullable relids to * impute to the clause; otherwise NULL + * 'postponed_qual_list': list of PostponedQual structs, which we can add + * this qual to if it turns out to belong to a higher join level * * 'qualscope' identifies what level of JOIN the qual came from syntactically. * 'ojscope' is needed if we decide to force the qual up to the outer-join *************** distribute_qual_to_rels(PlannerInfo *roo *** 1190,1196 **** Relids qualscope, Relids ojscope, Relids outerjoin_nonnullable, ! Relids deduced_nullable_relids) { Relids relids; bool is_pushed_down; --- 1269,1276 ---- Relids qualscope, Relids ojscope, Relids outerjoin_nonnullable, ! Relids deduced_nullable_relids, ! List **postponed_qual_list) { Relids relids; bool is_pushed_down; *************** distribute_qual_to_rels(PlannerInfo *roo *** 1207,1226 **** relids = pull_varnos(clause); /* ! * Normally relids is a subset of qualscope, and we like to check that ! * here as a crosscheck on the parser and rewriter. That need not be the ! * case when there are LATERAL RTEs, however: the clause could contain ! * references to rels outside its syntactic scope as a consequence of ! * pull-up of such references from a LATERAL subquery below it. So, only ! * check if the query contains no LATERAL RTEs. ! * ! * However, if it's an outer-join clause, we always insist that relids be ! * a subset of ojscope. This is safe because is_simple_subquery() ! * disallows pullup of LATERAL subqueries that could cause the restriction ! * to be violated. */ - if (!root->hasLateralRTEs && !bms_is_subset(relids, qualscope)) - elog(ERROR, "JOIN qualification cannot refer to other relations"); if (ojscope && !bms_is_subset(relids, ojscope)) elog(ERROR, "JOIN qualification cannot refer to other relations"); --- 1287,1322 ---- relids = pull_varnos(clause); /* ! * In ordinary SQL, a WHERE or JOIN/ON clause can't reference any rels ! * that aren't within its syntactic scope; however, if we pulled up a ! * LATERAL subquery then we might find such references in quals that have ! * been pulled up. We need to treat such quals as belonging to the join ! * level that includes every rel they reference. Although we could make ! * pull_up_subqueries() place such quals correctly to begin with, it's ! * easier to handle it here. When we find a clause that contains Vars ! * outside its syntactic scope, we add it to the postponed_clauses list, ! * and process it once we've recursed back up to the appropriate join ! * level. ! */ ! if (!bms_is_subset(relids, qualscope)) ! { ! PostponedQual *pq = (PostponedQual *) palloc(sizeof(PostponedQual)); ! ! Assert(root->hasLateralRTEs); /* shouldn't happen otherwise */ ! Assert(jointype == JOIN_INNER); /* mustn't postpone past outer join */ ! Assert(!is_deduced); /* shouldn't be deduced, either */ ! pq->qual = clause; ! pq->relids = relids; ! *postponed_qual_list = lappend(*postponed_qual_list, pq); ! return; ! } ! ! /* ! * In any case, if it's an outer-join clause, we insist that relids be a ! * subset of ojscope. (It's pull_up_subqueries()'s responsibility to not ! * pull up a LATERAL subquery if that would cause this to fail; the ! * semantics that would result from such a situation are unclear.) */ if (ojscope && !bms_is_subset(relids, ojscope)) elog(ERROR, "JOIN qualification cannot refer to other relations"); *************** process_implied_equality(PlannerInfo *ro *** 1874,1880 **** */ distribute_qual_to_rels(root, (Node *) clause, true, below_outer_join, JOIN_INNER, ! qualscope, NULL, NULL, nullable_relids); } /* --- 1970,1977 ---- */ distribute_qual_to_rels(root, (Node *) clause, true, below_outer_join, JOIN_INNER, ! qualscope, NULL, NULL, nullable_relids, ! NULL); } /* diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 875baef..bb6d983 100644 *** a/src/backend/optimizer/prep/prepjointree.c --- b/src/backend/optimizer/prep/prepjointree.c *************** static bool is_simple_union_all(Query *s *** 84,89 **** --- 84,91 ---- static bool is_simple_union_all_recurse(Node *setOp, Query *setOpQuery, List *colTypes); static bool is_safe_append_member(Query *subquery); + static bool jointree_contains_lateral_outer_refs(Node *jtnode, bool restricted, + Relids safe_upper_varnos); static void replace_vars_in_jointree(Node *jtnode, pullup_replace_vars_context *context, JoinExpr *lowest_nulling_outer_join); *************** is_simple_subquery(Query *subquery, Rang *** 1303,1322 **** return false; /* ! * If the subquery is LATERAL, and we're below any outer join, and the ! * subquery contains lateral references to rels outside the outer join, ! * don't pull up. Doing so would risk creating outer-join quals that ! * contain references to rels outside the outer join, which is a semantic ! * mess that doesn't seem worth addressing at the moment. */ ! if (rte->lateral && lowest_outer_join != NULL) { ! Relids lvarnos = pull_varnos_of_level((Node *) subquery, 1); ! Relids jvarnos = get_relids_in_jointree((Node *) lowest_outer_join, ! true); ! if (!bms_is_subset(lvarnos, jvarnos)) return false; } /* --- 1305,1351 ---- return false; /* ! * If the subquery is LATERAL, check to see if its WHERE or JOIN/ON quals ! * contain any lateral references to rels outside an upper outer join ! * (including the case where the outer join is within the subquery ! * itself). If so, don't pull up. Doing so would result in a situation ! * where we need to postpone quals from below an outer join to above it, ! * which is probably completely wrong and in any case is a complication ! * that doesn't seem worth addressing at the moment. */ ! if (rte->lateral) { ! bool restricted; ! Relids safe_upper_varnos; ! if (lowest_outer_join != NULL) ! { ! restricted = true; ! safe_upper_varnos = get_relids_in_jointree((Node *) lowest_outer_join, ! true); ! } ! else ! { ! restricted = false; ! safe_upper_varnos = NULL; /* doesn't matter */ ! } ! ! if (jointree_contains_lateral_outer_refs((Node *) subquery->jointree, ! restricted, safe_upper_varnos)) return false; + + /* + * If there's an upper outer join, also disallow any targetlist + * references outside it, since these might get pulled into quals + * above this subquery. + */ + if (lowest_outer_join != NULL) + { + Relids lvarnos = pull_varnos_of_level((Node *) subquery->targetList, 1); + + if (!bms_is_subset(lvarnos, safe_upper_varnos)) + return false; + } } /* *************** is_simple_subquery(Query *subquery, Rang *** 1344,1355 **** * correctly generate a Result plan for a jointree that's totally empty, * but we can't cope with an empty FromExpr appearing lower down in a * jointree: we identify join rels via baserelid sets, so we couldn't ! * distinguish a join containing such a FromExpr from one without it. ! * This would for example break the PlaceHolderVar mechanism, since we'd ! * have no way to identify where to evaluate a PHV coming out of the ! * subquery. Not worth working hard on this, just to collapse ! * SubqueryScan/Result into Result; especially since the SubqueryScan can ! * often be optimized away by setrefs.c anyway. */ if (subquery->jointree->fromlist == NIL) return false; --- 1373,1384 ---- * correctly generate a Result plan for a jointree that's totally empty, * but we can't cope with an empty FromExpr appearing lower down in a * jointree: we identify join rels via baserelid sets, so we couldn't ! * distinguish a join containing such a FromExpr from one without it. This ! * would for example break the PlaceHolderVar mechanism, since we'd have ! * no way to identify where to evaluate a PHV coming out of the subquery. ! * Not worth working hard on this, just to collapse SubqueryScan/Result ! * into Result; especially since the SubqueryScan can often be optimized ! * away by setrefs.c anyway. */ if (subquery->jointree->fromlist == NIL) return false; *************** is_safe_append_member(Query *subquery) *** 1467,1472 **** --- 1496,1575 ---- } /* + * jointree_contains_lateral_outer_refs + * Check for disallowed lateral references in a jointree's quals + * + * If restricted is false, all level-1 Vars are allowed (but we still must + * search the jointree, since it might contain outer joins below which there + * will be restrictions). If restricted is true, return TRUE when any qual + * in the jointree contains level-1 Vars coming from outside the rels listed + * in safe_upper_varnos. + */ + static bool + jointree_contains_lateral_outer_refs(Node *jtnode, bool restricted, + Relids safe_upper_varnos) + { + if (jtnode == NULL) + return false; + if (IsA(jtnode, RangeTblRef)) + return false; + else if (IsA(jtnode, FromExpr)) + { + FromExpr *f = (FromExpr *) jtnode; + ListCell *l; + + /* First, recurse to check child joins */ + foreach(l, f->fromlist) + { + if (jointree_contains_lateral_outer_refs(lfirst(l), + restricted, + safe_upper_varnos)) + return true; + } + + /* Then check the top-level quals */ + if (restricted && + !bms_is_subset(pull_varnos_of_level(f->quals, 1), + safe_upper_varnos)) + return true; + } + else if (IsA(jtnode, JoinExpr)) + { + JoinExpr *j = (JoinExpr *) jtnode; + + /* + * If this is an outer join, we mustn't allow any upper lateral + * references in or below it. + */ + if (j->jointype != JOIN_INNER) + { + restricted = true; + safe_upper_varnos = NULL; + } + + /* Check the child joins */ + if (jointree_contains_lateral_outer_refs(j->larg, + restricted, + safe_upper_varnos)) + return true; + if (jointree_contains_lateral_outer_refs(j->rarg, + restricted, + safe_upper_varnos)) + return true; + + /* Check the JOIN's qual clauses */ + if (restricted && + !bms_is_subset(pull_varnos_of_level(j->quals, 1), + safe_upper_varnos)) + return true; + } + else + elog(ERROR, "unrecognized node type: %d", + (int) nodeTag(jtnode)); + return false; + } + + /* * Helper routine for pull_up_subqueries: do pullup_replace_vars on every * expression in the jointree, without changing the jointree structure itself. * Ugly, but there's no other way... diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index fc3e168..98aacd3 100644 *** a/src/test/regress/expected/join.out --- b/src/test/regress/expected/join.out *************** explain (costs off) *** 3161,3167 **** Nested Loop Left Join -> Seq Scan on int4_tbl x -> Index Scan using tenk1_unique1 on tenk1 ! Index Cond: (unique1 = x.f1) (4 rows) -- check scoping of lateral versus parent references --- 3161,3167 ---- Nested Loop Left Join -> Seq Scan on int4_tbl x -> Index Scan using tenk1_unique1 on tenk1 ! Index Cond: (x.f1 = unique1) (4 rows) -- check scoping of lateral versus parent references *************** select * from int4_tbl i left join *** 3648,3659 **** lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; QUERY PLAN ------------------------------------------- ! Nested Loop Left Join Output: i.f1, j.f1 ! Filter: (i.f1 = j.f1) -> Seq Scan on public.int4_tbl i Output: i.f1 ! -> Materialize Output: j.f1 -> Seq Scan on public.int2_tbl j Output: j.f1 --- 3648,3659 ---- lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; QUERY PLAN ------------------------------------------- ! Hash Left Join Output: i.f1, j.f1 ! Hash Cond: (i.f1 = j.f1) -> Seq Scan on public.int4_tbl i Output: i.f1 ! -> Hash Output: j.f1 -> Seq Scan on public.int2_tbl j Output: j.f1 *************** select * from int4_tbl i left join *** 3661,3670 **** select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; ! f1 | f1 ! ----+---- ! 0 | 0 ! (1 row) explain (verbose, costs off) select * from int4_tbl i left join --- 3661,3674 ---- select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; ! f1 | f1 ! -------------+---- ! 0 | 0 ! 123456 | ! -123456 | ! 2147483647 | ! -2147483647 | ! (5 rows) explain (verbose, costs off) select * from int4_tbl i left join *************** select * from int4_tbl i left join *** 3691,3696 **** --- 3695,3723 ---- -2147483647 | (5 rows) + explain (verbose, costs off) + select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; + QUERY PLAN + ------------------------------------------------- + Nested Loop + Output: a.f1, b.f1, c.q1, c.q2 + -> Seq Scan on public.int4_tbl a + Output: a.f1 + -> Hash Left Join + Output: b.f1, c.q1, c.q2 + Hash Cond: (b.f1 = c.q1) + -> Seq Scan on public.int4_tbl b + Output: b.f1 + -> Hash + Output: c.q1, c.q2 + -> Seq Scan on public.int8_tbl c + Output: c.q1, c.q2 + Filter: (a.f1 = c.q2) + (14 rows) + -- lateral reference in a PlaceHolderVar evaluated at join level explain (verbose, costs off) select * from diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 36853dd..c0ed8b0 100644 *** a/src/test/regress/sql/join.sql --- b/src/test/regress/sql/join.sql *************** select * from int4_tbl i left join *** 1022,1027 **** --- 1022,1032 ---- lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; + explain (verbose, costs off) + select * from int4_tbl a, + lateral ( + select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) + ) ss; -- lateral reference in a PlaceHolderVar evaluated at join level explain (verbose, costs off)
pgsql-hackers by date: