Thread: Weird behaviour of ROLLUP/GROUPING

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

Thanks.

Regards.

--

Guillaume.

>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes:

 Guillaume> CASE grouping(to_char(b, 'MMYYYY'))

 Guillaume> ERROR:  arguments to GROUPING must be grouping expressions of the
 Guillaume> associated query level
 Guillaume> LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ...

 Guillaume> AFAICT, both queries should behave the same, though their
 Guillaume> actual behaviours are quite opposite. Working fine for the
 Guillaume> first, erroring out on the second.

 Guillaume> Does anyone has any idea what's going on here?

 Andrew> Not yet. But I will find out, since it seems to be a bug.

It is definitely a bug, to do with assignment of collations. It
specifically happens when you use GROUPING which contains any
subexpression of a collatable type, inside a CASE expr WHEN clause,
since that assigns collations to the expression much earlier in parsing
than the rest of the query, so the code that validates GROUPING ends up
trying to compare an expression which has had collations assigned to it
to one which has not, and so it thinks they differ.

I will see about fixing this, somehow.

>>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes:

 >> I will see about fixing this, somehow.

 Guillaume> Thanks a lot.

I've committed a fix (to all supported branches, since this bug actually
precedes the addition of GROUPING SETS and can be triggered with a
simple GROUP BY if you try hard enough). The regression test says it
works now, but it'd be good if you could try it again on REL_11_STABLE
(at commit e74d8c5085 or later) to check that it fixes your case.