Re: Weird behaviour of ROLLUP/GROUPING

st 16. 1. 2019 v 13:51 odesílatel Guillaume Lelarge <guillaume@lelarge.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.
>