PERCENT_RANK may return NaN instead of 0

[mrotteveel]

PERCENT_RANK can return NaN instead of 0e0 (0) in some cases:

(example obtained from a Hibernate test):

create table EntityOfBasics (
    id integer not null,
    the_int integer,
    primary key (id)
);

With setup code

insert into EntityOfBasics (the_int, id) values (5, 1);
insert into EntityOfBasics (the_int, id) values (6, 2);
insert into EntityOfBasics (the_int, id) values (7, 3);
insert into EntityOfBasics (the_int, id) values (13, 4);
insert into EntityOfBasics (the_int, id) values (5, 5);

The query

select
    id,
    the_int,
    row_number() over(partition by eob1_0.the_int order by eob1_0.id),
    percent_rank() over(partition by eob1_0.the_int order by eob1_0.id),
    cume_dist() over(partition by eob1_0.the_int order by eob1_0.id) 
from EntityOfBasics eob1_0 
order by 1;

produces

ID	THE_INT	ROW_NUMBER	PERCENT_RANK	CUME_DIST
1	5	1	0.0	0.5
2	6	1	NaN	1.0
3	7	1	NaN	1.0
4	13	1	NaN	1.0
5	5	2	1.0	1.0

Those NaN values should all be zero.

See also ISO/IEC 9075-2:2023 9.23 Evaluation and transformation of <window function>, specifically syntax rule 5g

[mrotteveel]

In other words, if the partition has one row, the PERCENT_RANK should be 0, not NaN.