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.
Can we close this issue? I think the discussion concluded that this was not a code error. If desired a new issue could be lodged for a warning and/or improved documentation.