From 3500d1cc78f61927e05c0e73158b87ff24f81c09 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Sun, 26 Jul 2015 15:34:29 +0200 Subject: [PATCH] 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 --- src/backend/optimizer/util/clauses.c | 7 ++- src/test/regress/expected/groupingsets.out | 62 ++++++++++++++++++++++ src/test/regress/sql/groupingsets.sql | 17 ++++++ 3 files changed, 85 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 0137e0ecfc..c72dbef1c8 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 2e12a53d69..bdd77f8979 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 eeea995f33..8eb580812a 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); -- 2.40.0