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@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     David Rowley <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com>>
>     writes:
>      > On Tue, 14 May 2024 at 14:53, Huw Rogers <djnz00@gmail.com
>     <mailto: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
>