From: | Bernd Hopp <berndjhopp(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | 'within group'- or percentile_cont-expression seems to have ramifications on table ordering |
Date: | 2021-06-20 15:42:09 |
Message-ID: | CAFa2YRwWtVG6d2291FHBc7+9F_-a9GJLGBF-+_eNw3F51MWZJw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-06-20 17:26:01 | Re: BUG #17065: pg_restore never returns even on simple situations (maybe I just don't understand something) |
Previous Message | PG Bug reporting form | 2021-06-19 11:28:45 | BUG #17065: pg_restore never returns even on simple situations (maybe I just don't understand something) |