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

Commitff99d77

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 parent10bcd41 commitff99d77

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
@@ -994,11 +994,8 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
994994

995995
/*
996996
* The subquery's targetlist items are now in the appropriate form to
997-
* insert into the top query, but if we are under an outer join then
998-
* non-nullable items and lateral references may have to be turned into
999-
* PlaceHolderVars. If we are dealing with an appendrel member then
1000-
* anything that's not a simple Var has to be turned into a
1001-
* PlaceHolderVar. Set up required context data for pullup_replace_vars.
997+
* insert into the top query, except that we may need to wrap them in
998+
* PlaceHolderVars. Set up required context data for pullup_replace_vars.
1002999
*/
10031000
rvcontext.root=root;
10041001
rvcontext.targetlist=subquery->targetList;
@@ -1010,13 +1007,48 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
10101007
rvcontext.relids=NULL;
10111008
rvcontext.outer_hasSubLinks=&parse->hasSubLinks;
10121009
rvcontext.varno=varno;
1013-
rvcontext.need_phvs= (lowest_nulling_outer_join!=NULL||
1014-
containing_appendrel!=NULL);
1015-
rvcontext.wrap_non_vars=(containing_appendrel!=NULL);
1010+
/* these flags will be set below, if needed */
1011+
rvcontext.need_phvs= false;
1012+
rvcontext.wrap_non_vars=false;
10161013
/* initialize cache array with indexes 0 .. length(tlist) */
10171014
rvcontext.rv_cache=palloc0((list_length(subquery->targetList)+1)*
10181015
sizeof(Node*));
10191016

1017+
/*
1018+
* If we are under an outer join then non-nullable items and lateral
1019+
* references may have to be turned into PlaceHolderVars.
1020+
*/
1021+
if (lowest_nulling_outer_join!=NULL)
1022+
rvcontext.need_phvs= true;
1023+
1024+
/*
1025+
* If we are dealing with an appendrel member then anything that's not a
1026+
* simple Var has to be turned into a PlaceHolderVar. We force this to
1027+
* ensure that what we pull up doesn't get merged into a surrounding
1028+
* expression during later processing and then fail to match the
1029+
* expression actually available from the appendrel.
1030+
*/
1031+
if (containing_appendrel!=NULL)
1032+
{
1033+
rvcontext.need_phvs= true;
1034+
rvcontext.wrap_non_vars= true;
1035+
}
1036+
1037+
/*
1038+
* If the parent query uses grouping sets, we need a PlaceHolderVar for
1039+
* anything that's not a simple Var. Again, this ensures that expressions
1040+
* retain their separate identity so that they will match grouping set
1041+
* columns when appropriate. (It'd be sufficient to wrap values used in
1042+
* grouping set columns, and do so only in non-aggregated portions of the
1043+
* tlist and havingQual, but that would require a lot of infrastructure
1044+
* that pullup_replace_vars hasn't currently got.)
1045+
*/
1046+
if (parse->groupingSets)
1047+
{
1048+
rvcontext.need_phvs= true;
1049+
rvcontext.wrap_non_vars= true;
1050+
}
1051+
10201052
/*
10211053
* Replace all of the top query's references to the subquery's outputs
10221054
* 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
@@ -352,6 +352,51 @@ select a, d, grouping(a,b,c)
352352
2 | 2 | 2
353353
(4 rows)
354354

355+
-- check that pulled-up subquery outputs still go to null when appropriate
356+
select four, x
357+
from (select four, ten, 'foo'::text as x from tenk1) as t
358+
group by grouping sets (four, x)
359+
having x = 'foo';
360+
four | x
361+
------+-----
362+
| foo
363+
(1 row)
364+
365+
select four, x || 'x'
366+
from (select four, ten, 'foo'::text as x from tenk1) as t
367+
group by grouping sets (four, x)
368+
order by four;
369+
four | ?column?
370+
------+----------
371+
0 |
372+
1 |
373+
2 |
374+
3 |
375+
| foox
376+
(5 rows)
377+
378+
select (x+y)*1, sum(z)
379+
from (select 1 as x, 2 as y, 3 as z) s
380+
group by grouping sets (x+y, x);
381+
?column? | sum
382+
----------+-----
383+
3 | 3
384+
| 3
385+
(2 rows)
386+
387+
select x, not x as not_x, q2 from
388+
(select *, q1 = 1 as x from int8_tbl i1) as t
389+
group by grouping sets(x, q2)
390+
order by x, q2;
391+
x | not_x | q2
392+
---+-------+-------------------
393+
f | t |
394+
| | -4567890123456789
395+
| | 123
396+
| | 456
397+
| | 4567890123456789
398+
(5 rows)
399+
355400
-- simple rescan tests
356401
select a, b, sum(v.x)
357402
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
@@ -130,6 +130,26 @@ select a, d, grouping(a,b,c)
130130
from gstest3
131131
group by grouping sets ((a,b), (a,c));
132132

133+
-- check that pulled-up subquery outputs still go to null when appropriate
134+
select four, x
135+
from (select four, ten,'foo'::textas xfrom tenk1)as t
136+
group by grouping sets (four, x)
137+
having x='foo';
138+
139+
select four, x||'x'
140+
from (select four, ten,'foo'::textas xfrom tenk1)as t
141+
group by grouping sets (four, x)
142+
order by four;
143+
144+
select (x+y)*1,sum(z)
145+
from (select1as x,2as y,3as z) s
146+
group by grouping sets (x+y, x);
147+
148+
select x, not xas not_x, q2from
149+
(select*, q1=1as xfrom int8_tbl i1)as t
150+
group by grouping sets(x, q2)
151+
order by x, q2;
152+
133153
-- simple rescan tests
134154

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

0 commit comments

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