{Not A Bug} RE: BUG #7685: last_value() not consistent throughout window partition

> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-
> owner@postgresql.org] On Behalf Of wes@devauld.ca
> Sent: Tuesday, November 20, 2012 10:28 AM
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] BUG #7685: last_value() not consistent throughout =
window
> partition
>=20
> The following bug has been logged on the website:
>=20
> Bug reference:      7685
> Logged by:          Wes Devauld
> Email address:      wes@devauld.ca
> PostgreSQL version: 9.2.1
> Operating system:   Windows 7 Enterprise
> Description:
>=20
> -- The last_value(0 window function appears to be changing values
> -- mid window.  An example is detailed below.
>=20
> ----SELECT version();
> --                           version
> -------------------------------------------------------------
> -- PostgreSQL 9.1.6, compiled by Visual C++ build 1500, 32-bit
>=20
> -------------------------
>=20
> -- **** DB DUMP ****
> --
> -- PostgreSQL database dump
> --
>=20
> -- Dumped from database version 9.1.4
> -- Dumped by pg_dump version 9.1.4
> -- Started on 2012-11-19 15:01:41
>=20
> SET statement_timeout =3D 0;
> SET client_encoding =3D 'UTF8';
> SET standard_conforming_strings =3D on;
> SET check_function_bodies =3D false;
> SET client_min_messages =3D warning;
>=20
> SET search_path =3D public, pg_catalog;
>=20
> SET default_tablespace =3D '';
>=20
> SET default_with_oids =3D false;
>=20
> --
> -- TOC entry 170 (class 1259 OID 25186)
> -- Dependencies: 5
> -- Name: bug_report; Type: TABLE; Schema: public; Owner: postgres;
> Tablespace:
> --
>=20
> CREATE TABLE bug_report (
>     d1 date NOT NULL,
>     d2 date NOT NULL,
>     v real NOT NULL
> );
>=20
>=20
> ALTER TABLE public.bug_report OWNER TO postgres;
>=20
> --
> -- TOC entry 1880 (class 0 OID 25186)
> -- Dependencies: 170
> -- Data for Name: bug_report; Type: TABLE DATA; Schema: public; Owner:
> postgres
> --
>=20
> 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
> \.
>=20
>=20
> -- Completed on 2012-11-19 15:01:42
>=20
> --**** Description ****
>=20
> 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
>=20
> --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
>=20
> --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.
>=20
>=20
> --Thanks, and good work with the product.
>=20

Not A Bug.

When you add an "Order By" the resultant window FRAME is from the first =
record (of the partition) up-to-and-including the current record (of the =
partition); no "future" records (in the partition) are part of the =
window FRAME.  Thus for "last_value" you are always implicitly referring =
to the current row (of the FRAME).  This is defined and documented =
behavior.  In order to use "last_value" you need to override the FRAME =
that the window operates on to include all rows.

http://www.postgresql.org/docs/9.2/interactive/functions-window.html - =
read the second paragraph following the table and explore from there

David J.