Hi,
It looks like we do support nested GROUPING SETS, I mean Sets withing
Sets, not other types. However this nesting is broken.
Here is the simple example where I would expect three rows in the
result. But unfortunately it is giving "unrecognized node type"
error. Which is something weird and need a fix.
postgres=# create table gstest2 (a integer, b integer, c integer);
postgres=# insert into gstest2 values (1,1,1), (1,1,1), (1,1,1),
(1,1,1), (1,1,1), (1,1,1), (1,1,2), (1,2,2), (2,2,2);
postgres=# select sum(c) from gstest2
group by grouping sets((), grouping sets((), grouping sets(())))
order by 1 desc;
ERROR: unrecognized node type: 926
I spend much time to understand the cause and was looking into
transformGroupingSet() and transformGroupClauseList() function.
I have tried fixing "unrecognized node type: 926" error there,
but later it is failing with "unrecognized node type: 656".
Later I have realized that we have actually have an issue while
flattening grouping sets. If we have nested grouping sets like
above, then we are getting GroupingSet node inside the list and
transformGroupClauseList() does not expect that and end up with
this error.
I have tried fixing this issue in flatten_grouping_sets(), after
flattening grouping sets node, we need to concat the result with
the existing list and should not append. This alone does not
solve the issue as we need a list when we have ROW expression.
Thus there, if not top level, I am creating a list now.
Attached patch with few testcases too.
Please have a look.
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index e90e1d6..31d4331 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1779,8 +1779,19 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
RowExpr *r = (RowExpr *) expr;
if (r->row_format == COERCE_IMPLICIT_CAST)
- return flatten_grouping_sets((Node *) r->args,
- false, NULL);
+ {
+ Node *n1 = flatten_grouping_sets((Node *) r->args,
+ false, NULL);
+
+ /*
+ * Make a list for row expression if toplevel is false,
+ * return flatten list otherwise
+ */
+ if (toplevel)
+ return (Node *) n1;
+ else
+ return (Node *) list_make1(n1);
+ }
}
break;
case T_GroupingSet:
@@ -1805,7 +1816,10 @@ flatten_grouping_sets(Node *expr, bool toplevel, bool *hasGroupingSets)
{
Node *n2 = flatten_grouping_sets(lfirst(l2), false, NULL);
- result_set = lappend(result_set, n2);
+ if (IsA(n2, List))
+ 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 adb39b3..5c47717 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -145,6 +145,112 @@ 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)
+
-- 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 0883afd..e478d34 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -73,6 +73,32 @@ 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;
+
-- 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
