Windowing Function Patch Review -> ROW_NUMBER without ORDER BY

From: "David Rowley" <dgrowley(at)gmail(dot)com>
To: "'Hitoshi Harada'" <umi(dot)tanuki(at)gmail(dot)com>, <pgsql-hackers(at)postgresql(dot)org>
Cc: <heikki(dot)linnakangas(at)enterprisedb(dot)com>, "'Vladimir Sitnikov'" <sitnikov(dot)vladimir(at)gmail(dot)com>
Subject: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY
Date: 2008-11-09 21:35:01
Message-ID: CA36904CC62E4C3BB37C177425A22D02@amd64
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I've been trying to think of a use case for using ROW_NUMBER() with no ORDER
BY in the window clause.

Using the example table I always seem to be using, for those who missed it
in other threads.

create table employees (
id INT primary key,
name varchar(30) not null,
department varchar(30) not null,
salary int not null,
check (salary >= 0)
);

insert into employees values(1,'Jeff','IT',10000);
insert into employees values(2,'Sam','IT',12000);
insert into employees values(3,'Richard','Manager',30000);
insert into employees values(4,'Ian','Manager',20000);
insert into employees values(5,'John','IT',60000);
insert into employees values(6,'Matthew','Director',60000);

david=# select *,row_number() over () from employees;
id | name | department | salary | row_number
----+---------+------------+--------+------------
1 | Jeff | IT | 10000 | 1
2 | Sam | IT | 12000 | 2
4 | Ian | Manager | 20000 | 3
5 | John | IT | 60000 | 4
6 | Matthew | Director | 60000 | 5
3 | Richard | Manager | 30000 | 6
(6 rows)

row_number seems to assign the rows a number in order of how it reads them
from the heap. Just to confirm...

update employees set salary = salary where id = 3;

david=# select *,row_number() over () from employees;
id | name | department | salary | row_number
----+---------+------------+--------+------------
1 | Jeff | IT | 10000 | 1
2 | Sam | IT | 12000 | 2
4 | Ian | Manager | 20000 | 3
5 | John | IT | 60000 | 4
6 | Matthew | Director | 60000 | 5
3 | Richard | Manager | 30000 | 6
(6 rows)

The spec says: "The ROW_NUMBER function computes the sequential row number,
starting with 1 (one) for the first row, of the row within its window
partition according to the window ordering of the window."

I'm just not sure if we should block this or not.

Does anyone see this as a feature?

Does anyone see this as a bug?

Any feedback is welcome

David.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Kirkwood 2008-11-09 22:07:57 Re: Hot standby v5 patch assertion failure
Previous Message Nikolay Samokhvalov 2008-11-09 21:14:34 Re: again...