db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1139) Division operator may give wrong results with NUMERIC operands
Date Sat, 01 Apr 2006 02:22:38 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1139?page=comments#action_12372750 ] 

Satheesh Bandaram commented on DERBY-1139:
------------------------------------------

Thanks Andrew for the link... I looked at Derby documentation. Based on what I find here,
Derby may be doing the right thing, just the comments in the code may need to be changed.

Matthias, can you follow up on DECIMAL/NUMERIC arithmetic discussion in Derby reference guide
and may be try adjusting your datatype precision and scale?

Scale for decimal arithmetic
---------------------------------------
SQL statements can involve arithmetic expressions that use decimal data types of
different precisions (the total number of digits, both to the left and to the right of the
decimal point) and scales (the number of digits of the fractional component). The
precision and scale of the resulting decimal type depend on the precision and scale of the
operands.
Given an arithmetic expression that involves two decimal operands:
• lp stands for the precision of the left operand
• rp stands for the precision of the right operand
• ls stands for the scale of the left operand
• rs stands for the scale of the right operand
Use the following formulas to determine the scale of the resulting data type for the
following kinds of arithmetical expressions:
• multiplication
ls + rs
• division
31 - lp + ls - rs
• AVG()
max(max(ls, rs), 4)
• all others
max(ls, rs)

> 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