Re: Underspecified window queries in regression tests

On Fri, Oct 14, 2011 at 9:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We could hack around this by adding more columns to the result so that
> an index-only scan doesn't work.  But I wonder whether it wouldn't be
> smarter to add ORDER BY clauses to the window function calls.  I've been
> known to argue against adding just-in-case ORDER BYs to the regression
> tests in the past; but these cases bother me more because a plan change
> will not just rearrange the result rows but change their contents,
> making it really difficult to verify that nothing's seriously wrong.

I'm not sure if it applies to this case but I recall I was recently
running queries on Oracle that included window functions and it
wouldn't even let me run them without ORDER BY clauses in the window
definition. I don't know if it cleverly determines that the ORDER BY
will change the results or if Oracle just requires ORDER BY on all
window definitions or what.

--
greg