Re: Weird behaviour of ROLLUP/GROUPING

st 16. 1. 2019 v 13:51 odesílatel Guillaume Lelarge <guillaume(at)lelarge(dot)info>
napsal:

> Hi,
>
> One of my customers found something quite weird on his 9.6 cluster. Here
> is a quick demo showing the issue:
>
> -- quick demo table
> CREATE TABLE t1 (a integer, b timestamp, c integer);
>
> -- a working query
> SELECT
> CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
> CASE WHEN grouping(to_char(b, 'MMYYYY')) = 1
> THEN 'some date'
> ELSE to_char(b, 'MMYYYY') END AS new_b,
> sum(c)
> FROM t1
> GROUP BY ROLLUP(a, to_char(b,'MMYYYY'));
>
> -- the non-working query
> SELECT
> CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
> CASE grouping(to_char(b, 'MMYYYY'))
> WHEN 1 THEN 'some date'
> ELSE to_char(b, 'MMYYYY') END AS new_b,
> sum(c)
> FROM t1
> GROUP BY ROLLUP(a, to_char(b,'MMYYYY'));
> ERROR: arguments to GROUPING must be grouping expressions of the
> associated query level
> LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ...
> ^
>
> AFAICT, both queries should behave the same, though their actual
> behaviours are quite opposite. Working fine for the first, erroring out on
> the second.
>
> Does anyone has any idea what's going on here?
>
> BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE).
>

looks like PostgreSQL bug - it cannot to work with subqueries correctly

Pavel

>
> Thanks.
>
> Regards.
>
>
> --
> Guillaume.
>