BUG #5123: bug in window function "last_value"

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 |           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

And it's well-documented. See
http://www.postgresql.org/docs/8.4/static/functions-window.html

--
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