Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit043f6ff

Browse files
committed
Fix bogus handling of "postponed" lateral quals.
When pulling a "postponed" qual from a LATERAL subquery up into the qualsof an outer join, we must make sure that the postponed qual is includedin those seen by make_outerjoininfo(). Otherwise we might compute atoo-small min_lefthand or min_righthand for the outer join, leading to"JOIN qualification cannot refer to other relations" failures fromdistribute_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 parentc29a6dd commit043f6ff

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
@@ -4060,6 +4060,28 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
40604060
Output: i.f1
40614061
(34 rows)
40624062

4063+
-- check processing of postponed quals (bug #9041)
4064+
explain (verbose, costs off)
4065+
select * from
4066+
(select 1 as x) x cross join (select 2 as y) y
4067+
left join lateral (
4068+
select * from (select 3 as z) z where z.z = x.x
4069+
) zz on zz.z = y.y;
4070+
QUERY PLAN
4071+
----------------------------------------------
4072+
Nested Loop Left Join
4073+
Output: (1), (2), (3)
4074+
Join Filter: (((3) = (1)) AND ((3) = (2)))
4075+
-> Nested Loop
4076+
Output: (1), (2)
4077+
-> Result
4078+
Output: 1
4079+
-> Result
4080+
Output: 2
4081+
-> Result
4082+
Output: 3
4083+
(11 rows)
4084+
40634085
-- test some error cases where LATERAL should have been used but wasn't
40644086
select f1,g from int4_tbl a, (select f1 as g) ss;
40654087
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
@@ -1134,6 +1134,14 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
11341134
)onc.q2=ss2.q1,
11351135
lateral (select*from int4_tbl iwheress2.y> f1) ss3;
11361136

1137+
-- check processing of postponed quals (bug #9041)
1138+
explain (verbose, costs off)
1139+
select*from
1140+
(select1as x) xcross join (select2as y) y
1141+
left join lateral (
1142+
select*from (select3as z) zwherez.z=x.x
1143+
) zzonzz.z=y.y;
1144+
11371145
-- test some error cases where LATERAL should have been used but wasn't
11381146
select f1,gfrom int4_tbl a, (select f1as g) ss;
11391147
select f1,gfrom int4_tbl a, (selecta.f1as g) ss;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp