Re: Underspecified window queries in regression tests

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