From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Huw Rogers <djnz00@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: numeric calculation bug as of 16.2-2
Date: Tue, 14 May 2024 00:12:15 -0400
Message-ID: <915577.1715659935@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvrX3nRaLXfDmbQzo_19KYeb_4qiGi5P2OX3L063xDk9NQ@mail.gmail.com>
References: <CACu6R2qT6nTTWWQ+VKRzCqnZFXJg99Q_8nv59Jy48nrzovOCTw@mail.gmail.com>
<CAApHDvrX3nRaLXfDmbQzo_19KYeb_4qiGi5P2OX3L063xDk9NQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, djnz00@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: numeric calculation bug as of 16.2-2
In-Reply-To: <915577.1715659935@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox