BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
The following bug has been logged on the website:
Bug reference: 16919
Logged by: Levi Aul
Email address: levi@leviaul.com
PostgreSQL version: 13.2
Operating system: Ubuntu 20.04.2 (x86_64), Linux kernel 5.4.0
Description:
Simple operations on NUMERIC, supposedly an arbitrary-precision type, are
having unexpected results for me:
SELECT '12345678901234567890123456789012345678901234567890'::numeric;
-- expected 12345678901234567890123456789012345678901234567890
-- got 12345678901234567890123456789012345678900000000000
SELECT (('123456789012345678901234567890'::numeric) *
('100000000000000000000'::numeric)) + ('12345678901234567890'::numeric);
-- expected 12345678901234567890123456789012345678901234567890
-- got 12345678901234567890123456789012345678900000000000
Correct results can be attained by replacing NUMERIC in the above with
pgmp's mpz type.
Also, this precision loss doesn't occur with the internal _numeric
array-member type:
SELECT
'{12345678901234567890123456789012345678901234567890}'::numeric[];
-- expected {12345678901234567890123456789012345678901234567890}
-- got {12345678901234567890123456789012345678901234567890}
I've verified with debug_print_parse that nothing strange is going on with
implicit casts overriding the defaults; the native function numeric_in is
being called here. (And even if it weren't, having an overridden (text TO
numeric) cast wouldn't also be affecting the math-op result above, as it
operates upon apparently-lossless inputs while still achieving a lossy
result.)
PG Bug reporting form <noreply@postgresql.org> writes:
Simple operations on NUMERIC, supposedly an arbitrary-precision type, are
having unexpected results for me:
SELECT '12345678901234567890123456789012345678901234567890'::numeric;
-- expected 12345678901234567890123456789012345678901234567890
-- got 12345678901234567890123456789012345678900000000000
[ raised eyebrow... ] Works for me:
regression=# SELECT '12345678901234567890123456789012345678901234567890'::numeric;
numeric
----------------------------------------------------
12345678901234567890123456789012345678901234567890
(1 row)
I'm wondering which compiler and what build options you used.
Also, your reference to mpz makes me doubt that this is a stock
version of Postgres, so maybe you just have a home-grown bug.
(There are some arbitrary decisions about precision of the results
of numeric division, as well as the transcendental functions.
But plain addition and multiplication should be exact, and of course
just reading out a literal certainly should be.)
regards, tom lane
I was banging my head against a wall for a while trying to debug this,
because this *is* a stock Postgres (PGDG APT Postgres 13.2), as are all the
installed extensions (also from PGDG APT.) Also, I couldn't replicate the
problem even on the same machine in a secondary cluster.
But then, as a sanity check, I tried the same query in psql on the
*original* cluster, and it was fine...
It turns out it was actually the *client* I was originally using for
testing my queries (Postico) that was losing precision! The query was fine!
I could have figured this out sooner if I had just done a simple round-trip
test:
SELECT '1234567890123456789012345678901234567890'::numeric::text;
That, of course, gives the right result, even in Postico — demonstrating
that it was never NUMERIC's fault.
Time for a bug report to Postico...
On Wed, Mar 10, 2021 at 2:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
PG Bug reporting form <noreply@postgresql.org> writes:
Simple operations on NUMERIC, supposedly an arbitrary-precision type, are
having unexpected results for me:SELECT '12345678901234567890123456789012345678901234567890'::numeric;
-- expected 12345678901234567890123456789012345678901234567890
-- got 12345678901234567890123456789012345678900000000000[ raised eyebrow... ] Works for me:
regression=# SELECT
'12345678901234567890123456789012345678901234567890'::numeric;
numeric
----------------------------------------------------
12345678901234567890123456789012345678901234567890
(1 row)I'm wondering which compiler and what build options you used.
Also, your reference to mpz makes me doubt that this is a stock
version of Postgres, so maybe you just have a home-grown bug.(There are some arbitrary decisions about precision of the results
of numeric division, as well as the transcendental functions.
But plain addition and multiplication should be exact, and of course
just reading out a literal certainly should be.)regards, tom lane
Levi Aul <levi@leviaul.com> writes:
It turns out it was actually the *client* I was originally using for
testing my queries (Postico) that was losing precision! The query was fine!
I did think of that possibility, but rejected it because you indicated
you'd done some server-side tracing to confirm what you were seeing.
Oh well.
regards, tom lane