Re: Frames vs partitions: is SQL2008 completely insane?

"David Rowley" <dgrowley(at)gmail(dot)com> writes:
> Hitoshi Harada wrote:
>> I tested on Oracle 10.2.0, and the results are:
>> ...
>> which means the section 4.15 is true. Could anyone try DB2?

> DB2 9.5 results [ are the same ]

OK, good, that means the reference to the frame in 6.10 rule 1b is just
a copy-and-pasteo. (I wonder if it got fixed in the final spec?)
I guess this makes sense because we also see that 6.10 syntax rule 6b
forbids a framing spec on lead/lag, which makes sense if these functions
ignore the frame and no sense otherwise.

I also realized after more thought that the way to get frame = partition
with a default frame spec is to omit any ORDER BY in the window spec.
So my concern about LAST_VALUE being useless without framing ability is
unfounded, and I withdraw the complaint that we need to put in some
minimal framing features. But we'll have to be careful to document all
this properly. Also, it does seem that it might be worthwhile to try to
ensure that combinations of windows that have the same PARTITION list
and empty vs nonempty ORDER BY get optimized well. Right now it's
dependent on ordering of the WindowClause whether you pay an extra sort
for that case or not.

regards, tom lane