Re: Incorrect rounding of double values at max precision

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 > 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.

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

Yes. This came up for discussion in the Ryu patch, but did not get much
input; I think some sort of case could be made for making the casts
exact, but the cast can't look at a config GUC without losing its
immutability, and changing the value could have an effect on functional
indexes. So I ended up not touching that at all.

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

Hmm.

The Ryu output values will still throw out edge cases similar to the
above; for example, 502.15::float8 / 10 = 50.214999999999996 whereas
502.15::numeric / 10 = 50.215, so rounding the result of that to 2
digits will give a different result.

Perhaps it would make more sense for the float8 to numeric cast to look
at the requested typmod and use that for the conversion? That way we
could make casts like fltval::numeric(20,2) or whatever produce the
correct result without any double-rounding issues. But the nature of
floating point means that this would still throw out occasionally
unexpected values (e.g. the 502.15::float8/10 example would still give
50.21 for a 2-digit result rather than 50.22).

(502.15::float8 is exactly
502.14999999999997726263245567679405212402343750)

I also did consider adding functions to convert a float8 value to the
_exact_ numeric that it represents. This is easy enough to write using
numeric arithmetic (I have SQL versions that I used extensively when
testing the Ryu code) but the performance isn't exceptionally good.
Might be good enough for many applications, though.

-- 
Andrew (irc:RhodiumToad)