From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: neverov.max@gmail.com
Subject: BUG #17329: Aggregate Functions Precision Error
Date: Wed, 08 Dec 2021 21:02:56 +0000
Message-ID: <17329-8c3b204b1716bd24@postgresql.org> (raw)
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-TABL...)
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....
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, neverov.max@gmail.com
Subject: Re: BUG #17329: Aggregate Functions Precision Error
In-Reply-To: <17329-8c3b204b1716bd24@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox