db-derby-dev mailing list archives

Site index · List index
Message view
Top
From "Matthias Ohlemeyer (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1139) Division operator may give wrong results with NUMERIC operands
Date Wed, 12 Apr 2006 22:37:20 GMT
```    [ 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.

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: