Re: Underspecified window queries in regression tests

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
> 2011/10/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> I can't recall whether there was some good reason for underspecifying
>> these test queries. It looks like all the problematic ones were added in
>> commit ec4be2ee6827b6bd85e0813c7a8993cfbb0e6fa7 "Extend the set of frame
>> options supported for window functions", which means it was either me or
>> Hitoshi-san who wrote them that way, but memory is not serving this
>> afternoon.

> I don't remember if I wrote that part or not, but I like to add
> explicit ORDER BY to the test cases. It doesn't appear that some
> reason stopped us to specify it. So +1 for adding the clauses.

I looked at this more closely and realized that the reason for doing it
like that was to test window frames defined using ROWS rather than
RANGE. If we fully specify the window function's input ordering then
there's no very interesting distinction between the two, because no rows
will have any peers. So adding ORDER BY would in fact reduce the scope
of the tests.

At this point I'm inclined to leave it alone. Maybe we could think of
some other test cases (perhaps using some other function than SUM) which
would both exercise the difference between RANGE and ROWS mode, and not
be sensitive to the detailed input ordering. But I doubt it's really
worth the trouble.

regards, tom lane