Weird behaviour of ROLLUP/GROUPING

Поиск
Список
Период
Сортировка
От Guillaume Lelarge
Тема Weird behaviour of ROLLUP/GROUPING
Дата
Msg-id CAECtzeVSO_US8C2Khgfv54ZMUOBR4sWq+6_bLrETnWExHT=rFg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Weird behaviour of ROLLUP/GROUPING  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: Weird behaviour of ROLLUP/GROUPING  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew Gierth
Дата:
Сообщение: Re: lost "left join"
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Weird behaviour of ROLLUP/GROUPING