Details

Type: Bug

Status: Open

Priority: Major

Resolution: Unresolved

Affects Version/s: 10.1.2.1

Fix Version/s: None

Component/s: SQL

Labels:

Urgency:Low
Description
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.
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