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

From: Srikanth M K <srixmk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest
Date: 2018-02-02 05:09:30
Message-ID: CAHnS-j9ZddhusaAKujcwyow8crBW92jSkRiF=4P+zMpDy=nRCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2018-02-02 07:24:55 Re: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP , difference in order and error with unnest
Previous Message Amit Kapila 2018-02-02 02:46:52 Re: Re: BUG #15039: some question about hash index code