'within group'- or percentile_cont-expression seems to have ramifications on table ordering

here's how to reproduce. create a test table:

create table test (
id integer not null primary key,
value integer not null,
value_percentile integer not null default 0
);

insert random values into the 'value' column

do $$
begin
for r in 0..100 loop
insert into test(id, value) values (r, random() * 100);
end loop;
end;
$$;

compute the percentiles for those values and write them to the percentile-column

do $$
declare
_value integer;
begin
for r in 0..100 loop
select into _value percentile_cont(r::float / 100)
within group (order by test.value) from test;
raise notice '%: %',r::float / 100, _value;

update test set value_percentile = r where value = _value;
end loop;
end
$$;

inspect the table

select * from test;

you can see that the rows are not in order of insertion any more, but
in descending order of value. That may not violate the specification,
however I find it

to be counterintuitive that a non-updating query would have such side effects.

[image: image.png]

best

Bernd