Re: Window Functions with identical PARTITION BY and ORDER BY clauses evaluated separately

On Fri, Mar 7, 2025 at 5:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Christopher Inokuchi <cinokuchi(at)gmail(dot)com> writes:
> > Was it really not intentional that the docs explicitly name PARTITION BY
> > and ORDER BY rather than the entire window_definition? If I understand
> > correctly, only those two clauses control which records are hit and in
> what
> > order.
>
> Yeah, it's intentional, and in fact required by the SQL standard.
> However, you're misinterpreting what the guarantee is. The spec
> requirement is that window functions sharing PARTITION BY and
> ORDER BY all be evaluated on the same concrete ordering of the
> data, ie there can't be any re-sorting between them. And that's
> what we implement. We do use a separate WindowAgg node for
> each distinguishable window specification, but you'll notice
> there is not a Sort step between them unless the query involves
> entirely-incompatible PARTITION/ORDER BY specs.
>
> Perhaps the wording in section 7.2.5 could be improved; I agree
> that "evaluated in one pass" is capable of being read in more
> than one way, and it's not clear that it's referring to sorts.
> Do you have any suggestions for clearer wording?
>

We seem to do quite a few things that we don't tell the user about. The
attached patch describes those things and adds an example demonstrating
their effects via an explain; which is the only way you can construct an
example for this material.

Considered a draft pending feedback to either throw it out in favor of a
one-word/one-line fix or support for going into this amount of detail.

David J.