Skip to content

Commit a4aa854

Browse files
tglsfdctglsfdc
committed
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.
1 parent e3ec801 commit a4aa854

File tree

3 files changed

+59
-27
lines changed

3 files changed

+59
-27
lines changed

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 29 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -797,6 +797,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
797797
ojscope;
798798
List *leftjoinlist,
799799
*rightjoinlist;
800+
List *my_quals;
800801
SpecialJoinInfo *sjinfo;
801802
ListCell *l;
802803

@@ -895,6 +896,32 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
895896
root->nullable_baserels = bms_add_members(root->nullable_baserels,
896897
nullable_rels);
897898

899+
/*
900+
* Try to process any quals postponed by children. If they need
901+
* further postponement, add them to my output postponed_qual_list.
902+
* Quals that can be processed now must be included in my_quals, so
903+
* that they'll be handled properly in make_outerjoininfo.
904+
*/
905+
my_quals = NIL;
906+
foreach(l, child_postponed_quals)
907+
{
908+
PostponedQual *pq = (PostponedQual *) lfirst(l);
909+
910+
if (bms_is_subset(pq->relids, *qualscope))
911+
my_quals = lappend(my_quals, pq->qual);
912+
else
913+
{
914+
/*
915+
* We should not be postponing any quals past an outer join.
916+
* If this Assert fires, pull_up_subqueries() messed up.
917+
*/
918+
Assert(j->jointype == JOIN_INNER);
919+
*postponed_qual_list = lappend(*postponed_qual_list, pq);
920+
}
921+
}
922+
/* list_concat is nondestructive of its second argument */
923+
my_quals = list_concat(my_quals, (List *) j->quals);
924+
898925
/*
899926
* For an OJ, form the SpecialJoinInfo now, because we need the OJ's
900927
* 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,
910937
leftids, rightids,
911938
*inner_join_rels,
912939
j->jointype,
913-
(List *) j->quals);
940+
my_quals);
914941
if (j->jointype == JOIN_SEMI)
915942
ojscope = NULL;
916943
else
@@ -923,33 +950,8 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
923950
ojscope = NULL;
924951
}
925952

926-
/*
927-
* Try to process any quals postponed by children. If they need
928-
* further postponement, add them to my output postponed_qual_list.
929-
*/
930-
foreach(l, child_postponed_quals)
931-
{
932-
PostponedQual *pq = (PostponedQual *) lfirst(l);
933-
934-
if (bms_is_subset(pq->relids, *qualscope))
935-
distribute_qual_to_rels(root, pq->qual,
936-
false, below_outer_join, j->jointype,
937-
*qualscope,
938-
ojscope, nonnullable_rels, NULL,
939-
NULL);
940-
else
941-
{
942-
/*
943-
* We should not be postponing any quals past an outer join.
944-
* If this Assert fires, pull_up_subqueries() messed up.
945-
*/
946-
Assert(j->jointype == JOIN_INNER);
947-
*postponed_qual_list = lappend(*postponed_qual_list, pq);
948-
}
949-
}
950-
951953
/* Process the JOIN's qual clauses */
952-
foreach(l, (List *) j->quals)
954+
foreach(l, my_quals)
953955
{
954956
Node *qual = (Node *) lfirst(l);
955957

‎src/test/regress/expected/join.out

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4012,6 +4012,28 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
40124012
Output: i.f1
40134013
(34 rows)
40144014

4015+
-- check processing of postponed quals (bug #9041)
4016+
explain (verbose, costs off)
4017+
select * from
4018+
(select 1 as x) x cross join (select 2 as y) y
4019+
left join lateral (
4020+
select * from (select 3 as z) z where z.z = x.x
4021+
) zz on zz.z = y.y;
4022+
QUERY PLAN
4023+
----------------------------------------------
4024+
Nested Loop Left Join
4025+
Output: (1), (2), (3)
4026+
Join Filter: (((3) = (1)) AND ((3) = (2)))
4027+
-> Nested Loop
4028+
Output: (1), (2)
4029+
-> Result
4030+
Output: 1
4031+
-> Result
4032+
Output: 2
4033+
-> Result
4034+
Output: 3
4035+
(11 rows)
4036+
40154037
-- test some error cases where LATERAL should have been used but wasn't
40164038
select f1,g from int4_tbl a, (select f1 as g) ss;
40174039
ERROR: column "f1" does not exist

‎src/test/regress/sql/join.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1120,6 +1120,14 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
11201120
) on c.q2 = ss2.q1,
11211121
lateral (select * from int4_tbl i where ss2.y > f1) ss3;
11221122

1123+
-- check processing of postponed quals (bug #9041)
1124+
explain (verbose, costs off)
1125+
select * from
1126+
(select 1 as x) x cross join (select 2 as y) y
1127+
left join lateral (
1128+
select * from (select 3 as z) z where z.z = x.x
1129+
) zz on zz.z = y.y;
1130+
11231131
-- test some error cases where LATERAL should have been used but wasn't
11241132
select f1,g from int4_tbl a, (select f1 as g) ss;
11251133
select f1,g from int4_tbl a, (select a.f1 as g) ss;

0 commit comments

Comments
 (0)