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

Commitda53be2

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 parentc000a47 commitda53be2

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
@@ -3530,7 +3530,6 @@ static List *
35303530
reorder_grouping_sets(List*groupingsets,List*sortclause)
35313531
{
35323532
ListCell*lc;
3533-
ListCell*lc2;
35343533
List*previous=NIL;
35353534
List*result=NIL;
35363535

@@ -3540,35 +3539,33 @@ reorder_grouping_sets(List *groupingsets, List *sortclause)
35403539
List*new_elems=list_difference_int(candidate,previous);
35413540
GroupingSetData*gs=makeNode(GroupingSetData);
35423541

3543-
if (list_length(new_elems)>0)
3542+
while (list_length(sortclause)>list_length(previous)&&
3543+
list_length(new_elems)>0)
35443544
{
3545-
while (list_length(sortclause)>list_length(previous))
3546-
{
3547-
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3548-
intref=sc->tleSortGroupRef;
3545+
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3546+
intref=sc->tleSortGroupRef;
35493547

3550-
if (list_member_int(new_elems,ref))
3551-
{
3552-
previous=lappend_int(previous,ref);
3553-
new_elems=list_delete_int(new_elems,ref);
3554-
}
3555-
else
3556-
{
3557-
/* diverged from the sortclause; give up on it */
3558-
sortclause=NIL;
3559-
break;
3560-
}
3548+
if (list_member_int(new_elems,ref))
3549+
{
3550+
previous=lappend_int(previous,ref);
3551+
new_elems=list_delete_int(new_elems,ref);
35613552
}
3562-
3563-
foreach(lc2,new_elems)
3553+
else
35643554
{
3565-
previous=lappend_int(previous,lfirst_int(lc2));
3555+
/* diverged from the sortclause; give up on it */
3556+
sortclause=NIL;
3557+
break;
35663558
}
35673559
}
35683560

3561+
/*
3562+
* Safe to use list_concat (which shares cells of the second arg)
3563+
* because we know that new_elems does not share cells with anything.
3564+
*/
3565+
previous=list_concat(previous,new_elems);
3566+
35693567
gs->set=list_copy(previous);
35703568
result=lcons(gs,result);
3571-
list_free(new_elems);
35723569
}
35733570

35743571
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