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

Commitc64de21

Browse files
committed
Fix qual-clause-misplacement issues with pulled-up LATERAL subqueries.
In an example such asSELECT * FROM i LEFT JOIN LATERAL (SELECT * FROM j WHERE i.n = j.n) j ON true;it is safe to pull up the LATERAL subquery into its parent, but we mustthen treat the "i.n = j.n" clause as a qual clause of the LEFT JOIN. Theprevious coding in deconstruct_recurse mistakenly labeled the clause as"is_pushed_down", resulting in wrong semantics if the clause were appliedat the join node, as per an example submitted awhile ago by Jeremy Evans.To fix, postpone processing of such clauses until we return back up tothe appropriate recursion depth in deconstruct_recurse.In addition, tighten the is-safe-to-pull-up checks in is_simple_subquery;we previously missed the possibility that the LATERAL subquery might itselfcontain an outer join that makes lateral references in lower quals unsafe.A regression test case equivalent to Jeremy's example was already in mycommit of yesterday, but was giving the wrong results because of thisbug. This patch fixes the expected output for that, and also adds atest case for the second problem.
1 parent78e1220 commitc64de21

File tree

5 files changed

+339
-62
lines changed

5 files changed

+339
-62
lines changed

‎src/backend/optimizer/README

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -803,11 +803,10 @@ still expected to enforce any join clauses that can be pushed down to it,
803803
so that all paths of the same parameterization have the same rowcount.
804804

805805
We also allow LATERAL subqueries to be flattened (pulled up into the parent
806-
query) by the optimizer, but only when they don't contain any lateral
807-
references to relations outside the lowest outer join that can null the
808-
LATERAL subquery. This restriction prevents lateral references from being
809-
introduced into outer-join qualifications, which would create semantic
810-
confusion. Note that even with this restriction, pullup of a LATERAL
806+
query) by the optimizer, but only when this does not introduce lateral
807+
references into JOIN/ON quals that would refer to relations outside the
808+
lowest outer join at/above that qual. The semantics of such a qual would
809+
be unclear. Note that even with this restriction, pullup of a LATERAL
811810
subquery can result in creating PlaceHolderVars that contain lateral
812811
references to relations outside their syntactic scope. We still evaluate
813812
such PHVs at their syntactic location or lower, but the presence of such a

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

Lines changed: 128 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -36,12 +36,21 @@ intfrom_collapse_limit;
3636
intjoin_collapse_limit;
3737

3838

39+
/* Elements of the postponed_qual_list used during deconstruct_recurse */
40+
typedefstructPostponedQual
41+
{
42+
Node*qual;/* a qual clause waiting to be processed */
43+
Relidsrelids;/* the set of baserels it references */
44+
}PostponedQual;
45+
46+
3947
staticvoidextract_lateral_references(PlannerInfo*root,RelOptInfo*brel,
4048
Indexrtindex);
4149
staticvoidadd_lateral_info(PlannerInfo*root,Relidslhs,Relidsrhs);
4250
staticList*deconstruct_recurse(PlannerInfo*root,Node*jtnode,
4351
boolbelow_outer_join,
44-
Relids*qualscope,Relids*inner_join_rels);
52+
Relids*qualscope,Relids*inner_join_rels,
53+
List**postponed_qual_list);
4554
staticSpecialJoinInfo*make_outerjoininfo(PlannerInfo*root,
4655
Relidsleft_rels,Relidsright_rels,
4756
Relidsinner_join_rels,
@@ -53,7 +62,8 @@ static void distribute_qual_to_rels(PlannerInfo *root, Node *clause,
5362
Relidsqualscope,
5463
Relidsojscope,
5564
Relidsouterjoin_nonnullable,
56-
Relidsdeduced_nullable_relids);
65+
Relidsdeduced_nullable_relids,
66+
List**postponed_qual_list);
5767
staticboolcheck_outerjoin_delay(PlannerInfo*root,Relids*relids_p,
5868
Relids*nullable_relids_p,boolis_pushed_down);
5969
staticboolcheck_equivalence_delay(PlannerInfo*root,
@@ -630,15 +640,23 @@ add_lateral_info(PlannerInfo *root, Relids lhs, Relids rhs)
630640
List*
631641
deconstruct_jointree(PlannerInfo*root)
632642
{
643+
List*result;
633644
Relidsqualscope;
634645
Relidsinner_join_rels;
646+
List*postponed_qual_list=NIL;
635647

636648
/* Start recursion at top of jointree */
637649
Assert(root->parse->jointree!=NULL&&
638650
IsA(root->parse->jointree,FromExpr));
639651

640-
returndeconstruct_recurse(root, (Node*)root->parse->jointree, false,
641-
&qualscope,&inner_join_rels);
652+
result=deconstruct_recurse(root, (Node*)root->parse->jointree, false,
653+
&qualscope,&inner_join_rels,
654+
&postponed_qual_list);
655+
656+
/* Shouldn't be any leftover quals */
657+
Assert(postponed_qual_list==NIL);
658+
659+
returnresult;
642660
}
643661

644662
/*
@@ -656,13 +674,16 @@ deconstruct_jointree(PlannerInfo *root)
656674
**inner_join_rels gets the set of base Relids syntactically included in
657675
*inner joins appearing at or below this jointree node (do not modify
658676
*or free this, either)
677+
**postponed_qual_list is a list of PostponedQual structs, which we can
678+
*add quals to if they turn out to belong to a higher join level
659679
*Return value is the appropriate joinlist for this jointree node
660680
*
661681
* In addition, entries will be added to root->join_info_list for outer joins.
662682
*/
663683
staticList*
664684
deconstruct_recurse(PlannerInfo*root,Node*jtnode,boolbelow_outer_join,
665-
Relids*qualscope,Relids*inner_join_rels)
685+
Relids*qualscope,Relids*inner_join_rels,
686+
List**postponed_qual_list)
666687
{
667688
List*joinlist;
668689

@@ -685,6 +706,7 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
685706
elseif (IsA(jtnode,FromExpr))
686707
{
687708
FromExpr*f= (FromExpr*)jtnode;
709+
List*child_postponed_quals=NIL;
688710
intremaining;
689711
ListCell*l;
690712

@@ -707,7 +729,8 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
707729
sub_joinlist=deconstruct_recurse(root,lfirst(l),
708730
below_outer_join,
709731
&sub_qualscope,
710-
inner_join_rels);
732+
inner_join_rels,
733+
&child_postponed_quals);
711734
*qualscope=bms_add_members(*qualscope,sub_qualscope);
712735
sub_members=list_length(sub_joinlist);
713736
remaining--;
@@ -728,6 +751,23 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
728751
if (list_length(f->fromlist)>1)
729752
*inner_join_rels=*qualscope;
730753

754+
/*
755+
* Try to process any quals postponed by children.If they need
756+
* further postponement, add them to my output postponed_qual_list.
757+
*/
758+
foreach(l,child_postponed_quals)
759+
{
760+
PostponedQual*pq= (PostponedQual*)lfirst(l);
761+
762+
if (bms_is_subset(pq->relids,*qualscope))
763+
distribute_qual_to_rels(root,pq->qual,
764+
false,below_outer_join,JOIN_INNER,
765+
*qualscope,NULL,NULL,NULL,
766+
NULL);
767+
else
768+
*postponed_qual_list=lappend(*postponed_qual_list,pq);
769+
}
770+
731771
/*
732772
* Now process the top-level quals.
733773
*/
@@ -737,12 +777,14 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
737777

738778
distribute_qual_to_rels(root,qual,
739779
false,below_outer_join,JOIN_INNER,
740-
*qualscope,NULL,NULL,NULL);
780+
*qualscope,NULL,NULL,NULL,
781+
postponed_qual_list);
741782
}
742783
}
743784
elseif (IsA(jtnode,JoinExpr))
744785
{
745786
JoinExpr*j= (JoinExpr*)jtnode;
787+
List*child_postponed_quals=NIL;
746788
Relidsleftids,
747789
rightids,
748790
left_inners,
@@ -771,10 +813,12 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
771813
caseJOIN_INNER:
772814
leftjoinlist=deconstruct_recurse(root,j->larg,
773815
below_outer_join,
774-
&leftids,&left_inners);
816+
&leftids,&left_inners,
817+
&child_postponed_quals);
775818
rightjoinlist=deconstruct_recurse(root,j->rarg,
776819
below_outer_join,
777-
&rightids,&right_inners);
820+
&rightids,&right_inners,
821+
&child_postponed_quals);
778822
*qualscope=bms_union(leftids,rightids);
779823
*inner_join_rels=*qualscope;
780824
/* Inner join adds no restrictions for quals */
@@ -784,21 +828,25 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
784828
caseJOIN_ANTI:
785829
leftjoinlist=deconstruct_recurse(root,j->larg,
786830
below_outer_join,
787-
&leftids,&left_inners);
831+
&leftids,&left_inners,
832+
&child_postponed_quals);
788833
rightjoinlist=deconstruct_recurse(root,j->rarg,
789834
true,
790-
&rightids,&right_inners);
835+
&rightids,&right_inners,
836+
&child_postponed_quals);
791837
*qualscope=bms_union(leftids,rightids);
792838
*inner_join_rels=bms_union(left_inners,right_inners);
793839
nonnullable_rels=leftids;
794840
break;
795841
caseJOIN_SEMI:
796842
leftjoinlist=deconstruct_recurse(root,j->larg,
797843
below_outer_join,
798-
&leftids,&left_inners);
844+
&leftids,&left_inners,
845+
&child_postponed_quals);
799846
rightjoinlist=deconstruct_recurse(root,j->rarg,
800847
below_outer_join,
801-
&rightids,&right_inners);
848+
&rightids,&right_inners,
849+
&child_postponed_quals);
802850
*qualscope=bms_union(leftids,rightids);
803851
*inner_join_rels=bms_union(left_inners,right_inners);
804852
/* Semi join adds no restrictions for quals */
@@ -807,10 +855,12 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
807855
caseJOIN_FULL:
808856
leftjoinlist=deconstruct_recurse(root,j->larg,
809857
true,
810-
&leftids,&left_inners);
858+
&leftids,&left_inners,
859+
&child_postponed_quals);
811860
rightjoinlist=deconstruct_recurse(root,j->rarg,
812861
true,
813-
&rightids,&right_inners);
862+
&rightids,&right_inners,
863+
&child_postponed_quals);
814864
*qualscope=bms_union(leftids,rightids);
815865
*inner_join_rels=bms_union(left_inners,right_inners);
816866
/* each side is both outer and inner */
@@ -853,15 +903,41 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
853903
ojscope=NULL;
854904
}
855905

856-
/* Process the qual clauses */
906+
/*
907+
* Try to process any quals postponed by children.If they need
908+
* further postponement, add them to my output postponed_qual_list.
909+
*/
910+
foreach(l,child_postponed_quals)
911+
{
912+
PostponedQual*pq= (PostponedQual*)lfirst(l);
913+
914+
if (bms_is_subset(pq->relids,*qualscope))
915+
distribute_qual_to_rels(root,pq->qual,
916+
false,below_outer_join,j->jointype,
917+
*qualscope,
918+
ojscope,nonnullable_rels,NULL,
919+
NULL);
920+
else
921+
{
922+
/*
923+
* We should not be postponing any quals past an outer join.
924+
* If this Assert fires, pull_up_subqueries() messed up.
925+
*/
926+
Assert(j->jointype==JOIN_INNER);
927+
*postponed_qual_list=lappend(*postponed_qual_list,pq);
928+
}
929+
}
930+
931+
/* Process the JOIN's qual clauses */
857932
foreach(l, (List*)j->quals)
858933
{
859934
Node*qual= (Node*)lfirst(l);
860935

861936
distribute_qual_to_rels(root,qual,
862937
false,below_outer_join,j->jointype,
863938
*qualscope,
864-
ojscope,nonnullable_rels,NULL);
939+
ojscope,nonnullable_rels,NULL,
940+
postponed_qual_list);
865941
}
866942

867943
/* Now we can add the SpecialJoinInfo to join_info_list */
@@ -1154,7 +1230,8 @@ make_outerjoininfo(PlannerInfo *root,
11541230
* the appropriate list for each rel. Alternatively, if the clause uses a
11551231
* mergejoinable operator and is not delayed by outer-join rules, enter
11561232
* the left- and right-side expressions into the query's list of
1157-
* EquivalenceClasses.
1233+
* EquivalenceClasses. Alternatively, if the clause needs to be treated
1234+
* as belonging to a higher join level, just add it to postponed_qual_list.
11581235
*
11591236
* 'clause': the qual clause to be distributed
11601237
* 'is_deduced': TRUE if the qual came from implied-equality deduction
@@ -1170,6 +1247,9 @@ make_outerjoininfo(PlannerInfo *root,
11701247
*equal qualscope)
11711248
* 'deduced_nullable_relids': if is_deduced is TRUE, the nullable relids to
11721249
*impute to the clause; otherwise NULL
1250+
* 'postponed_qual_list': list of PostponedQual structs, which we can add
1251+
*this qual to if it turns out to belong to a higher join level.
1252+
*Can be NULL if caller knows postponement is impossible.
11731253
*
11741254
* 'qualscope' identifies what level of JOIN the qual came from syntactically.
11751255
* 'ojscope' is needed if we decide to force the qual up to the outer-join
@@ -1190,7 +1270,8 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
11901270
Relidsqualscope,
11911271
Relidsojscope,
11921272
Relidsouterjoin_nonnullable,
1193-
Relidsdeduced_nullable_relids)
1273+
Relidsdeduced_nullable_relids,
1274+
List**postponed_qual_list)
11941275
{
11951276
Relidsrelids;
11961277
boolis_pushed_down;
@@ -1207,20 +1288,33 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
12071288
relids=pull_varnos(clause);
12081289

12091290
/*
1210-
* Normally relids is a subset of qualscope, and we like to check that
1211-
* here as a crosscheck on the parser and rewriter. That need not be the
1212-
* case when there are LATERAL RTEs, however: the clause could contain
1213-
* references to rels outside its syntactic scope as a consequence of
1214-
* pull-up of such references from a LATERAL subquery below it. So, only
1215-
* check if the query contains no LATERAL RTEs.
1216-
*
1217-
* However, if it's an outer-join clause, we always insist that relids be
1218-
* a subset of ojscope. This is safe because is_simple_subquery()
1219-
* disallows pullup of LATERAL subqueries that could cause the restriction
1220-
* to be violated.
1291+
* In ordinary SQL, a WHERE or JOIN/ON clause can't reference any rels
1292+
* that aren't within its syntactic scope; however, if we pulled up a
1293+
* LATERAL subquery then we might find such references in quals that have
1294+
* been pulled up.We need to treat such quals as belonging to the join
1295+
* level that includes every rel they reference. Although we could make
1296+
* pull_up_subqueries() place such quals correctly to begin with, it's
1297+
* easier to handle it here. When we find a clause that contains Vars
1298+
* outside its syntactic scope, we add it to the postponed-quals list, and
1299+
* process it once we've recursed back up to the appropriate join level.
1300+
*/
1301+
if (!bms_is_subset(relids,qualscope))
1302+
{
1303+
PostponedQual*pq= (PostponedQual*)palloc(sizeof(PostponedQual));
1304+
1305+
Assert(root->hasLateralRTEs);/* shouldn't happen otherwise */
1306+
Assert(jointype==JOIN_INNER);/* mustn't postpone past outer join */
1307+
Assert(!is_deduced);/* shouldn't be deduced, either */
1308+
pq->qual=clause;
1309+
pq->relids=relids;
1310+
*postponed_qual_list=lappend(*postponed_qual_list,pq);
1311+
return;
1312+
}
1313+
1314+
/*
1315+
* If it's an outer-join clause, also check that relids is a subset of
1316+
* ojscope. (This should not fail if the syntactic scope check passed.)
12211317
*/
1222-
if (!root->hasLateralRTEs&& !bms_is_subset(relids,qualscope))
1223-
elog(ERROR,"JOIN qualification cannot refer to other relations");
12241318
if (ojscope&& !bms_is_subset(relids,ojscope))
12251319
elog(ERROR,"JOIN qualification cannot refer to other relations");
12261320

@@ -1874,7 +1968,8 @@ process_implied_equality(PlannerInfo *root,
18741968
*/
18751969
distribute_qual_to_rels(root, (Node*)clause,
18761970
true,below_outer_join,JOIN_INNER,
1877-
qualscope,NULL,NULL,nullable_relids);
1971+
qualscope,NULL,NULL,nullable_relids,
1972+
NULL);
18781973
}
18791974

18801975
/*

0 commit comments

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