BUG #17329: Aggregate Functions Precision Error
От | PG Bug reporting form |
---|---|
Тема | BUG #17329: Aggregate Functions Precision Error |
Дата | |
Msg-id | 17329-8c3b204b1716bd24@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17329: Aggregate Functions Precision Error
|
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: