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