Обсуждение: numeric calculation bug as of 16.2-2
Straightforward calculation bug. ((2^127)/(2^63))*(2^63) != (2^127). This prevents a reliable uint128<->numeric cast function (which is how I ran into it). See below for test case. -Huw
] psql test
psql (16.2)
Type "help" for help.
test=# select ('170141183460469231731687303715884105727'::numeric / '9223372036854775808'::numeric) * '9223372036854775808'::numeric;
?column?
-----------------------------------------
170141183460469231731687303715884105728
(1 row)
psql (16.2)
Type "help" for help.
test=# select ('170141183460469231731687303715884105727'::numeric / '9223372036854775808'::numeric) * '9223372036854775808'::numeric;
?column?
-----------------------------------------
170141183460469231731687303715884105728
(1 row)
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. There's a comment that explains the standard does not offer much
guidance on this. If you want higher precision then you should use a
typemod in the numeric cast to specify that.
The scale selection for the division is done in select_div_scale(). A
comment there says:
/*
* The result scale of a division isn't specified in any SQL standard. For
* PostgreSQL we select a result scale that will give at least
* NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
* result no less accurate than float8; but use a scale not less than
* either input's display scale.
*/
NUMERIC_MIN_SIG_DIGITS is 16.
That comment appears correct to me. It's no less accurate than float8.
Adding add a (pretty large) typemod shows a more accurate answer:
select ('170141183460469231731687303715884105727'::numeric(1000,900) /
'9223372036854775808'::numeric * '9223372036854775808'::numeric);
results in 170141183460469231731687303715884105727.
I'm not sure what we could do to make a better choice of scale. Making
it larger will make the code slower and unless we put the scale at the
limit of the numeric type, someone could still complain about wrong
answers.
David
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
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)
?column?
----------
f
(1 row)
On Tue, May 14, 2024 at 1:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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
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
>