Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY

2008/11/10 David Rowley <dgrowley(at)gmail(dot)com>:
> 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?

I don't see any reason to take it as a bug. It may be confusing some
people but it is consistent enough and not ambiguous. Many users
already know if they don't specify ORDER BY clause in a simple regular
query they wouldn't receive ordered rows so it will match their
senses.

Regards,

--
Hitoshi Harada