Windowing Function Patch Review -> ROW_NUMBER without ORDER BY

Поиск
Список
Период
Сортировка
От David Rowley
Тема Windowing Function Patch Review -> ROW_NUMBER without ORDER BY
Дата
Msg-id CA36904CC62E4C3BB37C177425A22D02@amd64
обсуждение исходный текст
Ответ на Re: Windowing Function Patch Review -> Standard Conformance  ("Hitoshi Harada" <umi.tanuki@gmail.com>)
Ответы Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY
Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY
Список 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.





В списке pgsql-hackers по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: Block-level CRC checks
Следующее
От: Mark Kirkwood
Дата:
Сообщение: Re: Hot standby v5 patch assertion failure