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

Commit05dc5f4

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 parent43085a4 commit05dc5f4

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
@@ -3438,7 +3438,6 @@ static List *
34383438
reorder_grouping_sets(List*groupingsets,List*sortclause)
34393439
{
34403440
ListCell*lc;
3441-
ListCell*lc2;
34423441
List*previous=NIL;
34433442
List*result=NIL;
34443443

@@ -3448,35 +3447,33 @@ reorder_grouping_sets(List *groupingsets, List *sortclause)
34483447
List*new_elems=list_difference_int(candidate,previous);
34493448
GroupingSetData*gs=makeNode(GroupingSetData);
34503449

3451-
if (list_length(new_elems)>0)
3450+
while (list_length(sortclause)>list_length(previous)&&
3451+
list_length(new_elems)>0)
34523452
{
3453-
while (list_length(sortclause)>list_length(previous))
3454-
{
3455-
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3456-
intref=sc->tleSortGroupRef;
3453+
SortGroupClause*sc=list_nth(sortclause,list_length(previous));
3454+
intref=sc->tleSortGroupRef;
34573455

3458-
if (list_member_int(new_elems,ref))
3459-
{
3460-
previous=lappend_int(previous,ref);
3461-
new_elems=list_delete_int(new_elems,ref);
3462-
}
3463-
else
3464-
{
3465-
/* diverged from the sortclause; give up on it */
3466-
sortclause=NIL;
3467-
break;
3468-
}
3456+
if (list_member_int(new_elems,ref))
3457+
{
3458+
previous=lappend_int(previous,ref);
3459+
new_elems=list_delete_int(new_elems,ref);
34693460
}
3470-
3471-
foreach(lc2,new_elems)
3461+
else
34723462
{
3473-
previous=lappend_int(previous,lfirst_int(lc2));
3463+
/* diverged from the sortclause; give up on it */
3464+
sortclause=NIL;
3465+
break;
34743466
}
34753467
}
34763468

3469+
/*
3470+
* Safe to use list_concat (which shares cells of the second arg)
3471+
* because we know that new_elems does not share cells with anything.
3472+
*/
3473+
previous=list_concat(previous,new_elems);
3474+
34773475
gs->set=list_copy(previous);
34783476
result=lcons(gs,result);
3479-
list_free(new_elems);
34803477
}
34813478

34823479
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