Re: Window functions: frame-adhering aggregate without ORDER BY clause

Alright, this makes sense. Thank you for the quick response!

Best regards,
Romain Carl

On 26.06.23 15:54, Tom Lane wrote:
> Romain Carl <romaincarl(at)aol(dot)com> writes:
>> among the window tests (src/test/regress/expected/window.out), I noticed
>> the presence of tests that rely upon the order of rows not determined by
>> any ORDER BY clause, such as:
> Yeah ...
>
>> The current row's frame and, consequently, the result of the sum
>> aggregate depend on the order produced by the sequential scan of table
>> tenk1. Since such order is, in general, not part of PG's defined
>> behavior, what purpose do the tests that rely upon it serve?
> The tests are perfectly entitled to test PG's actual behavior.
> I don't see much difference between this particular case and the
> fact that we have any tests at all that lack ORDER BY, because
> formally speaking the engine could choose to emit the rows in
> some other order. In practice, if we ever did make the engine
> behave differently, it'd be on us to fix affected test cases.
>
>> Following up to that, how is an EXCLUDE GROUP defined to behave in
>> absence of any ORDER BY clause?
> I see in the docs
>
> <literal>EXCLUDE GROUP</literal> excludes the current row and its
> ordering peers from the frame.
>
> and a bit later
>
> Without <literal>ORDER BY</literal>,
> ... all rows become peers of the current row.
>
> so excluding the whole frame seems like the right behavior.
>
> regards, tom lane