Re: numeric calculation bug as of 16.2-2

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