Re: Ordering behavior for aggregates

Le mardi 13 décembre 2022, 14:05:10 CET Vik Fearing a écrit :
> On 12/13/22 13:55, Magnus Hagander wrote:
> > On Tue, Dec 13, 2022 at 1:51 PM Vik Fearing <vik(at)postgresfriends(dot)org>
wrote:
> >> However, it is completely useless for things like AVG() or SUM(). If
> >> you include it, the aggregate will do the sort even though it is neither
> >> required nor desired.

I'm not sure about this. For AVG and SUM, if you want reproducible results
with floating point numbers, you may want it. And if you disallow it for most
avg and sum implementations except for floating point types, it's not a very
consistent user experience.

> >>
> >> I am proposing something like pg_aggregate.aggordering which would be an
> >> enum of behaviors such as f=Forbidden, a=Allowed, r=Required. Currently
> >> all aggregates would have 'a' but I am thinking that a lot of them could
> >> be switched to 'f'. In that case, if a user supplies an ordering, an
> >> error is raised.
> >
> > Should there perhaps also be an option for "ignored" where we'd allow the
> > user to specify it, but not actually do the sort because we know it's
> > pointless? Or maybe that should be the behaviour of "forbidden", which
> > should then perhaps have a different name?
>
> I did think about that but I can't think of any reason we would want to
> silently ignore something the user has written. If the ordering doesn't
> make sense, we should forbid it.

It is allowed as of now, and so it would be a compatibility issue for queries
existing in the wild. Ignoring it is just an optimization, just how we
optimize away some joins entirely.

--
Ronan Dunklau