Re: numeric calculation bug as of 16.2-2

On 5/14/24 01:04, Huw Rogers wrote:
> 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)

Your assumption that this could ever result in 'true' with every input
is still wrong as it is based on a hypothetical infinite precision.
NUMERIC never was designed for infinite precision, just arbitrary and
you didn't even specify a desired precision.

bc(1)'s output for example is

scale=100
(2^127-1)/(2^63)
18446744073709551615.9999999999999999998915797827514495565992547199130058288574218750000000000000000000000000000000000000

You have to force PostgreSQL to use that same number of digits by
invoking round():

db1=# select round((2^127::numeric - 1), 100) / (2^63::numeric);
?column?

---------------------------------------------------------------------------------------------------------------------------
18446744073709551615.9999999999999999998915797827514495565992547199130058288574218750000000000000000000000000000000000000

This has nothing to do with being on powers of two. You just noticed it
happening on those numbers. It could happen on any recurring decimal.
For example:

db1=# select (1::numeric) / (3::numeric) * (3::numeric);
?column?
------------------------
0.99999999999999999999

So your "workaround" would be something like

db1=# select round((2^127::numeric - 1), 100) / (2^63::numeric) *
(2^63::numeric);

?column?

------------------------------------------------------------------------------------------------------------------------------------
----------
170141183460469231731687303715884105727.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000

But again, it will fail on recurring decimals or even a simple construct
like sqrt(2) because no amount of digits will get those cases precise.
It only works in your particular example because (2^127-1)/(2^63) has a
finite number of decimals that is reasonable to compute to the end.

Regards, Jan

>
>
>
> On Tue, May 14, 2024 at 1:12 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com>>
> writes:
> > On Tue, 14 May 2024 at 14:53, Huw Rogers <djnz00(at)gmail(dot)com
> <mailto:djnz00(at)gmail(dot)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
>