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

Commita4aa854

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 parente3ec801 commita4aa854

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
)onc.q2=ss2.q1,
11211121
lateral (select*from int4_tbl iwheress2.y> f1) ss3;
11221122

1123+
-- check processing of postponed quals (bug #9041)
1124+
explain (verbose, costs off)
1125+
select*from
1126+
(select1as x) xcross join (select2as y) y
1127+
left join lateral (
1128+
select*from (select3as z) zwherez.z=x.x
1129+
) zzonzz.z=y.y;
1130+
11231131
-- test some error cases where LATERAL should have been used but wasn't
11241132
select f1,gfrom int4_tbl a, (select f1as g) ss;
11251133
select f1,gfrom int4_tbl a, (selecta.f1as g) ss;

0 commit comments

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