Re: BUG #7685: last_value() not consistent throughout window partition

Sorry for wasting your time, I now see: If frame_end is omitted it defaults
to CURRENT ROW.

clearly in the documentation. I made the assumption that the default was
UNBOUNDED PROCEDING/FOLLOWING which is now very obviously not the case.

Again, sorry for not really taking the time to ensure this was in fact a
bug.
-W

On Tue, Nov 20, 2012 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> wes(at)devauld(dot)ca writes:
> > -- The last_value(0 window function appears to be changing values
> > -- mid window. An example is detailed below.
>
> So far as I can see, these results are correct per spec. Note the
> caution in the manual about these window functions:
>
> Note that first_value, last_value, and nth_value consider only the rows
> within the "window frame", which by default contains the rows from the
> start of the partition through the last peer of the current row. This is
> likely to give unhelpful results for last_value and sometimes also
> nth_value. You can redefine the frame by adding a suitable frame
> specification (RANGE or ROWS) to the OVER clause. See Section 4.2.8 for
> more information about frame specifications.
>
> Since your d1 values are all distinct, the last peer is the same as the
> current row, so last_value(v) ends up just producing the current row's v.
> Which is what the query is producing.
>
> If you want the last row in the partition, you could use first_value
> with the opposite sort order, or use last_value with the same sort
> order and RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
> (The current SQL committee is nothing if not verbose :-()
>
> regards, tom lane
>