The following bug has been logged on the website:
Bug reference: 17329
Logged by: Max Neverov
Email address: neverov.max@gmail.com
PostgreSQL version: 13.3
Operating system: Alpine 10.3.1_git20210424
Description:
Aggregate functions (described here
https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE)
that are defined for double precision type suffer from loss of
significance.
Corresponding code see
https://github.com/postgres/postgres/blob/49407dc32a2931550e4ff1dea314b6a25afdfc35/src/backend/utils/adt/float.c#L3401.
Consider the following:
drop table if exists test;
create table test(y numeric, x numeric);
insert into test values
(1, 3),
(2, 3.0e+22),
(3, -3);
select covar_pop(y, x) from test;
covar_pop
-------------------
699050.6666666666
(1 row)
truncate table test;
insert into test values
(1, 3),
(3, -3),
(2, 3.0e+22);
select covar_pop(y, x) from test;
covar_pop
-----------
-2
(1 row)
truncate table test;
insert into test values
(2, 3.0e+22),
(3, -3),
(1, 3);
select covar_pop(y, x) from test;
covar_pop
--------------------
-699050.6666666666
(1 row)
The expected result is -2.
The result depends on the order of values although it shouldn't. This
happens because operations with 3.0e+22 lead to the loss of precision since
the type can hold only 15 decimal digits precision.
Even if the functions defined for double precision type I would expect
Postgres either to report an error or to return the correct result.