Thread: 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

>
>                        regards, tom lane
>