Обсуждение: BUG #5123: bug in window function "last_value"
The following bug has been logged online: Bug reference: 5123 Logged by: Andrey Email address: andrey@ulab.ru PostgreSQL version: 8.4.1-x86_64 Operating system: RHEL5-x86_64 Description: bug in window function "last_value" Details: EXAMPLE: CREATE TABLE t ( id serial NOT NULL, CONSTRAINT pkey_t PRIMARY KEY (id) ) WITH (OIDS=FALSE); INSERT INTO t VALUES(default); INSERT INTO t VALUES(default); INSERT INTO t VALUES(default); select id, first_value(id) over(order by id), last_value(id) over(order by id) from t; RESULT: id | first_value | last_value ----+-------------+------------ 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 (3 rows) fist_value - good, last_value - bad
Andrey wrote: > select id, first_value(id) over(order by id), last_value(id) over(order by > id) from t; > > RESULT: > id | first_value | last_value > ----+-------------+------------ > 1 | 1 | 1 > 2 | 1 | 2 > 3 | 1 | 3 > (3 rows) > > fist_value - good, last_value - bad Looks ok to me. What did you expect? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Andrey wrote:
>> select id, first_value(id) over(order by id), last_value(id) over(order by
>> id) from t;
>>
>> RESULT:
>> id | first_value | last_value
>> ----+-------------+------------
>> 1 | 1 | 1
>> 2 | 1 | 2
>> 3 | 1 | 3
>> (3 rows)
>>
>> fist_value - good, last_value - bad
> Looks ok to me. What did you expect?
These *are* the correct answers, since the default window frame runs
from first row to current row. If you don't like them, you may need
to specify a different window frame.
regards, tom lane
2009/10/16 Tom Lane <tgl@sss.pgh.pa.us>: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: >> Andrey wrote: >>> select id, first_value(id) over(order by id), last_value(id) over(order= by >>> id) from t; >>> >>> RESULT: >>> id | first_value | last_value >>> ----+-------------+------------ >>> 1 | =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 =A0 =A0 =A01 >>> 2 | =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 =A0 =A0 =A02 >>> 3 | =A0 =A0 =A0 =A0 =A0 1 | =A0 =A0 =A0 =A0 =A03 >>> (3 rows) >>> >>> fist_value - good, last_value - bad > >> Looks ok to me. What did you expect? > > These *are* the correct answers, since the default window frame runs > from first row to current row. =A0If you don't like them, you may need > to specify a different window frame. > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane > And it's well-documented. See http://www.postgresql.org/docs/8.4/static/functions-window.html --=20 Hitoshi Harada
On Fri, Oct 16, 2009 at 04:45:55PM +0300, Heikki Linnakangas wrote: > Andrey wrote: > > select id, first_value(id) over(order by id), last_value(id) over(order by > > id) from t; > > > > RESULT: > > id | first_value | last_value > > ----+-------------+------------ > > 1 | 1 | 1 > > 2 | 1 | 2 > > 3 | 1 | 3 > > (3 rows) > > > > fist_value - good, last_value - bad > > Looks ok to me. What did you expect? I think what the OP was expecting was to have the last value be 3 instead of changing. This is at least a POLA violation. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> I think what the OP was expecting was to have the last value be 3
> instead of changing. This is at least a POLA violation.
[ shrug... ] It's what the spec requires, as far as anybody here
can tell. As Hitoshi-san already noted, we do point out in our
docs that last_value is not too useful unless you use a nondefault
window frame selection. I'm not sure what else we could do.
regards, tom lane
On Fri, Oct 16, 2009 at 01:28:57PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I think what the OP was expecting was to have the last value be 3 > > instead of changing. This is at least a POLA violation. > > [ shrug... ] It's what the spec requires, as far as anybody here > can tell. As Hitoshi-san already noted, we do point out in our docs > that last_value is not too useful unless you use a nondefault window > frame selection. I'm not sure what else we could do. Perhaps an illustration of the issue and a workaround in the docs? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate