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