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

Commit5c353d2

Browse files
tglsfdcpull[bot]
authored andcommitted
Fix "wrong varnullingrels" for subquery nestloop parameters.
If we apply outer join identity 3 when relation C is a subqueryhaving lateral references to relation B, then the lateral referenceswithin C continue to bear the original syntactically-correctvarnullingrels marks, but that won't match what is available fromthe outer side of the nestloop. Compensate for that inprocess_subquery_nestloop_params(). This is a slightly hacky fix,but we certainly don't want to re-plan C in toto for each possibleouter join order, so there's not a lot of better alternatives.Richard Guo and Tom Lane, per report from Markus WinandDiscussion:https://postgr.es/m/DFBB2D25-DE97-49CA-A60E-07C881EA59A7@winand.at
1 parent2c0c98e commit5c353d2

File tree

4 files changed

+59
-10
lines changed

4 files changed

+59
-10
lines changed

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2289,9 +2289,11 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
22892289
* the outer-join level at which they are used, Vars seen in the
22902290
* NestLoopParam expression may have nullingrels that are just a
22912291
* subset of those in the Vars actually available from the outer
2292-
* side. Not checking this exactly is a bit grotty, but the work
2293-
* needed to make things match up perfectly seems well out of
2294-
* proportion to the value.
2292+
* side. Another case that can cause that to happen is explained
2293+
* in the comments for process_subquery_nestloop_params. Not
2294+
* checking this exactly is a bit grotty, but the work needed to
2295+
* make things match up perfectly seems well out of proportion to
2296+
* the value.
22952297
*/
22962298
nlp->paramval= (Var*)fix_upper_expr(root,
22972299
(Node*)nlp->paramval,

‎src/backend/optimizer/util/paramassign.c

Lines changed: 29 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -421,8 +421,26 @@ replace_nestloop_param_placeholdervar(PlannerInfo *root, PlaceHolderVar *phv)
421421
* provide these values. This differs from replace_nestloop_param_var in
422422
* that the PARAM_EXEC slots to use have already been determined.
423423
*
424+
* An additional complication is that the subplan_params may contain
425+
* nullingrel markers that need adjustment. This occurs if we have applied
426+
* outer join identity 3,
427+
*(A leftjoin B on (Pab)) leftjoin C on (Pb*c)
428+
*= A leftjoin (B leftjoin C on (Pbc)) on (Pab)
429+
* and C is a subquery containing lateral references to B. It's still safe
430+
* to apply the identity, but the parser will have created those references
431+
* in the form "b*" (i.e., with varnullingrels listing the A/B join), while
432+
* what we will have available from the nestloop's outer side is just "b".
433+
* We deal with that here by stripping the nullingrels down to what is
434+
* available from the outer side according to root->curOuterRels.
435+
* That fixes matters for the case of forward application of identity 3.
436+
* If the identity was applied in the reverse direction, we will have
437+
* subplan_params containing too few nullingrel bits rather than too many.
438+
* Currently, that causes no problems because setrefs.c applies only a
439+
* subset check to nullingrels in NestLoopParams, but we'd have to work
440+
* harder if we ever want to tighten that check.
441+
*
424442
* Note that we also use root->curOuterRels as an implicit parameter for
425-
* sanity checks.
443+
* sanity checks and nullingrel adjustments.
426444
*/
427445
void
428446
process_subquery_nestloop_params(PlannerInfo*root,List*subplan_params)
@@ -449,17 +467,19 @@ process_subquery_nestloop_params(PlannerInfo *root, List *subplan_params)
449467
nlp= (NestLoopParam*)lfirst(lc2);
450468
if (nlp->paramno==pitem->paramId)
451469
{
452-
Assert(equal(var,nlp->paramval));
453470
/* Present, so nothing to do */
454471
break;
455472
}
456473
}
457474
if (lc2==NULL)
458475
{
459-
/* No, so add it */
476+
/* No, so add it after adjusting its nullingrels */
477+
var=copyObject(var);
478+
var->varnullingrels=bms_intersect(var->varnullingrels,
479+
root->curOuterRels);
460480
nlp=makeNode(NestLoopParam);
461481
nlp->paramno=pitem->paramId;
462-
nlp->paramval=copyObject(var);
482+
nlp->paramval=var;
463483
root->curOuterParams=lappend(root->curOuterParams,nlp);
464484
}
465485
}
@@ -480,17 +500,19 @@ process_subquery_nestloop_params(PlannerInfo *root, List *subplan_params)
480500
nlp= (NestLoopParam*)lfirst(lc2);
481501
if (nlp->paramno==pitem->paramId)
482502
{
483-
Assert(equal(phv,nlp->paramval));
484503
/* Present, so nothing to do */
485504
break;
486505
}
487506
}
488507
if (lc2==NULL)
489508
{
490-
/* No, so add it */
509+
/* No, so add it after adjusting its nullingrels */
510+
phv=copyObject(phv);
511+
phv->phnullingrels=bms_intersect(phv->phnullingrels,
512+
root->curOuterRels);
491513
nlp=makeNode(NestLoopParam);
492514
nlp->paramno=pitem->paramId;
493-
nlp->paramval= (Var*)copyObject(phv);
515+
nlp->paramval= (Var*)phv;
494516
root->curOuterParams=lappend(root->curOuterParams,nlp);
495517
}
496518
}

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2589,6 +2589,24 @@ on t2.q2 = 123;
25892589
-> Seq Scan on int8_tbl t5
25902590
(12 rows)
25912591

2592+
explain (costs off)
2593+
select * from int8_tbl t1
2594+
left join int8_tbl t2 on true
2595+
left join lateral
2596+
(select * from int8_tbl t3 where t3.q1 = t2.q1 offset 0) s
2597+
on t2.q1 = 1;
2598+
QUERY PLAN
2599+
-------------------------------------------
2600+
Nested Loop Left Join
2601+
-> Seq Scan on int8_tbl t1
2602+
-> Materialize
2603+
-> Nested Loop Left Join
2604+
Join Filter: (t2.q1 = 1)
2605+
-> Seq Scan on int8_tbl t2
2606+
-> Seq Scan on int8_tbl t3
2607+
Filter: (q1 = t2.q1)
2608+
(8 rows)
2609+
25922610
--
25932611
-- check a case where we formerly got confused by conflicting sort orders
25942612
-- in redundant merge join path keys

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -514,6 +514,13 @@ select * from int8_tbl t1 left join
514514
left join int8_tbl t5ont2.q1=t5.q1
515515
ont2.q2=123;
516516

517+
explain (costs off)
518+
select*from int8_tbl t1
519+
left join int8_tbl t2on true
520+
left join lateral
521+
(select*from int8_tbl t3wheret3.q1=t2.q1 offset0) s
522+
ont2.q1=1;
523+
517524
--
518525
-- check a case where we formerly got confused by conflicting sort orders
519526
-- in redundant merge join path keys

0 commit comments

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