Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP ,difference in order and error with unnest

Поиск
Список
Период
Сортировка
От Srikanth M K
Тема Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP ,difference in order and error with unnest
Дата
Msg-id CAHnS-j9ZddhusaAKujcwyow8crBW92jSkRiF=4P+zMpDy=nRCw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest
Список pgsql-bugs
Дерево обсуждения
Hello,

I've been running some aggregation queries in PostgreSQL 9.6.6 for some time, but the same query under 10.1 throws errors and produces results in a different order.
Reproducing the trace under both versions below:

---------------------------------------------------------------------- Trace 1: PostgreSQL 9.6.6 -------------------------------------------------
demo=# select version();
                                                     version                                                    
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

demo=# create table tags (id text, tags text[], qty int);

demo=# select * from tags;
 id  |    tags     | qty
-----+-------------+-----
 A01 | {tag1,tag2} |  10
 A02 | {tag1,tag3} |  20
 A03 | {tag4}      |  30
(3 rows)

demo=# select id, grouping (id), sum(qty) from tags group by rollup(id);
 id  | grouping | sum
-----+----------+-----
 A01 |        0 |  10
 A02 |        0 |  20
 A03 |        0 |  30
     |        1 |  60
(4 rows)

demo=# select unnest(tags), grouping (unnest(tags)), sum(qty) from tags group by rollup(unnest(tags));
 unnest | grouping | sum
--------+----------+-----
 tag1   |        0 |  30
 tag2   |        0 |  10
 tag3   |        0 |  20
 tag4   |        0 |  30
        |        1 |  90
(5 rows)


-------------------------------------------------- Trace 2: PostgreSQL 10.1 -----------------------------------------------------
demo=# select version();
                                                    version                                                    
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

demo=# select * from tags;
 id  |    tags     | qty
-----+-------------+-----
 A01 | {tag1,tag2} |  10
 A02 | {tag1,tag3} |  20
 A03 | {tag4}      |  30
(3 rows)

demo=# select id, grouping (id), sum(qty) from tags group by rollup(id);
 id  | grouping | sum
-----+----------+-----
     |        1 |  60
 A01 |        0 |  10
 A03 |        0 |  30
 A02 |        0 |  20
(4 rows)

demo=# select unnest(tags), grouping (unnest(tags)), sum(qty) from tags group by rollup(unnest(tags));
ERROR:  aggregate function calls cannot contain set-returning function calls
LINE 1: select unnest(tags), grouping (unnest(tags)), sum(qty) from ...
                                       ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.
demo=#


Question 1: Was the ordering of the aggregate rows always undefined? Under 9.6.6 it was always at the end of the base rows, under 10.1 it seems to be usually at the beginning of the corresponding block of base rows.

Question 2: Is the error regarding aggregate function calls under 10.1 as planned or is it a bug?

Thanks...
-  Srix.

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

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: Re: BUG #15039: some question about hash index code
Следующее
От: Andrew Gierth
Дата:
Сообщение: Re: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest