Re: numeric calculation bug as of 16.2-2 - Mailing list pgsql-bugs
From | Jan Wieck |
---|---|
Subject | Re: numeric calculation bug as of 16.2-2 |
Date | |
Msg-id | 7e9a5010-5f92-467f-82b6-c5f7e5b672cc@wi3ck.info Whole thread Raw |
In response to | Re: numeric calculation bug as of 16.2-2 (Huw Rogers <djnz00@gmail.com>) |
List | pgsql-bugs |
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 >
pgsql-bugs by date: