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

Commita1637ca

Browse files
committed
Repair logic for reordering grouping sets optimization.
The logic in reorder_grouping_sets to order grouping set elements tomatch a pre-specified sort ordering was defective, resulting inunnecessary sort nodes (though the query output would still becorrect). Repair, simplifying the code a little, and add a test.Per report from Richard Guo, though I didn't use their patch. Originalbug seems to have been my fault.Backpatch back to 9.5 where grouping sets were introduced.Discussion:https://postgr.es/m/CAN_9JTzyjGcUjiBHxLsgqfk7PkdLGXiM=pwM+=ph2LsWw0WO1A@mail.gmail.com
1 parent69da8c1 commita1637ca

File tree

3 files changed

+34
-21
lines changed

3 files changed

+34
-21
lines changed

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

Lines changed: 18 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -3255,7 +3255,6 @@ static List *
32553255
reorder_grouping_sets(List*groupingsets,List*sortclause)
32563256
{
32573257
ListCell*lc;
3258-
ListCell*lc2;
32593258
List*previous=NIL;
32603259
List*result=NIL;
32613260

@@ -3265,35 +3264,33 @@ reorder_grouping_sets(List *groupingsets, List *sortclause)
32653264
List*new_elems=list_difference_int(candidate,previous);
32663265
GroupingSetData*gs=makeNode(GroupingSetData);
32673266

3268-
if (list_length(new_elems)>0)
3267+
while (list_length(sortclause)>list_length(previous)&&
3268+
list_length(new_elems)>0)
32693269
{
3270-
while (list_length(sortclause)>list_length(previous))
3271-
{
3272-
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3273-
intref=sc->tleSortGroupRef;
3270+
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3271+
intref=sc->tleSortGroupRef;
32743272

3275-
if (list_member_int(new_elems,ref))
3276-
{
3277-
previous=lappend_int(previous,ref);
3278-
new_elems=list_delete_int(new_elems,ref);
3279-
}
3280-
else
3281-
{
3282-
/* diverged from the sortclause; give up on it */
3283-
sortclause=NIL;
3284-
break;
3285-
}
3273+
if (list_member_int(new_elems,ref))
3274+
{
3275+
previous=lappend_int(previous,ref);
3276+
new_elems=list_delete_int(new_elems,ref);
32863277
}
3287-
3288-
foreach(lc2,new_elems)
3278+
else
32893279
{
3290-
previous=lappend_int(previous,lfirst_int(lc2));
3280+
/* diverged from the sortclause; give up on it */
3281+
sortclause=NIL;
3282+
break;
32913283
}
32923284
}
32933285

3286+
/*
3287+
* Safe to use list_concat (which shares cells of the second arg)
3288+
* because we know that new_elems does not share cells with anything.
3289+
*/
3290+
previous=list_concat(previous,new_elems);
3291+
32943292
gs->set=list_copy(previous);
32953293
result=lcons(gs,result);
3296-
list_free(new_elems);
32973294
}
32983295

32993296
list_free(previous);

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -637,6 +637,19 @@ select a, b, sum(v.x)
637637
| | 9
638638
(12 rows)
639639

640+
-- Test reordering of grouping sets
641+
explain (costs off)
642+
select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a;
643+
QUERY PLAN
644+
------------------------------------------------------------------------------
645+
GroupAggregate
646+
Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1
647+
Group Key: "*VALUES*".column3
648+
-> Sort
649+
Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1
650+
-> Values Scan on "*VALUES*"
651+
(6 rows)
652+
640653
-- Agg level check. This query should error out.
641654
select (select grouping(a,b) from gstest2) from gstest2 group by a,b;
642655
ERROR: arguments to GROUPING must be grouping expressions of the associated query level

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,6 +213,9 @@ select a, b, sum(v.x)
213213
from (values (1),(2)) v(x), gstest_data(v.x)
214214
group by cube (a,b)order by a,b;
215215

216+
-- Test reordering of grouping sets
217+
explain (costs off)
218+
select*from gstest1group by grouping sets((a,b,v),(v))order by v,b,a;
216219

217220
-- Agg level check. This query should error out.
218221
select (select grouping(a,b)from gstest2)from gstest2group by a,b;

0 commit comments

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