pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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>
  2021-03-10 22:00 ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations Levi Aul <levi@leviaul.com>
  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-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 ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-11 02:22   ` Levi Aul <levi@leviaul.com>
  2021-03-11 02:26     ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations 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-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 ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations Tom Lane <tgl@sss.pgh.pa.us>
  2021-03-11 02:22   ` Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations Levi Aul <levi@leviaul.com>
@ 2021-03-11 02:26     ` Tom Lane <tgl@sss.pgh.pa.us>
  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