From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | neverov(dot)max(at)gmail(dot)com |
Subject: | BUG #17329: Aggregate Functions Precision Error |
Date: | 2021-12-08 21:02:56 |
Message-ID: | 17329-8c3b204b1716bd24@postgresql.org |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17329
Logged by: Max Neverov
Email address: neverov(dot)max(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-12-08 21:45:06 | Re: BUG #17329: Aggregate Functions Precision Error |
Previous Message | PG Bug reporting form | 2021-12-08 17:19:45 | BUG #17328: import foreign schema never reports errors |