BUG #17329: Aggregate Functions Precision Error

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: Raw Message | Whole Thread | 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.

Responses

Browse pgsql-bugs by date

  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