Обсуждение: Difference in behaviour between 9.6 and 10.1: GROUPING with ROLLUP ,difference in order and error with unnest
Hello,
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=#
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 -----------------------------------------------------
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.
>>>>> "Srikanth" == Srikanth M K <srixmk@gmail.com> writes: Srikanth> Question 1: Was the ordering of the aggregate rows always Srikanth> undefined? Yes. In the absence of an ORDER BY clause, the ordering of result rows is _always_ undefined. It just so happened that prior to pg 10, ROLLUP was always implemented by sorting, so it appeared to produce results in a stable order (even though this was never guaranteed). PG 10 added the ability to do grouping sets via hashing, which is a significant performance advantage in many cases, but means that the order of rows will be more variable. Srikanth> Under 9.6.6 it was always at the end of the base rows, under Srikanth> 10.1 it seems to be usually at the beginning of the Srikanth> corresponding block of base rows. If you don't use ORDER BY, you can't make any assumptions about the order of results. Srikanth> Question 2: Is the error regarding aggregate function calls Srikanth> under 10.1 as planned or is it a bug? That I guess is fallout from the changes to how SRFs in the targetlist are handled. SRFs in aggregate function arguments weren't really allowed before, but while GROUPING() is syntactically an aggregate function, it does not in fact evaluate its arguments (the arguments are simply matched to grouping expressions). Before pg 10, the restriction would only kick in at runtime (as "set-valued function called in context that cannot accept a set") and only if the SRF call wasn't itself a grouping expression (because if it was, it'd be evaluated before the grouping node). Commits 0436f6bde and 9c7dc89282b added explicit parse-time checks for SRF usage in a number of contexts. I don't think it was intentional that it errors on this specific kind of query. The more interesting question is why check_agg_arguments_walker recurses for GROUPING when it does not for actual aggregates; this is probably my code originally, so I will need to dig up exactly why that is. The workaround (which is arguably a better way to write the query in the first place), as suggested in the error HINT, is to move the unnest to a lateral call: select tag, grouping (tag), sum(qty) from tags, unnest(tags) as tag group by rollup(tag); -- Andrew (irc:RhodiumToad)
Thanks so much for the detailed response, Andrew. That clears up things quite a bit for me.
As you suggested, I had already re-structured the unnest query so that really wasn't a major issue. I also incorporated explicit ordering, so all is well!
On 2 February 2018 at 12:54, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Srikanth" == Srikanth M K <srixmk@gmail.com> writes:
Srikanth> Question 1: Was the ordering of the aggregate rows always
Srikanth> undefined?
Yes.
In the absence of an ORDER BY clause, the ordering of result rows is
_always_ undefined. It just so happened that prior to pg 10, ROLLUP was
always implemented by sorting, so it appeared to produce results in a
stable order (even though this was never guaranteed). PG 10 added the
ability to do grouping sets via hashing, which is a significant
performance advantage in many cases, but means that the order of rows
will be more variable.
Srikanth> Under 9.6.6 it was always at the end of the base rows, under
Srikanth> 10.1 it seems to be usually at the beginning of the
Srikanth> corresponding block of base rows.
If you don't use ORDER BY, you can't make any assumptions about the
order of results.
Srikanth> Question 2: Is the error regarding aggregate function calls
Srikanth> under 10.1 as planned or is it a bug?
That I guess is fallout from the changes to how SRFs in the targetlist
are handled. SRFs in aggregate function arguments weren't really allowed
before, but while GROUPING() is syntactically an aggregate function, it
does not in fact evaluate its arguments (the arguments are simply
matched to grouping expressions). Before pg 10, the restriction would
only kick in at runtime (as "set-valued function called in context that
cannot accept a set") and only if the SRF call wasn't itself a grouping
expression (because if it was, it'd be evaluated before the grouping
node).
Commits 0436f6bde and 9c7dc89282b added explicit parse-time checks for
SRF usage in a number of contexts. I don't think it was intentional that
it errors on this specific kind of query. The more interesting question
is why check_agg_arguments_walker recurses for GROUPING when it does not
for actual aggregates; this is probably my code originally, so I will
need to dig up exactly why that is.
The workaround (which is arguably a better way to write the query in the
first place), as suggested in the error HINT, is to move the unnest to a
lateral call:
select tag, grouping (tag), sum(qty)
from tags, unnest(tags) as tag
group by rollup(tag);
--
Andrew (irc:RhodiumToad)