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

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!

Thanks again for your time...
- Srix

On 2 February 2018 at 12:54, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Srikanth" == Srikanth M K <srixmk(at)gmail(dot)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)
>