Re: Incorrect rounding of double values at max precision - Mailing list pgsql-bugs

From Tom Lane
Subject Re: Incorrect rounding of double values at max precision
Date
Msg-id 5045.1571686957@sss.pgh.pa.us
Whole thread Raw
In response to Incorrect rounding of double values at max precision  (Gilleain Torrance <Gilleain.Torrance@alfasystems.com>)
Responses Re: Incorrect rounding of double values at max precision
RE: Incorrect rounding of double values at max precision
List pgsql-bugs
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



pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16071: Server crashes when 'numeric' data type is used on any plpython Function
Next
From: Andrew Gierth
Date:
Subject: Re: Incorrect rounding of double values at max precision