BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: levi(at)leviaul(dot)com
Subject: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
Date: 2021-03-10 20:41:32
Message-ID: 16919-64fff49d97341054@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16919
Logged by: Levi Aul
Email address: levi(at)leviaul(dot)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.)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jason Ayre 2021-03-10 21:04:21 12.4 -> 12.5 upgrade, broken CTE query
Previous Message Oh, Mike 2021-03-10 20:29:18 Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns