You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Shreyas Kaushik (JIRA)" <de...@db.apache.org> on 2005/01/31 10:08:19 UTC
[jira] Commented: (DERBY-123) Derby incorrectly rounds double values down during insert into NUMERIC
[ 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
Re: [PATCH] (DERBY-123) Derby incorrectly rounds double values down
during insert into NUMERIC
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
I ran derbyall, had no failures there.
thanks
Shreyas
Satheesh Bandaram wrote:
> I am preparing to commit this patch, but have you run Derby test
> suites with the change? (derbyall)
>
> It is possible some of the master output files may need to be updated
> as well...
>
> Satheesh
>
> Shreyas Kaushik wrote:
>
>> 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;
>> }
>>
>>
>>
Re: [PATCH] (DERBY-123) Derby incorrectly rounds double values down during insert into NUMERIC
Posted by RPost <rp...@pacbell.net>.
> "Daniel John Debrunner" wrote:
> Even using Double.toString() in itself creates an extra two objects . . .
Dan's right but interestingly, it is even more convoluted.
Double.toString() in Double.java is "public String toString() { return
String.valueOf(value);}
And then String.valueOf(value) in turn is "public static String
valueOf(double d) { return Double.toString(d);}
Which means it winds up calling the Double.toString(theValue) that you are
now going to call anyway.
Re: [PATCH] (DERBY-123) Derby incorrectly rounds double values down
during insert into NUMERIC
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Shreyas, would appreciate posting another patch, addressing both Dan's
and my concerns. You are pretty close already...
Thanks for running the tests. Committers would appreciate posting this
info along with all patches submitted.
Satheesh
Daniel John Debrunner wrote:
> Satheesh Bandaram wrote:
>
> >Also, would it be better to write the code as:
>
> >value = new BigDecimal(Double.toString(theValue));
>
> >to avoid creating a Double object? I suspect this code is used a lot
> >(when there are double values), so could save time.
>
> Another issue with the change is that there are other a handful of other
> cases in the code where a double is converted directly into a
> BigDecimal, I think these should be fixed as well, to be consistent.
> I did a quick search for new.*BigDecimal to find them.
>
> Dan.
>
Re: [PATCH] (DERBY-123) Derby incorrectly rounds double values down
during insert into NUMERIC
Posted by Daniel John Debrunner <dj...@debrunners.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Satheesh Bandaram wrote:
> Also, would it be better to write the code as:
>
> value = new BigDecimal(Double.toString(theValue));
>
> to avoid creating a Double object? I suspect this code is used a lot
> (when there are double values), so could save time.
I agree, whereever possible object creation should be avoided.
Imagine a table scan that returns a million rows, with new Double
the code is creating an extra one million temporary objects for garbage
collection.
Even using Double.toString() in itself creates an extra two objects (the
String and the char[] within String) per value, so with a million rows,
two million extra temporary objects for garbage collection. I assume the
correct behaviour justifies the expense. In general I assume
applications are not spending much time converting from double to
DECIMAL or DOUBLE to DECIMAL/BigDecimal.
Another issue with the change is that there are other a handful of other
cases in the code where a double is converted directly into a
BigDecimal, I think these should be fixed as well, to be consistent.
I did a quick search for new.*BigDecimal to find them.
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFCA4M/Iv0S4qsbfuQRAvXgAJoCk1XaPHe2AON8/KfNlUFavrH2+QCfc3qP
a8uiAvxQ0HXNmVZTsiuwUII=
=Md26
-----END PGP SIGNATURE-----
[PATCH] (DERBY-123) Derby incorrectly rounds double values down during
insert into NUMERIC
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
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.
>>
>>
>
>
>