Re: numeric calculation bug as of 16.2-2

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)

On Tue, May 14, 2024 at 1:12 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Tue, 14 May 2024 at 14:53, Huw Rogers <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
>