window functions maybe bug
Hello,
I wrote article about statistical function - when I tested Joe Celko's
method, I found some problems on not unique dataset:
on distinct dataset is rule so rows here is max(hi), then there is min(lo):
create table x1 (a integer);
insert into x1 select generate_series(1,10);
postgres=# select row_number() over (order by a), row_number() over
(order by a desc) from x1;
row_number | row_number
------------+------------
10 | 1
9 | 2
8 | 3
7 | 4
6 | 5
5 | 6
4 | 7
3 | 8
2 | 9
1 | 10
(10 rows)
but on other set I got
truncate table x1;
insert into x1 values(2),(2),(3),(3),(4),(4),(5),(5),(6),(6),(6),(8),(9),(9),(10),(10);
postgres=# select row_number() over (order by a), row_number() over
(order by a desc) from x1;
row_number | row_number
------------+------------
16 | 1
15 | 2
14 | 3
11 | 4
13 | 5
12 | 6
9 | 7
10 | 8
7 | 9
8 | 10
5 | 11
6 | 12
4 | 13
3 | 14
1 | 15
2 | 16
(16 rows)
I am not sure, is this correct? When this solution is correct, then
Joe Celko's method for median calculation is buggy.
Regards
Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
create table x1 (a integer);
insert into x1 values(2),(2),(3),(3),(4),(4),(5),(5),(6),(6),(6),(8),(9),(9),(10),(10);
postgres=# select row_number() over (order by a), row_number() over
(order by a desc) from x1;
row_number | row_number
------------+------------
16 | 1
15 | 2
14 | 3
11 | 4
13 | 5
12 | 6
9 | 7
10 | 8
7 | 9
8 | 10
5 | 11
6 | 12
4 | 13
3 | 14
1 | 15
2 | 16
(16 rows)
I am not sure, is this correct?
I don't see any grounds for arguing that it's wrong. The results for
rows with equal "a" values are indeterminate.
regards, tom lane
2009/9/2 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
create table x1 (a integer);
insert into x1 values(2),(2),(3),(3),(4),(4),(5),(5),(6),(6),(6),(8),(9),(9),(10),(10);postgres=# select row_number() over (order by a), row_number() over
(order by a desc) from x1;
row_number | row_number
------------+------------
16 | 1
15 | 2
14 | 3
11 | 4
13 | 5
12 | 6
9 | 7
10 | 8
7 | 9
8 | 10
5 | 11
6 | 12
4 | 13
3 | 14
1 | 15
2 | 16
(16 rows)I am not sure, is this correct?
I don't see any grounds for arguing that it's wrong. The results for
rows with equal "a" values are indeterminate.
I can understand it. So I found Joe Celko's bug :)
regards
Pavel Stehule
Show quoted text
regards, tom lane