Re: How do query optimizers affect window functions

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Wed, Nov 14, 2012 at 2:16 PM, Tianyin Xu <tixu(at)cs(dot)ucsd(dot)edu> wrote:
>> What do you mean by "refused to run"?

> I mean that it could throw an error. Kind of like the way this
> currently throws an error:

> select b, sum(b) from foo;
> ERROR: column "foo.b" must appear in the GROUP BY clause or be used
> in an aggregate function.

> To be clear, I am not saying that it does do this (clearly it does
> not), just that my intuition is that it should do this.

The SQL standard says that underspecified window ordering gives you
implementation-dependent results, but not an error. (Their use of
"implementation-dependent" basically means "unspecified".)

I think this is a fairly reasonable definition, since in many practical
cases it would be hard for the parser to tell whether the window
ordering was nailed down sufficiently to give a unique result, anyway.
(Even if we required you to give an ORDER BY for each column, there are
examples such as zero/minus-zero in float8 where that doesn't produce a
unique ordering. And such a requirement would just be a pain in the
rear a lot of the time.)

It's also consistent with what you get if, for example, you use LIMIT
without an ORDER BY or with an ORDER BY that doesn't constrain the
results to a unique row ordering.

In practice it's on the user to be sure he's nailed down the row
ordering sufficiently to get the results he wants in these cases.

regards, tom lane