Re: numeric calculation bug as of 16.2-2
| От | Huw Rogers |
|---|---|
| Тема | Re: numeric calculation bug as of 16.2-2 |
| Дата | |
| Msg-id | CACu6R2qXrCkNtdW8B9yzC=+gxo3hy5jatf1qaaQaLNaktAgA=Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: numeric calculation bug as of 16.2-2 (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: numeric calculation bug as of 16.2-2
(Jan Wieck <jan@wi3ck.info>)
|
| Список | pgsql-bugs |
Thanks for the reply; as you found, actually I was testing with (2^127-1), not 2^127, and apparently that makes the difference. (((2^127)-1)/(2^63))
The reason I think it's a bug is that I would not expect an off-by-one result. I would expect some fractional error of much less than one. It's also suspicious that this is triggered by an all-binary-ones value.
For now I'm just using a WITH INOUT cast for this, which works fine, although it would be easier for my purposes (adding int128 and uint128 types via an extension) to expose numericvar_to_int128() and int128_to_numericvar via numeric.h so that extensions could use them.
This would be the corrected test:
=# select ((2^127::numeric - 1::numeric)/(2^63::numeric))*(2^63::numeric) = (2^127::numeric - 1::numeric);
?column?
----------
f
(1 row)
?column?
----------
f
(1 row)
On Tue, May 14, 2024 at 1:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 14 May 2024 at 14:53, Huw Rogers <djnz00@gmail.com> wrote:
>> test=# select ('170141183460469231731687303715884105727'::numeric / '9223372036854775808'::numeric) * '9223372036854775808'::numeric;
>> ?column?
>> -----------------------------------------
>> 170141183460469231731687303715884105728
> I don't have enough experience in NUMERIC to tell if this is a bug or
> not.
It is not. If you think that using numeric (or any other
general-purpose arithmetic code) means you'll always get exact answers
for every calculation, I have a bridge in Brooklyn I'd like to sell
you.
The specific problem with the example you give is that you're using
fractional-power-of-2 numbers and expecting them to be exactly
representable in numeric's base-10 arithmetic. That's not happening.
Amusingly, type float8 (which is binary at bottom) can represent
such numbers exactly, so that this works:
=# select ((2^127)/(2^63))*(2^63) = (2^127);
?column?
----------
t
(Use pg_typeof to verify that the subexpressions are type float8.)
Nonetheless, float8 has a well-deserved reputation for being imprecise
with the decimal fractions that people commonly work with. That's
just the opposite side of the same coin: conversion between the two
bases is inexact, unless you are willing to work with an unlimited
number of fractional digits, which in practice nobody is.
BTW, just as a point of order, I cannot reproduce your complaint:
=# select ((2^127::numeric)/(2^63::numeric))*(2^63::numeric) = (2^127::numeric);
?column?
----------
t
(1 row)
=# select (2^127::numeric), (2^63::numeric);
?column? | ?column?
-----------------------------------------+---------------------
170141183460469231731687303715884105728 | 9223372036854775808
(1 row)
=# select (170141183460469231731687303715884105728/9223372036854775808)*9223372036854775808 = 170141183460469231731687303715884105728;
?column?
----------
t
(1 row)
I don't know where you got '170141183460469231731687303715884105727'
from, but that seems off-by-one. This doesn't invalidate my larger
point though.
regards, tom lane
В списке pgsql-bugs по дате отправления:
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #18462: Wrong SELinux types of the binary files