[DERBY-1139] Division operator may give wrong results with NUMERIC operands

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.1.2.1
• Component/s: SQL
• Labels: derby_triage10_8
• Urgency: Low
• Assignee: Unassigned
• Reporter: Matthias Ohlemeyer
• Created: 23/Mar/06 01:23
• Updated: 17/Feb/11 00:29

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.