Re: Ordering behavior for aggregates

On 12/13/22 14:25, Isaac Morland wrote:
> On Tue, 13 Dec 2022 at 07:50, Vik Fearing <vik@postgresfriends.org> wrote:
> 
> 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.
>>
> 
> Although I find "r" attractive, I have two concerns about it:
> 
> 1) Do we really want to require ordering? I know it's weird and partially
> undefined to call something like string_agg without an ordering, but what
> if in the specific application it doesn’t matter in what order the items
> appear?
> 
> 2) There is a backward compatibility issue here; it’s not clear to me we
> could apply "r" to any existing aggregate.


I do not intend to add 'r' to any existing aggregate.  I included it in 
the hypothetical enum for future aggregates and extensions.  It isn't 
perfect either because first_value((x, y) ORDER BY x) can still give a 
semi-random result.


> Actually upon consideration, I think I have similar concerns about "f". We
> don’t usually forbid "dumb" things; e.g., I can write a function which
> ignores its inputs. And in some situations, "dumb" things make sense. For
> example, if I’m specifying a function to use as a filter, it could be
> reasonable in a particular instance to provide a function which ignores one
> or more of its inputs.


Sure, but this isn't a function; this is syntax.  And in your example 
you are ignoring the input whereas currently aggregates *do not* ignore 
the ordering when they could/should.
-- 
Vik Fearing