You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Matthias Ohlemeyer (JIRA)" <de...@db.apache.org> on 2006/03/22 18:26:03 UTC
[jira] Created: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
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
[jira] Updated: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1139?page=all ]
Rick Hillegas updated DERBY-1139:
---------------------------------
Priority: Major (was: Critical)
Downgrading the priority of this issue to Major since according to Satheesh's analysis, Derby behavior seems to be correct albeit confusing. A warning or extra documentation could help clear up the confusion.
> 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
>
> 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
[jira] Commented: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
Posted by "Matthias Ohlemeyer (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
Posted by "Matthias Ohlemeyer (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1139?page=comments#action_12372742 ]
Andrew McIntyre commented on DERBY-1139:
----------------------------------------
Satheesh, based on the comment shouldn't this be changed to:
val = Math.max(NumberDataValue.MAX_DECIMAL_PRECISION_SCALE - lprec + lscale - rscale, 4);
FWIW, the algorithm that DB2 uses is described here:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/bjnrmstr156.htm
> 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
[jira] Commented: (DERBY-1139) Division operator may give wrong
results with NUMERIC operands
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ 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