Florian Pflug <fgp@phlo.org> writes:
> But some frame clauses (row 1 preceding, for example) have an effect despite
> there being no ORDER BY, like here:
Yeah, why did you expect differently? Without ORDER BY, all rows are
peers in the frame ordering, so there's no way for a RANGE spec to
select less than the whole partition. But with ROWS, you can select
less than that.
In general it's not that hard to create nondeterministic window-function
queries, since the SQL standard doesn't require you to specify a unique
ordering for the window function's input rows. Even in RANGE mode there
are plenty of functions that are sensitive to the exact ordering, eg
first_value/last_value. I guess the committee felt that locking this
down would restrict the feature too much.
regards, tom lane