Derby
  1. Derby
  2. DERBY-1139

Division operator may give wrong results with NUMERIC operands

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: None
    • Component/s: SQL
    • 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.

        Activity

        Hide
        Andrew McIntyre added a comment -

        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

        Show
        Andrew McIntyre added a comment - 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
        Hide
        Satheesh Bandaram added a comment -

        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)

        Show
        Satheesh Bandaram added a comment - 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)
        Hide
        Matthias Ohlemeyer added a comment -

        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?

        Show
        Matthias Ohlemeyer added a comment - 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?
        Hide
        Matthias Ohlemeyer added a comment -

        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.

        Show
        Matthias Ohlemeyer added a comment - 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.
        Hide
        Rick Hillegas added a comment -

        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.

        Show
        Rick Hillegas added a comment - 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.
        Hide
        Dag H. Wanvik added a comment -

        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.

        Show
        Dag H. Wanvik added a comment - 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.

          People

          • Assignee:
            Unassigned
            Reporter:
            Matthias Ohlemeyer
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development