Patch for this issue.
~ Shreyas
Shreyas Kaushik (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-123?page=comments#action_58294 ]
Shreyas Kaushik commented on DERBY-123:
---------------------------------------
The javadoc for BigDecimal constructor that takes a double as argument is as
below:
public BigDecimal(double val)
Translates a double into a BigDecimal which is the exact decimal
representation of the double's binary floating-point value. The scale of the
returned BigDecimal is the smallest value such that (10scale � val) is an
integer.
Notes:
1. The results of this constructor can be somewhat unpredictable. One
might assume that writing new BigDecimal(0.1) in Java creates a
BigDecimal which is exactly equal to 0.1 (an unscaled value of 1, with a scale
of 1), but it is actually equal to
0.1000000000000000055511151231257827021181583404541015625. This is because 0.1
cannot be represented exactly as a double (or, for that matter, as a binary
fraction of any finite length). Thus, the value that is being passed in to the
constructor is not exactly equal to 0.1, appearances notwithstanding.
2. The String constructor, on the other hand, is perfectly predictable: writing new
BigDecimal("0.1") creates a BigDecimal which is exactly equal to 0.1, as one
would expect. Therefore, it is generally recommended that the String constructor be used
in preference to this one.
3. When a double must be used as a source for a BigDecimal, note that this constructor provides an exact conversion; it does not give the same result as converting the double to a String using the Double.toString(double) method and then using the BigDecimal(String) constructor. To get that result, use the static valueOf(double) method.
Parameters:
val - double value to be converted to BigDecimal.
Throws:
NumberFormatException - if val is infinite or NaN.
From the *Notes* section it is clear that double values cannto be stored precisely as the value that is represented since it might not be possible to represent it as a fraction of finite length, hence the behaviour here.
So it suggested we construct the BigDecimal value by using
Double.toString() method and passing the value to BigDecimal constructor to
build the exact value.
Derby incorrectly rounds double values down during insert into NUMERIC
----------------------------------------------------------------------
Key: DERBY-123
URL: http://issues.apache.org/jira/browse/DERBY-123
Project: Derby
Type: Bug
Components: SQL
Versions: 10.0.2.1
Environment: Linux JDK 1.4.2
Reporter: Geoff Soutter
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.
Index: java/engine/org/apache/derby/iapi/types/SQLDecimal.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLDecimal.java (revision
149478)
+++ java/engine/org/apache/derby/iapi/types/SQLDecimal.java (working copy)
@@ -720,7 +720,8 @@
}
private void setCoreValue(double theValue) {
- value = new BigDecimal(theValue);
+ Double dVal = new Double(theValue);
+ value = new BigDecimal(dVal.toString());
rawData = null;
}
