db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Matthias Ohlemeyer (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1139) Division operator may give wrong results with NUMERIC operands
Date Thu, 13 Apr 2006 09:35:59 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1139?page=comments#action_12374318 ] 

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

Looking at the "Multiplication-Problem" above where a SQLException is thrown I think a minimal
requirement for DECIMAL arithmetic could be:

"If the exact value of an arithmetic calcuation does not fit into the result type (NUMERIC(x.,y)),
an Exception should be thrown."

This would at least make it possible to prevent false results either as presented to the user
or stored in the database; it would be especially necessary to apply this rule to the division
operator (see above).

It still does not make DECIMAL arithmetic any better on Derby though - I still think that
an overhaul to make it more intuitive and user friendly is urgently necessary.

> 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


Mime
View raw message