db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [PATCH] (DERBY-123) Derby incorrectly rounds double values down during insert into NUMERIC
Date Thu, 03 Feb 2005 23:33:11 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I am preparing to commit this patch, but have you run Derby test suites
with the change? (derbyall)<br>
<br>
It is possible some of the master output files may need to be updated
as well...<br>
<br>
Satheesh<br>
<br>
Shreyas Kaushik wrote:<br>
<blockquote cite="mid42019EE0.5080203@Sun.com" type="cite">Patch for
this issue.
  <br>
  <br>
~ Shreyas
  <br>
  <br>
Shreyas Kaushik (JIRA) wrote:
  <br>
  <br>
  <blockquote type="cite">&nbsp;&nbsp;&nbsp; [
<a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-123?page=comments#action_58294">http://issues.apache.org/jira/browse/DERBY-123?page=comments#action_58294</a>
]
    <br>
&nbsp;&nbsp;&nbsp; Shreyas Kaushik commented on DERBY-123:
    <br>
---------------------------------------
    <br>
    <br>
The javadoc for BigDecimal constructor that takes a double as argument
is as below:
    <br>
    <br>
public BigDecimal(double val)
    <br>
    <br>
&nbsp;&nbsp; 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 &times;
val) is an integer.
    <br>
    <br>
&nbsp;&nbsp; Notes:
    <br>
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1. The results of this constructor can
be somewhat unpredictable.
One might assume that writing new&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
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.
    <br>
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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.
    <br>
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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.
    <br>
&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; Parameters:
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val - double value to be converted
to BigDecimal. &nbsp;&nbsp; Throws:
    <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NumberFormatException - if val
is infinite or NaN.
    <br>
    <br>
>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.
    <br>
    <br>
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.
    <br>
    <br>
    <br>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">Derby incorrectly rounds double values down
during insert into NUMERIC
      <br>
----------------------------------------------------------------------
      <br>
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Key: DERBY-123
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; URL: <a class="moz-txt-link-freetext"
href="http://issues.apache.org/jira/browse/DERBY-123">http://issues.apache.org/jira/browse/DERBY-123</a>
      <br>
&nbsp;&nbsp;&nbsp; Project: Derby
      <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type: Bug
      <br>
&nbsp;Components: SQL
      <br>
&nbsp;&nbsp; Versions: 10.0.2.1
      <br>
Environment: Linux JDK 1.4.2
      <br>
&nbsp;&nbsp; Reporter: Geoff Soutter
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">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.
      <br>
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.
      <br>
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.
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
&nbsp;
    <br>
    <br>
  </blockquote>
  <pre wrap="">
<hr size="4" width="90%">
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;
 	}
 
  </pre>
</blockquote>
</body>
</html>

Mime
View raw message