pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
4+ messages / 3 participants
[nested] [flat]
* BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
@ 2021-03-10 20:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2021-03-10 20:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: levi@leviaul.com
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.)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
@ 2021-03-10 22:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2021-03-10 22:00 UTC (permalink / raw)
To: levi@leviaul.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
@ 2021-03-11 02:22 Levi Aul <levi@leviaul.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Levi Aul @ 2021-03-11 02:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations
@ 2021-03-11 02:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Levi Aul <levi@leviaul.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2021-03-11 02:26 UTC (permalink / raw)
To: Levi Aul <levi@leviaul.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-03-11 02:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 20:41 BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations PG Bug reporting form <noreply@postgresql.org>
2021-03-10 22:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-11 02:22 ` Levi Aul <levi@leviaul.com>
2021-03-11 02:26 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox