Re: Frames vs partitions: is SQL2008 completely insane?

"Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com> writes:
> 2008/12/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> I notice that the current patch code seems to implement
>> first/last/nth_value using the frame, but lead/lag using the partition,
>> which doesn't conform to spec AFAICS ...

> In 4.15, it says:

> The lead and lag functions each take three arguments, a <value
> expression> VE, an <exact numeric literal>
> OFFSET, and a <value expression> DEFAULT. For each row R within the
> *window partition P of R* defined by
> a window structure descriptor, the lag function returns the value of
> VE evaluated on a row that is OFFSET
> number of rows before R within P,

Well, that's interesting, but I think the controlling definition is in
6.10 general rule 1b, which very clearly states that the frame is to be
used for lead/lag (and the adjacent rules say the same for all the other
standard window functions).

The wording in 4.15 does seem like evidence that the spec authors may
have misspoke in 6.10, but we're never going to settle it from the text
of the spec. Can anyone check what DB2 and Oracle do here?

In any case, both sections agree that last_value works on the frame,
which makes it effectively useless with the default frame definition.
So I'm still thinking that we need at least a subset of frame support.
I'm tempted to propose that we just handle the CURRENT ROW and UNBOUNDED
options for each end of the frame.

regards, tom lane