On Wed, Jul 15, 2015 at 10:21 PM, Andrew Gierth <[email protected]
> wrote:
> >>>>> "Jeevan" == Jeevan Chalke <[email protected]> writes:
>
> Jeevan> Hi,
> Jeevan> It looks like we do support nested GROUPING SETS, I mean Sets
> Jeevan> withing Sets, not other types. However this nesting is broken.
>
> Good catch, but I'm not yet sure your fix is correct; I'll need to look
> into that.
>
Sure. Thanks.
However I wonder why we are supporting GROUPING SETS inside GROUPING SETS.
On Oracle, it is throwing an error.
We are not trying to be Oracle compatible, but just curious to know.
I have tried restricting it in attached patch.
But it may require few comment adjustment.
Thanks
>
> --
> Andrew (irc:RhodiumToad)
>
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e0ff6f1..738715f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -371,9 +371,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
relation_expr_list dostmt_opt_list
transform_element_list transform_type_list
-%type <list> group_by_list
+%type <list> group_by_list grouping_sets_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
-%type <node> grouping_sets_clause
+%type <node> grouping_sets_clause grouping_sets_item
%type <list> opt_fdw_options fdw_options
%type <defelt> fdw_option
@@ -10343,6 +10343,18 @@ group_by_item:
| grouping_sets_clause { $$ = $1; }
;
+grouping_sets_list:
+ grouping_sets_item { $$ = list_make1($1); }
+ | grouping_sets_list ',' grouping_sets_item { $$ = lappend($1,$3); }
+ ;
+
+grouping_sets_item:
+ a_expr { $$ = $1; }
+ | empty_grouping_set { $$ = $1; }
+ | cube_clause { $$ = $1; }
+ | rollup_clause { $$ = $1; }
+ ;
+
empty_grouping_set:
'(' ')'
{
@@ -10371,7 +10383,7 @@ cube_clause:
;
grouping_sets_clause:
- GROUPING SETS '(' group_by_list ')'
+ GROUPING SETS '(' grouping_sets_list ')'
{
$$ = (Node *) makeGroupingSet(GROUPING_SET_SETS, $4, @1);
}
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 842c2ae..e75dceb 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -145,6 +145,25 @@ 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(((a, b))))
+ order by 1 desc;
+ERROR: syntax error at or near "sets"
+LINE 2: group by grouping sets((), grouping sets(((a, b))))
+ ^
+select sum(c) from gstest2
+ group by grouping sets(grouping sets(rollup(c), grouping sets(cube(c))))
+ order by 1 desc;
+ERROR: syntax error at or near "sets"
+LINE 2: group by grouping sets(grouping sets(rollup(c), grouping s...
+ ^
+select sum(c) from gstest2
+ group by grouping sets(grouping sets(a, grouping sets(a), a))
+ order by 1 desc;
+ERROR: syntax error at or near "sets"
+LINE 2: group by grouping sets(grouping sets(a, grouping sets(a), ...
+ ^
-- 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 0bffb85..b7e4826 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -73,6 +73,17 @@ 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(((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(grouping sets(a, grouping sets(a), 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),());
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
