Re: Underspecified window queries in regression tests

2011/10/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.

Ah, you mentioned about ORDER BY in window specification (OVER
clause). I thought it was query's ORDER BY. Yes, it affects in RANGE
case, and we don't have rich frame support of RANGE (like n PRECEDING
...) so the case ORDER BY affects result is limited. Agree with
leaving it alone.

Regards,
--
Hitoshi Harada