diff options
| author | Andres Freund <andres@anarazel.de> | 2015-07-26 15:34:29 +0200 |
|---|---|---|
| committer | Andres Freund <andres@anarazel.de> | 2015-07-26 16:53:13 +0200 |
| commit | 3500d1cc78f61927e05c0e73158b87ff24f81c09 (patch) | |
| tree | 9c17d76fbb254e9d45c835f62d601915187d3de4 | |
| parent | 65b86c1767a7dac0cc79dcfba7ba4cbd326dc03f (diff) | |
| download | postgresql-3500d1cc78f61927e05c0e73158b87ff24f81c09.tar.gz postgresql-3500d1cc78f61927e05c0e73158b87ff24f81c09.zip | |
Recognize GROUPING() as a aggregate expression.
Previously GROUPING() was not recognized as a aggregate expression,
erroneously allowing the planner to move it from HAVING to WHERE.
Author: Jeevan Chalke
Reviewed-By: Andrew Gierth
Discussion: CAM2+6=WG9omG5rFOMAYBweJxmpTaapvVp5pCeMrE6BfpCwr4Og@mail.gmail.com
Backpatch: 9.5, where grouping sets were introduced
| -rw-r--r-- | src/backend/optimizer/util/clauses.c | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 62 | ||||
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 17 |
3 files changed, 85 insertions, 1 deletions
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 0137e0ecfce..c72dbef1c83 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -390,7 +390,7 @@ make_ands_implicit(Expr *clause) /* * contain_agg_clause - * Recursively search for Aggref nodes within a clause. + * Recursively search for Aggref/GroupingFunc nodes within a clause. * * Returns true if any aggregate found. * @@ -417,6 +417,11 @@ contain_agg_clause_walker(Node *node, void *context) Assert(((Aggref *) node)->agglevelsup == 0); return true; /* abort the tree traversal and return true */ } + if (IsA(node, GroupingFunc)) + { + Assert(((GroupingFunc *) node)->agglevelsup == 0); + return true; /* abort the tree traversal and return true */ + } Assert(!IsA(node, SubLink)); return expression_tree_walker(node, contain_agg_clause_walker, context); } diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 2e12a53d69f..bdd77f8979c 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -486,6 +486,68 @@ having exists (select 1 from onek b where sum(distinct a.four) = b.four); 9 | 3 (25 rows) +-- HAVING with GROUPING queries +select ten, grouping(ten) from onek +group by grouping sets(ten) having grouping(ten) >= 0 +order by 2,1; + ten | grouping +-----+---------- + 0 | 0 + 1 | 0 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 +(10 rows) + +select ten, grouping(ten) from onek +group by grouping sets(ten, four) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 + | 1 + | 1 + | 1 +(4 rows) + +select ten, grouping(ten) from onek +group by rollup(ten) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 +(1 row) + +select ten, grouping(ten) from onek +group by cube(ten) having grouping(ten) > 0 +order by 2,1; + ten | grouping +-----+---------- + | 1 +(1 row) + +select ten, grouping(ten) from onek +group by (ten) having grouping(ten) >= 0 +order by 2,1; + ten | grouping +-----+---------- + 0 | 0 + 1 | 0 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 +(10 rows) + -- FILTER queries select ten, sum(distinct four) filter (where four::text ~ '123') from onek a group by rollup(ten); diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index eeea995f337..8eb580812a9 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -154,6 +154,23 @@ select ten, sum(distinct four) from onek a group by grouping sets((ten,four),(ten)) having exists (select 1 from onek b where sum(distinct a.four) = b.four); +-- HAVING with GROUPING queries +select ten, grouping(ten) from onek +group by grouping sets(ten) having grouping(ten) >= 0 +order by 2,1; +select ten, grouping(ten) from onek +group by grouping sets(ten, four) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by rollup(ten) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by cube(ten) having grouping(ten) > 0 +order by 2,1; +select ten, grouping(ten) from onek +group by (ten) having grouping(ten) >= 0 +order by 2,1; + -- FILTER queries select ten, sum(distinct four) filter (where four::text ~ '123') from onek a group by rollup(ten); |