[DERBY-123] Derby incorrectly rounds double values down during insert into NUMERIC

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.1
• Fix Version/s: 10.1.1.0
• Component/s: SQL
• Environment: Linux JDK 1.4.2
• Assignee: Unassigned
• Reporter: Geoff Soutter
• Created: 17/Jan/05 19:47
• Updated: 10/Feb/05 04:38
• Resolved: 10/Feb/05 04:38

Description

When inserting a double value into a field defined as NUMERIC(a,b) using PreparedStatement.setDouble(), Derby may incorrectly round the number down.

For example, a NUMERIC(5,2) field with a double = 4.64 ends up with a value of 4.63 in the database. This works fine in Oracle and other databases.

The problem occurs because double cannot represent 4.64 exactly, so the actual value is 4.6399999... SQLDecimal.setCoreValue uses BigDecimal(double) which constructs a BigDecimal of 4.6399999... and then SQLDecimal.setWidth uses value.setScale(desiredScale, BigDecimal.ROUND_DOWN); Note that BigDecimal javadoc recommends that the double constructor be avoided for this reason.

One fix appears to be to change SQLDecimal.setCoreValue(double) to avoid using the double constructor of BigDecimal. Using Double.toString() and BigDecimal(String) looks as if it would work as expected, because Double.toString() has "special rounding abilities" and converts 4.639999... back to 4.64.