pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gilleain Torrance <Gilleain.Torrance@alfasystems.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Incorrect rounding of double values at max precision
Date: Mon, 21 Oct 2019 15:42:37 -0400
Message-ID: <5045.1571686957@sss.pgh.pa.us> (raw)
In-Reply-To: <DB3D1D143DDA534BBB39DE713E92343701CBA89718@GBEDBP01.chp.co.uk>
References: <DB3D1D143DDA534BBB39DE713E92343701CBA89718@GBEDBP01.chp.co.uk>

Gilleain Torrance <Gilleain.Torrance@alfasystems.com> writes:
> When storing a double in Postgres, it looks like under specific circumstances it can get rounded incorrectly:

> select round(cast(float8 '42258656681.38498' as numeric), 2), round(numeric '42258656681.38498', 2);

> which returns either 42258656681.38 or 42258656681.39 depending on whether it is float8 or not.

I think this is behaving as expected.  float8-to-numeric conversion
rounds the float8 to 15 (DBL_DIG) decimal places, since that's as much
precision as you're guaranteed to have.  So what comes out of the cast
is

regression=# select cast(float8 '42258656681.38498' as numeric);
     numeric     
-----------------
 42258656681.385
(1 row)

and then that rounds up to 42258656681.39.  In the other case you
have an exact numeric value of 42258656681.38498, so it's unsurprisingly
rounded to 42258656681.38.

You could quibble about whether numeric round() ought to apply
round-up or round-to-nearest-even when dealing with exact halfway
cases.  If it did the latter, this particular case would match up,
but other cases would not, so I don't think it's a helpful proposal
for this issue.

The other thing we could conceivably do is ask sprintf for more digits.
But since those extra digit(s) aren't fully precise, I'm afraid that
would likewise introduce as many oddities as it fixes.  Still, it's
somewhat interesting to wonder whether applying the Ryu algorithm
would produce better or worse results on average.

			regards, tom lane





view thread (11+ messages)  latest in thread

Message-ID: <5045.1571686957@sss.pgh.pa.us>
Permalink:  ../5045.1571686957@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/5045.1571686957@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, Gilleain.Torrance@alfasystems.com, pgsql-bugs@lists.postgresql.org, andrew@tao11.riddles.org.uk
  Subject: Re: Incorrect rounding of double values at max precision
  In-Reply-To: <5045.1571686957@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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