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

Commit9094767

Browse files
committed
Fix incorrect handling of subquery pullup in the presence of grouping sets.
If we flatten a subquery whose target list contains constants orexpressions, when those output columns are used in GROUPING SET columns,the planner was capable of doing the wrong thing by merging a pulled-upexpression into the surrounding expression during const-simplification.Then the late processing that attempts to match subexpressions to groupingsets would fail to match those subexpressions to grouping sets, with theeffect that they'd not go to null when expected.To fix, wrap such subquery outputs in PlaceHolderVars, ensuring thatthey preserve their separate identity throughout the planner's expressionprocessing. This is a bit of a band-aid, because the wrapper defeatsconst-simplification even in places where it would be safe to allow.But a nicer fix would likely be too invasive to back-patch, and theconsequences of the missed optimizations probably aren't large in mostcases.Back-patch to 9.5 where grouping sets were introduced.Heikki Linnakangas, with small mods and better test cases by me;additional review by Andrew GierthDiscussion:https://postgr.es/m/7dbdcf5c-b5a6-ef89-4958-da212fe10176@iki.fi
1 parentca4587f commit9094767

File tree

3 files changed

+105
-8
lines changed

3 files changed

+105
-8
lines changed

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

Lines changed: 40 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1003,11 +1003,8 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
10031003

10041004
/*
10051005
* The subquery's targetlist items are now in the appropriate form to
1006-
* insert into the top query, but if we are under an outer join then
1007-
* non-nullable items and lateral references may have to be turned into
1008-
* PlaceHolderVars. If we are dealing with an appendrel member then
1009-
* anything that's not a simple Var has to be turned into a
1010-
* PlaceHolderVar. Set up required context data for pullup_replace_vars.
1006+
* insert into the top query, except that we may need to wrap them in
1007+
* PlaceHolderVars. Set up required context data for pullup_replace_vars.
10111008
*/
10121009
rvcontext.root=root;
10131010
rvcontext.targetlist=subquery->targetList;
@@ -1019,13 +1016,48 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
10191016
rvcontext.relids=NULL;
10201017
rvcontext.outer_hasSubLinks=&parse->hasSubLinks;
10211018
rvcontext.varno=varno;
1022-
rvcontext.need_phvs= (lowest_nulling_outer_join!=NULL||
1023-
containing_appendrel!=NULL);
1024-
rvcontext.wrap_non_vars=(containing_appendrel!=NULL);
1019+
/* these flags will be set below, if needed */
1020+
rvcontext.need_phvs= false;
1021+
rvcontext.wrap_non_vars=false;
10251022
/* initialize cache array with indexes 0 .. length(tlist) */
10261023
rvcontext.rv_cache=palloc0((list_length(subquery->targetList)+1)*
10271024
sizeof(Node*));
10281025

1026+
/*
1027+
* If we are under an outer join then non-nullable items and lateral
1028+
* references may have to be turned into PlaceHolderVars.
1029+
*/
1030+
if (lowest_nulling_outer_join!=NULL)
1031+
rvcontext.need_phvs= true;
1032+
1033+
/*
1034+
* If we are dealing with an appendrel member then anything that's not a
1035+
* simple Var has to be turned into a PlaceHolderVar. We force this to
1036+
* ensure that what we pull up doesn't get merged into a surrounding
1037+
* expression during later processing and then fail to match the
1038+
* expression actually available from the appendrel.
1039+
*/
1040+
if (containing_appendrel!=NULL)
1041+
{
1042+
rvcontext.need_phvs= true;
1043+
rvcontext.wrap_non_vars= true;
1044+
}
1045+
1046+
/*
1047+
* If the parent query uses grouping sets, we need a PlaceHolderVar for
1048+
* anything that's not a simple Var. Again, this ensures that expressions
1049+
* retain their separate identity so that they will match grouping set
1050+
* columns when appropriate. (It'd be sufficient to wrap values used in
1051+
* grouping set columns, and do so only in non-aggregated portions of the
1052+
* tlist and havingQual, but that would require a lot of infrastructure
1053+
* that pullup_replace_vars hasn't currently got.)
1054+
*/
1055+
if (parse->groupingSets)
1056+
{
1057+
rvcontext.need_phvs= true;
1058+
rvcontext.wrap_non_vars= true;
1059+
}
1060+
10291061
/*
10301062
* Replace all of the top query's references to the subquery's outputs
10311063
* with copies of the adjusted subtlist items, being careful not to

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -389,6 +389,51 @@ select g as alias1, g as alias2
389389
3 |
390390
(6 rows)
391391

392+
-- check that pulled-up subquery outputs still go to null when appropriate
393+
select four, x
394+
from (select four, ten, 'foo'::text as x from tenk1) as t
395+
group by grouping sets (four, x)
396+
having x = 'foo';
397+
four | x
398+
------+-----
399+
| foo
400+
(1 row)
401+
402+
select four, x || 'x'
403+
from (select four, ten, 'foo'::text as x from tenk1) as t
404+
group by grouping sets (four, x)
405+
order by four;
406+
four | ?column?
407+
------+----------
408+
0 |
409+
1 |
410+
2 |
411+
3 |
412+
| foox
413+
(5 rows)
414+
415+
select (x+y)*1, sum(z)
416+
from (select 1 as x, 2 as y, 3 as z) s
417+
group by grouping sets (x+y, x);
418+
?column? | sum
419+
----------+-----
420+
3 | 3
421+
| 3
422+
(2 rows)
423+
424+
select x, not x as not_x, q2 from
425+
(select *, q1 = 1 as x from int8_tbl i1) as t
426+
group by grouping sets(x, q2)
427+
order by x, q2;
428+
x | not_x | q2
429+
---+-------+-------------------
430+
f | t |
431+
| | -4567890123456789
432+
| | 123
433+
| | 456
434+
| | 4567890123456789
435+
(5 rows)
436+
392437
-- simple rescan tests
393438
select a, b, sum(v.x)
394439
from (values (1),(2)) v(x), gstest_data(v.x)

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,26 @@ select g as alias1, g as alias2
152152
from generate_series(1,3) g
153153
group by alias1, rollup(alias2);
154154

155+
-- check that pulled-up subquery outputs still go to null when appropriate
156+
select four, x
157+
from (select four, ten,'foo'::textas xfrom tenk1)as t
158+
group by grouping sets (four, x)
159+
having x='foo';
160+
161+
select four, x||'x'
162+
from (select four, ten,'foo'::textas xfrom tenk1)as t
163+
group by grouping sets (four, x)
164+
order by four;
165+
166+
select (x+y)*1,sum(z)
167+
from (select1as x,2as y,3as z) s
168+
group by grouping sets (x+y, x);
169+
170+
select x, not xas not_x, q2from
171+
(select*, q1=1as xfrom int8_tbl i1)as t
172+
group by grouping sets(x, q2)
173+
order by x, q2;
174+
155175
-- simple rescan tests
156176

157177
select a, b,sum(v.x)

0 commit comments

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