Re: Incorrect rounding of double values at max precision

>>>>> "Gilleain" == Gilleain Torrance <Gilleain(dot)Torrance(at)alfasystems(dot)com> writes:

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

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

Gilleain> where mytable just has a decimal(13, 2) column and an integer
Gilleain> id.

I do not see any way to make that code work as you apparently expect in
all cases. Here is a simple counterexample: imagine setting x to 502.215
instead. Currently, that will insert a value of 502.22 into the table,
as you would expect. If we fixed your example above by doing the
conversion with maximum precision, then 502.215 would instead be
inserted as 502.21, because the actual float value that represents
502.215 is equal to 502.21499999999997498889570124447345733642578125.

Or for another example, 4.225865668139500E10 currently inserts as
42258656681.40 in your code, but using maximum precision would cause it
to insert as 42258656681.39 instead (the true value of a float8
'4.225865668139500E10' is 42258656681.39499664306640625.)

So while what we currently do is arguably wrong since it's doing two
rounding steps, fixing it wouldn't actually help your problem but would
just move the failure cases to different values.

--
Andrew (irc:RhodiumToad)