PostgreSQL bug in window function on partition?

Question

I have a table t that has the following data:

    name    | n
------------+---
 school     | 4
 hotel      | 2
 restaurant | 6
 school     | 3
 school     | 5
 hotel      | 1

When I run the following query, the result is somewhat odd.

select name, n,
       first_value(n) over (partition by name order by n desc),
       last_value(n) over (partition by name order by  n)
from t;

    name    | n | first_value | last_value
------------+---+-------------+------------
 hotel      | 1 |           2 |          1
 hotel      | 2 |           2 |          2
 restaurant | 6 |           6 |          6
 school     | 3 |           5 |          3
 school     | 4 |           5 |          4
 school     | 5 |           5 |          5
(6 rows)

Although first_value works as I expected, last_value works strange. I think the values of last_value column should be the same as first_value's because the first_value is order by n descending.

Is this a PostgreSQL's bug or am I missed something?

The version of PostgreSQL is:

postgres=# select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.1 on x86_64-apple-darwin14.1.0, compiled by Apple LLVM version 6.0 (clang-600.0.56) (based on LLVM 3.5svn), 64-bit
(1 row)

Patrick · Accepted Answer · 2015-05-08 01:18:36Z

11

No, this is not a bug. The first_value() and last_value() functions work on the window frame, not the partition. The window frame, as per the documentation, defaults to the start of the partition to the current row if no frame_clause is specified. That is just what you need for first_value() but for last_value() you should add range between unbounded preceding and unbounded following to your WINDOW definition to look beyond the current row:

select name, n,
       first_value(n) over (partition by name order by n desc),
       last_value(n) over (partition by name order by n
         range between unbounded preceding and unbounded following)
from t;

Note also that this has nothing to do with ordering of the rows in the partition. The ordering produces the partition in a certain order (not surprisingly) and the frame-based functions then work on the window frame without knowing or caring about any ordering of the rows.

edited May 8, 2015 at 1:18

answered May 7, 2015 at 4:50

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adamantish Over a year ago

Thank you so much. This is one of the craziest bits of api design I've ever seen in a database. If I can use a MAX function which refers to the partition and then replace with another function I expect it to also work on the partition. That's just sanity. Of course they need to keep this silliness for backwards compatibility now but can we have some alternately named functions that do the expected?

Collectives™ on Stack Overflow

PostgreSQL bug in window function on partition?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related