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).
