Christopher Inokuchi <cinokuchi@gmail.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.
