GSets: Fix bug involving GROUPING and HAVING together

Поиск
Список
Период
Сортировка
От Jeevan Chalke
Тема GSets: Fix bug involving GROUPING and HAVING together
Дата
Msg-id CAM2+6=WG9omG5rFOMAYBweJxmpTaapvVp5pCeMrE6BfpCwr4Og@mail.gmail.com
обсуждение исходный текст
Ответы Re: GSets: Fix bug involving GROUPING and HAVING together  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Re: GSets: Fix bug involving GROUPING and HAVING together  (Andres Freund <andres@anarazel.de>)
Список pgsql-hackers
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

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Heikki Linnakangas
Дата:
Сообщение: Re: [BUGS] BUG #13126: table constraint loses its comment
Следующее
От: Simon Riggs
Дата:
Сообщение: Re: TABLESAMPLE patch is really in pretty sad shape