From: | wes(at)devauld(dot)ca |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7685: last_value() not consistent throughout window partition |
Date: | 2012-11-20 15:27:44 |
Message-ID: | E1Tapjk-00074I-EU@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-11-20 17:33:45 | Re: BUG #7684: pg_dumpall doesn't sort "CREATE SERVER"s options correctly |
Previous Message | timur.luchkin | 2012-11-20 15:22:16 | BUG #7684: pg_dumpall doesn't sort "CREATE SERVER"s options correctly |