Postgres Pro
Downloads
Home   >   mailing lists

Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP ,difference in order and error with unnest - Mailing list pgsql-bugs

From Srikanth M K
Subject Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP ,difference in order and error with unnest
Date
Msg-id CAHnS-j9ZddhusaAKujcwyow8crBW92jSkRiF=4P+zMpDy=nRCw@mail.gmail.com
Whole thread Raw
Responses Re: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest
List pgsql-bugs
Tree view
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 by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Re: BUG #15039: some question about hash index code
Next
From: Andrew Gierth
Date:
Subject: Re: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest