From 043f6ff05d0a5140dfe25faf277ec9f1d7169005 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 30 Jan 2014 14:51:16 -0500 Subject: [PATCH] Fix bogus handling of "postponed" lateral quals. When pulling a "postponed" qual from a LATERAL subquery up into the quals of an outer join, we must make sure that the postponed qual is included in those seen by make_outerjoininfo(). Otherwise we might compute a too-small min_lefthand or min_righthand for the outer join, leading to "JOIN qualification cannot refer to other relations" failures from distribute_qual_to_rels. Subtler errors in the created plan seem possible, too, if the extra qual would only affect join ordering constraints. Per bug #9041 from David Leverton. Back-patch to 9.3. --- src/backend/optimizer/plan/initsplan.c | 56 +++++++++++++------------- src/test/regress/expected/join.out | 22 ++++++++++ src/test/regress/sql/join.sql | 8 ++++ 3 files changed, 59 insertions(+), 27 deletions(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index ba81b7f6ae..b57bfd2176 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -797,6 +797,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, ojscope; List *leftjoinlist, *rightjoinlist; + List *my_quals; SpecialJoinInfo *sjinfo; ListCell *l; @@ -895,6 +896,32 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, root->nullable_baserels = bms_add_members(root->nullable_baserels, nullable_rels); + /* + * Try to process any quals postponed by children. If they need + * further postponement, add them to my output postponed_qual_list. + * Quals that can be processed now must be included in my_quals, so + * that they'll be handled properly in make_outerjoininfo. + */ + my_quals = NIL; + foreach(l, child_postponed_quals) + { + PostponedQual *pq = (PostponedQual *) lfirst(l); + + if (bms_is_subset(pq->relids, *qualscope)) + my_quals = lappend(my_quals, pq->qual); + 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); + } + } + /* list_concat is nondestructive of its second argument */ + my_quals = list_concat(my_quals, (List *) j->quals); + /* * For an OJ, form the SpecialJoinInfo now, because we need the OJ's * semantic scope (ojscope) to pass to distribute_qual_to_rels. But @@ -910,7 +937,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, leftids, rightids, *inner_join_rels, j->jointype, - (List *) j->quals); + my_quals); if (j->jointype == JOIN_SEMI) ojscope = NULL; else @@ -923,33 +950,8 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join, 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) + foreach(l, my_quals) { Node *qual = (Node *) lfirst(l); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 74bc8ead26..ec64bbe7b9 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4060,6 +4060,28 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from Output: i.f1 (34 rows) +-- check processing of postponed quals (bug #9041) +explain (verbose, costs off) +select * from + (select 1 as x) x cross join (select 2 as y) y + left join lateral ( + select * from (select 3 as z) z where z.z = x.x + ) zz on zz.z = y.y; + QUERY PLAN +---------------------------------------------- + Nested Loop Left Join + Output: (1), (2), (3) + Join Filter: (((3) = (1)) AND ((3) = (2))) + -> Nested Loop + Output: (1), (2) + -> Result + Output: 1 + -> Result + Output: 2 + -> Result + Output: 3 +(11 rows) + -- test some error cases where LATERAL should have been used but wasn't select f1,g from int4_tbl a, (select f1 as g) ss; ERROR: column "f1" does not exist diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 409e0b13d6..f45aa145ad 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1134,6 +1134,14 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from ) on c.q2 = ss2.q1, lateral (select * from int4_tbl i where ss2.y > f1) ss3; +-- check processing of postponed quals (bug #9041) +explain (verbose, costs off) +select * from + (select 1 as x) x cross join (select 2 as y) y + left join lateral ( + select * from (select 3 as z) z where z.z = x.x + ) zz on zz.z = y.y; + -- test some error cases where LATERAL should have been used but wasn't select f1,g from int4_tbl a, (select f1 as g) ss; select f1,g from int4_tbl a, (select a.f1 as g) ss; -- 2.40.0