Some time ago, I wrote:
> I've been studying the bug reported at
> http://www.postgresql.org/message-id/[email protected]
> ...
> 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)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
