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

The following bug has been logged on the website:

Bug reference: 7685
Logged by: Wes Devauld
Email address: wes(at)devauld(dot)ca
PostgreSQL version: 9.2.1
Operating system: Windows 7 Enterprise
Description:

-- The last_value(0 window function appears to be changing values
-- mid window. An example is detailed below.

----SELECT version();
-- version
-------------------------------------------------------------
-- PostgreSQL 9.1.6, compiled by Visual C++ build 1500, 32-bit

-------------------------

-- **** DB DUMP ****
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.1.4
-- Dumped by pg_dump version 9.1.4
-- Started on 2012-11-19 15:01:41

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 170 (class 1259 OID 25186)
-- Dependencies: 5
-- Name: bug_report; Type: TABLE; Schema: public; Owner: postgres;
Tablespace:
--

CREATE TABLE bug_report (
d1 date NOT NULL,
d2 date NOT NULL,
v real NOT NULL
);

ALTER TABLE public.bug_report OWNER TO postgres;

--
-- TOC entry 1880 (class 0 OID 25186)
-- Dependencies: 170
-- Data for Name: bug_report; Type: TABLE DATA; Schema: public; Owner:
postgres
--

COPY bug_report (d1, d2, v) FROM stdin;
2012-11-19 2012-11-07 358363
2012-11-18 2012-11-07 358363
2012-11-17 2012-11-07 358363
2012-11-16 2012-11-07 257572
2012-11-15 2012-11-07 257572
2012-11-14 2012-11-07 257572
2012-11-13 2012-11-07 257572
2012-11-12 2012-11-07 257572
2012-11-11 2012-11-07 257572
2012-11-10 2012-11-07 257572
\.

-- Completed on 2012-11-19 15:01:42

--**** Description ****

select d2, last_value(v) over (partition by d2 order by d1),
first_value(v) over (partition by d2 order by d1 desc)
from bug_report

--Returns:
--"2012-11-07";358363;358363
--"2012-11-07";358363;358363
--"2012-11-07";358363;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363
--"2012-11-07";257572;358363

--You can see that the last_value()
--switches in the middle of the
--partition for the window function
--when both columns should be
--identical since the first/last
--and asc/desc changes should
--cancel each other out.

--Thanks, and good work with the product.

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

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
>