RE: Incorrect rounding of double values at max precision

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

Perhaps, but the original path we took to find this was through JDBC, specifically the one from https://jdbc.postgresql.org/. Tracking the double (42258656681.38498) through the driver shows that it is converted from java double to float8 bytes, and is then stored as 42258656681.39. Which is definitely not expected, even if it can be explained by double rounding.

The half-up/half-down of the rounding may be a side issue, although also important.

A simple test like this will show the behaviour we see:

Connection conn = getConnection(); // some db connection
PreparedStatement pstmt = conn.prepareStatement("INSERT into mytable VALUES (?, ?)");
double x = 4.225865668138498E10;
int id = 123;
pstmt.setObject(1, id);
pstmt.setDouble(2, x);
pstmt.execute();

where mytable just has a decimal(13, 2) column and an integer id. When selected afterwards, we get the 42258656681.39 value instead of an expected value of 42258656681.38.

thanks

gilleain

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: 21 October 2019 19:43
To: Gilleain Torrance <Gilleain(dot)Torrance(at)alfasystems(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org; Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
Subject: Re: Incorrect rounding of double values at max precision

Gilleain Torrance <Gilleain(dot)Torrance(at)alfasystems(dot)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