From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Huw Rogers <djnz00@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: numeric calculation bug as of 16.2-2
In-reply-to: 
 <CAApHDvrX3nRaLXfDmbQzo_19KYeb_4qiGi5P2OX3L063xDk9NQ@mail.gmail.com>
References: 
 <CACu6R2qT6nTTWWQ+VKRzCqnZFXJg99Q_8nv59Jy48nrzovOCTw@mail.gmail.com>
 <CAApHDvrX3nRaLXfDmbQzo_19KYeb_4qiGi5P2OX3L063xDk9NQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Tue, 14 May 2024 15:30:34 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <915576.1715659935.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 14 May 2024 00:12:15 -0400
Message-ID: <915577.1715659935@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/915577.1715659935%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 14 May 2024 at 14:53, Huw Rogers <djnz00@gmail.com> wrote:
>> test=3D# 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:

=3D# select ((2^127)/(2^63))*(2^63) =3D (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:

=3D# select ((2^127::numeric)/(2^63::numeric))*(2^63::numeric) =3D (2^127:=
:numeric);
 ?column? =

----------
 t
(1 row)

=3D# select (2^127::numeric), (2^63::numeric);
                ?column?                 |      ?column?       =

-----------------------------------------+---------------------
 170141183460469231731687303715884105728 | 9223372036854775808
(1 row)

=3D# select (170141183460469231731687303715884105728/9223372036854775808)*=
9223372036854775808 =3D 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