Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2116160297.1144881440106.JavaMail.jira@ajax>
Date: Wed, 12 Apr 2006 23:37:20 +0100 (BST)
From: "Matthias Ohlemeyer (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1139) Division operator may give wrong
 results with NUMERIC operands
In-Reply-To: <956930219.1143048363383.JavaMail.jira@ajax>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1139?page=comments#action_12374265 ] 

Matthias Ohlemeyer commented on DERBY-1139:
-------------------------------------------

Thanks for the hints Satheesh, Andrew - it got me a little further.

Using NUMERIC(20,7) throughout my application instead of NUMERIC(31,11) solved the original issue, at least it seemed so. It does not address a whole lot of related issues concerning decimal arithmetic in DERBY. Even if the calculation of precision and scale is documented in the reference guide and even if Derby behaves just like documented I still think this behaviour is counterintuitive and errorprone, especially when a wrong result is given without a warning as in the example above: 1.5 / 2.5 <> 0!!! There are situations where I may not even be aware of the precision and scale of the operands and I happily go on calculating expressions for some financial transactions without ever noticing the accumulated errors.

Please consider the following table:

CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(20,7), n2 NUMERIC(20,7));
INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5); 

Now calculating

SELECT n1/n2 FROM t

yields the correct result 0.600... It's too bad that the equivalent query (only one row in t!)

SELECT SUM(n1)/SUM(n2) FROM t

yields 0.00.... The reason seems to be that the SUM-function automatically adjusts the precision to 31. Is the user of the database to think of all these implications when using decimal arithmetic? Or would he be better off using DOUBLE instead of NUMERIC (in most situations this is not an option when storing and calculating currency data).

Another "unexpected" result, when I switched to NUMERIC(20,7):

If I do the following query

SELECT CAST(1.1 AS NUMERIC(20,7)) * CAST(2.2 AS NUMERIC(20,7)) * CAST(3.3 AS NUMERIC(20,7)) * CAST(4.4 AS NUMERIC(20,7)) * CAST(5.5 AS NUMERIC(20,7)) FROM TEST

I get the following output in my SQL-client

org.apache.derby.client.am.SqlException: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,31)

whereas the same statement with DOUBLE instead of NUMERIC(20,7) flawlessly calculates the correct value. The reason here is the accumulation of scale-values.

After my experiences I think that Derby's DECIMAL subsystem need a thorough check and improvement: Users should not be troubled with choosing the right precision and scale of their database fields and in numerical calculations; they should only be bothered when a value cannot be stored in a  database column because precision or scale is out of the defined range.  (The Oracle datatype NUMBER datatype behaves like that!) Would this qualify as an enhancement request?

> Division operator may give wrong results with NUMERIC operands
> --------------------------------------------------------------
>
>          Key: DERBY-1139
>          URL: http://issues.apache.org/jira/browse/DERBY-1139
>      Project: Derby
>         Type: Bug

>   Components: SQL
>     Versions: 10.1.2.1
>     Reporter: Matthias Ohlemeyer
>     Priority: Critical

>
> The division operator '/' may give wrong results when used with NUMRERIC operands.
> Example (copied from ij):
> CREATE TABLE t (d1 DOUBLE, d2 DOUBLE, n1 NUMERIC(31,11), n2
> NUMERIC(31,11));
> INSERT INTO t VALUES (1.5, 2.5, 1.5, 2.5);
> SELECT d1/d2, n1/n2, n1*(1.0/n2) FROM t;
> 1           |2                 |3
> ----------------------------------------------------------------
> 0.6         |0                 |0.600000000000000000000000000000
> 1 row selected
> The result in column 2 should not be zero, but 0.6.
> It seems there is something wrong with the calculation of the scale. Hint from Satheesh Bandaram:
> If you look at NumericTypeCompiler code, which calculates the scale and precision of operation result type, the comments and the code doesn't seem to match. (getScale() method):
> NumericTypeCompiler.java
>         else if (TypeCompiler.DIVIDE_OP.equals(operator))
>         {
>             /*
>             ** Take max left scale + right precision - right scale + 1,
>             ** or 4, whichever is biggest
>             */
>             LanguageConnectionContext lcc = (LanguageConnectionContext)
>                 (ContextService.getContext(LanguageConnectionContext.CONTEXT_ID));
>             // Scale: 31 - left precision + left scale - right scale
>             val = Math.max(NumberDataValue.MAX_DECIMAL_PRECISION_SCALE - lprec + lscale - rscale, 0);
>         }
> Here val is returning zero for scale, it seems.

-- 
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
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira