On Wed, Jul 15, 2015 at 10:21 PM, Andrew Gierth <and...@tao11.riddles.org.uk > wrote:
> >>>>> "Jeevan" == Jeevan Chalke <jeevan.cha...@enterprisedb.com> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers