Re: Weird behaviour of ROLLUP/GROUPING

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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