Hi,
I have observed some fishy behavior related to GROUPING in HAVING clause
and when we have only one element in GROUPING SETS.
Basically, when we have only one element in GROUING SETS, we are assuming
it as a simple GROUP BY with one column. Due to which we are ending up with
this error.
If we have ROLLUP/CUBE or GROUPING SETS with multiple elements, then we are
not getting this error.
Here are some examples:
postgres=# select ten, grouping(ten) from onek
postgres-# group by grouping sets(ten) having grouping(ten) >= 0
postgres-# order by 2,1;
ERROR: parent of GROUPING is not Agg node
postgres=# select ten, grouping(ten) from onek
postgres-# group by grouping sets(ten, four) having grouping(ten) > 0
postgres-# order by 2,1;
ten | grouping
-----+----------
(0 rows)fix_bug_related_to_grouping_v1.patch
postgres=# select ten, grouping(ten) from onek
postgres-# group by rollup(ten) having grouping(ten) > 0
postgres-# order by 2,1;
ten | grouping
-----+----------
| 1
(1 row)
postgres=# select ten, grouping(ten) from onek
postgres-# group by cube(ten) having grouping(ten) > 0
postgres-# order by 2,1;
ten | grouping
-----+----------
| 1
(1 row)
I had a look over relevant code and found that contain_agg_clause_walker()
is not considering GroupingFunc as an aggregate node, due to which it is
failing to consider it in a having clause in subquery_planner().
Fix this by adding GroupingFunc node in this walker. We do it correctly in
contain_aggs_of_level_walker() in which we have handling for GroupingFunc
there.
Attached patch to fix this.
The side effect is that, if we have plain group by clause, then too we can
use GROUPING in HAVING clause. But I guess it is fine.
Let me know if I missed anything to consider here.
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company