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

Commit19a00ea

Browse files
committed
In back branches, fix conditions for pullup of FROM-less subqueries.
In branches before commit4be058f, we have to prevent flatteningof subqueries with empty jointrees if the subqueries' outputcolumns might need to be wrapped in PlaceHolderVars. That'sbecause the empty jointree would result in empty phrels for thePlaceHolderVars, meaning we'd be unable to figure out where toevaluate them. However, we've failed to keep is_simple_subquery'scheck for this hazard in sync with what pull_up_simple_subqueryactually does. The former is checking "lowest_outer_join != NULL",whereas the conditions pull_up_simple_subquery actually uses areif (lowest_nulling_outer_join != NULL)if (containing_appendrel != NULL)if (parse->groupingSets)So the outer-join test is overly conservative, while we missed outchecking for appendrels and groupingSets. The appendrel omissionis harmless, because in that case we also check is_safe_append_memberwhich will also reject such subqueries. The groupingSets omissionis a bug though, leading to assertion failures or planner errorssuch as "variable not found in subplan target lists".is_simple_subquery has access to none of the three variables usedin the correct tests, but its callers do, so I chose to have thempass down a bool corresponding to the OR of these conditions.(The need for duplicative conditions would be a maintenancehazard in actively-developed code, but I'm not too concernedabout it in branches that have only ~ 1 year to live.)Per bug #17614 from Wei Wei. Patch v10 and v11 only, since we have abetter answer to this in v12 and later (indeed, the faulty check inis_simple_subquery is gone entirely).Discussion:https://postgr.es/m/17614-8ec20c85bdecaa2a@postgresql.org
1 parentd4adff0 commit19a00ea

File tree

3 files changed

+80
-20
lines changed

3 files changed

+80
-20
lines changed

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 29 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@ static void make_setop_translation_list(Query *query, Index newvarno,
8383
List**translated_vars);
8484
staticboolis_simple_subquery(Query*subquery,RangeTblEntry*rte,
8585
JoinExpr*lowest_outer_join,
86+
boolwill_need_phvs,
8687
booldeletion_ok);
8788
staticNode*pull_up_simple_values(PlannerInfo*root,Node*jtnode,
8889
RangeTblEntry*rte);
@@ -691,7 +692,11 @@ pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
691692
*/
692693
if (rte->rtekind==RTE_SUBQUERY&&
693694
is_simple_subquery(rte->subquery,rte,
694-
lowest_outer_join,deletion_ok)&&
695+
lowest_outer_join,
696+
(lowest_nulling_outer_join!=NULL||
697+
containing_appendrel!=NULL||
698+
root->parse->groupingSets),
699+
deletion_ok)&&
695700
(containing_appendrel==NULL||
696701
is_safe_append_member(rte->subquery)))
697702
returnpull_up_simple_subquery(root,jtnode,rte,
@@ -955,7 +960,11 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
955960
* pull_up_subqueries_recurse.
956961
*/
957962
if (is_simple_subquery(subquery,rte,
958-
lowest_outer_join,deletion_ok)&&
963+
lowest_outer_join,
964+
(lowest_nulling_outer_join!=NULL||
965+
containing_appendrel!=NULL||
966+
parse->groupingSets),
967+
deletion_ok)&&
959968
(containing_appendrel==NULL||is_safe_append_member(subquery)))
960969
{
961970
/* good to go */
@@ -1023,6 +1032,14 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
10231032
rvcontext.rv_cache=palloc0((list_length(subquery->targetList)+1)*
10241033
sizeof(Node*));
10251034

1035+
/*
1036+
* The next few stanzas identify cases where we might need to insert
1037+
* PlaceHolderVars. These same conditions must be checked by callers of
1038+
* is_simple_subquery(): pass will_need_phvs = true if anything below
1039+
* would set rvcontext.need_phvs = true. Else we can find ourselves
1040+
* trying to generate a PHV with empty phrels.
1041+
*/
1042+
10261043
/*
10271044
* If we are under an outer join then non-nullable items and lateral
10281045
* references may have to be turned into PlaceHolderVars.
@@ -1433,11 +1450,14 @@ make_setop_translation_list(Query *query, Index newvarno,
14331450
* (Note subquery is not necessarily equal to rte->subquery; it could be a
14341451
* processed copy of that.)
14351452
* lowest_outer_join is the lowest outer join above the subquery, or NULL.
1453+
* will_need_phvs is true if we might need to wrap the subquery outputs
1454+
* with PlaceHolderVars; see comments within.
14361455
* deletion_ok is TRUE if it'd be okay to delete the subquery entirely.
14371456
*/
14381457
staticbool
14391458
is_simple_subquery(Query*subquery,RangeTblEntry*rte,
14401459
JoinExpr*lowest_outer_join,
1460+
boolwill_need_phvs,
14411461
booldeletion_ok)
14421462
{
14431463
/*
@@ -1489,7 +1509,7 @@ is_simple_subquery(Query *subquery, RangeTblEntry *rte,
14891509

14901510
/*
14911511
* Don't pull up a subquery with an empty jointree, unless it has no quals
1492-
* and deletion_ok isTRUE andwe're not underneath an outer join.
1512+
* and deletion_ok istrue andwill_need_phvs is false.
14931513
*
14941514
* query_planner() will correctly generate a Result plan for a jointree
14951515
* that's totally empty, but we can't cope with an empty FromExpr
@@ -1504,16 +1524,18 @@ is_simple_subquery(Query *subquery, RangeTblEntry *rte,
15041524
* But even in a safe context, we must keep the subquery if it has any
15051525
* quals, because it's unclear where to put them in the upper query.
15061526
*
1507-
* Also, we must forbid pullup if such a subquery is underneath an outer
1508-
* join, because then we might need to wrap its output columns with
1509-
* PlaceHolderVars, and the PHVs would then have empty relid sets meaning
1527+
* Also, we must forbid pullup if the subquery outputs would need
1528+
* PlaceHolderVar wrappers; the PHVs would then have empty relids meaning
15101529
* we couldn't tell where to evaluate them. (This test is separate from
15111530
* the deletion_ok flag for possible future expansion: deletion_ok tells
15121531
* whether the immediate parent site in the jointree could cope, not
15131532
* whether we'd have PHV issues. It's possible this restriction could be
15141533
* fixed by letting the PHVs use the relids of the parent jointree item,
15151534
* but that complication is for another day.)
15161535
*
1536+
* The caller must pass will_need_phvs = true under the same conditions
1537+
* that would cause pull_up_simple_subquery to set need_phvs = true.
1538+
*
15171539
* Note that deletion of a subquery is also dependent on the check below
15181540
* that its targetlist contains no set-returning functions. Deletion from
15191541
* a FROM list or inner JOIN is okay only if the subquery must return
@@ -1522,7 +1544,7 @@ is_simple_subquery(Query *subquery, RangeTblEntry *rte,
15221544
if (subquery->jointree->fromlist==NIL&&
15231545
(subquery->jointree->quals!=NULL||
15241546
!deletion_ok||
1525-
lowest_outer_join!=NULL))
1547+
will_need_phvs))
15261548
return false;
15271549

15281550
/*

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

Lines changed: 44 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1677,27 +1677,25 @@ select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*)
16771677
-- test handling of outer GroupingFunc within subqueries
16781678
explain (costs off)
16791679
select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
1680-
QUERY PLAN
1681-
---------------------------
1682-
GroupAggregate
1683-
Group Key: $2
1680+
QUERY PLAN
1681+
---------------------------------
1682+
MixedAggregate
1683+
Hash Key: $1
16841684
Group Key: ()
1685-
InitPlan 1 (returns $1)
1686-
-> Result
1687-
InitPlan 3 (returns $2)
1688-
-> Result
1689-
InitPlan 4 (returns $3)
1690-
-> Result
16911685
-> Result
1692-
SubPlan 2
1686+
InitPlan 2 (returns $1)
1687+
-> Result
1688+
InitPlan 3 (returns $2)
1689+
-> Result
1690+
SubPlan 1
16931691
-> Result
1694-
(12 rows)
1692+
(10 rows)
16951693

16961694
select (select grouping(v1)) from (values ((select 1))) v(v1) group by cube(v1);
16971695
grouping
16981696
----------
1699-
0
17001697
1
1698+
0
17011699
(2 rows)
17021700

17031701
explain (costs off)
@@ -1723,4 +1721,37 @@ select (select grouping(v1)) from (values ((select 1))) v(v1) group by v1;
17231721
0
17241722
(1 row)
17251723

1724+
-- check that we don't pull up when a phrel-less PHV would result
1725+
explain (verbose, costs off)
1726+
select ss.f from int4_tbl as i4 cross join lateral (select i4.f1 as f) as ss
1727+
group by cube(ss.f) order by 1;
1728+
QUERY PLAN
1729+
--------------------------------------------------
1730+
Sort
1731+
Output: (i4.f1)
1732+
Sort Key: (i4.f1)
1733+
-> MixedAggregate
1734+
Output: (i4.f1)
1735+
Hash Key: (i4.f1)
1736+
Group Key: ()
1737+
-> Nested Loop
1738+
Output: (i4.f1)
1739+
-> Seq Scan on public.int4_tbl i4
1740+
Output: i4.f1
1741+
-> Result
1742+
Output: i4.f1
1743+
(13 rows)
1744+
1745+
select ss.f from int4_tbl as i4 cross join lateral (select i4.f1 as f) as ss
1746+
group by cube(ss.f) order by 1;
1747+
f
1748+
-------------
1749+
-2147483647
1750+
-123456
1751+
0
1752+
123456
1753+
2147483647
1754+
1755+
(6 rows)
1756+
17261757
-- end

‎src/test/regress/sql/groupingsets.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -477,4 +477,11 @@ explain (costs off)
477477
select (select grouping(v1))from (values ((select1))) v(v1)group by v1;
478478
select (select grouping(v1))from (values ((select1))) v(v1)group by v1;
479479

480+
-- check that we don't pull up when a phrel-less PHV would result
481+
explain (verbose, costs off)
482+
selectss.ffrom int4_tblas i4cross join lateral (selecti4.f1as f)as ss
483+
group by cube(ss.f)order by1;
484+
selectss.ffrom int4_tblas i4cross join lateral (selecti4.f1as f)as ss
485+
group by cube(ss.f)order by1;
486+
480487
-- end

0 commit comments

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