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.
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 |