diff options
| -rw-r--r-- | src/backend/parser/parse_clause.c | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 121 | ||||
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 29 |
3 files changed, 160 insertions, 3 deletions
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 4e490b23b4e..59808568a52 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -1812,7 +1812,7 @@ findTargetlistEntrySQL99(ParseState *pstate, Node *node, List **tlist, * Inside a grouping set (ROLLUP, CUBE, or GROUPING SETS), we expect the * content to be nested no more than 2 deep: i.e. ROLLUP((a,b),(c,d)) is * ok, but ROLLUP((a,(b,c)),d) is flattened to ((a,b,c),d), which we then - * normalize to ((a,b,c),(d)). + * (later) normalize to ((a,b,c),(d)). * * CUBE or ROLLUP can be nested inside GROUPING SETS (but not the reverse), * and we leave that alone if we find it. But if we see GROUPING SETS inside @@ -1881,9 +1881,16 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets) foreach(l2, gset->content) { - Node *n2 = flatten_grouping_sets(lfirst(l2), false, NULL); + Node *n1 = lfirst(l2); + Node *n2 = flatten_grouping_sets(n1, false, NULL); - result_set = lappend(result_set, n2); + if (IsA(n1, GroupingSet) && + ((GroupingSet *)n1)->kind == GROUPING_SET_SETS) + { + result_set = list_concat(result_set, (List *) n2); + } + else + result_set = lappend(result_set, n2); } /* diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index bdd77f8979c..b0b8c4b7f26 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -145,6 +145,127 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum | | 12 | 36 (6 rows) +-- nesting with grouping sets +select sum(c) from gstest2 + group by grouping sets((), grouping sets((), grouping sets(()))) + order by 1 desc; + sum +----- + 12 + 12 + 12 +(3 rows) + +select sum(c) from gstest2 + group by grouping sets((), grouping sets((), grouping sets(((a, b))))) + order by 1 desc; + sum +----- + 12 + 12 + 8 + 2 + 2 +(5 rows) + +select sum(c) from gstest2 + group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) + order by 1 desc; + sum +----- + 12 + 12 + 6 + 6 + 6 + 6 +(6 rows) + +select sum(c) from gstest2 + group by grouping sets(a, grouping sets(a, cube(b))) + order by 1 desc; + sum +----- + 12 + 10 + 10 + 8 + 4 + 2 + 2 +(7 rows) + +select sum(c) from gstest2 + group by grouping sets(grouping sets((a, (b)))) + order by 1 desc; + sum +----- + 8 + 2 + 2 +(3 rows) + +select sum(c) from gstest2 + group by grouping sets(grouping sets((a, b))) + order by 1 desc; + sum +----- + 8 + 2 + 2 +(3 rows) + +select sum(c) from gstest2 + group by grouping sets(grouping sets(a, grouping sets(a), a)) + order by 1 desc; + sum +----- + 10 + 10 + 10 + 2 + 2 + 2 +(6 rows) + +select sum(c) from gstest2 + group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) + order by 1 desc; + sum +----- + 10 + 10 + 10 + 10 + 10 + 10 + 10 + 10 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2 +(16 rows) + +select sum(c) from gstest2 + group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) + order by 1 desc; + sum +----- + 10 + 8 + 8 + 2 + 2 + 2 + 2 + 2 +(8 rows) + -- empty input: first is 0 rows, second 1, third 3 etc. select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); a | b | sum | count diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index 8eb580812a9..bff85d0db55 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -73,6 +73,35 @@ select grouping(a), a, array_agg(b), select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by rollup (a,b) order by rsum, a, b; +-- nesting with grouping sets +select sum(c) from gstest2 + group by grouping sets((), grouping sets((), grouping sets(()))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets((), grouping sets((), grouping sets(((a, b))))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c)))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(a, grouping sets(a, cube(b))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(grouping sets((a, (b)))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(grouping sets((a, b))) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(grouping sets(a, grouping sets(a), a)) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets(grouping sets(a, grouping sets(a, grouping sets(a), ((a)), a, grouping sets(a), (a)), a)) + order by 1 desc; +select sum(c) from gstest2 + group by grouping sets((a,(a,b)), grouping sets((a,(a,b)),a)) + order by 1 desc; + -- empty input: first is 0 rows, second 1, third 3 etc. select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); |