XMLWordPrintable

    Details

    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      PDI currently has a problem handling DECIMAL(18) and DECIMAL(16 to 18, >0) fields correct. Values of this field type that utilize more than 15 decimal digits get corrupted.

      The DECIMAL(18) problem actually is caused by a bug which does not correctly classify a DECIMAL(18) field as PDI BigNumber. Instead the data is converted to a PDI Number (Java double) which does not have the capacity to represent integer numbers with more than 15 decimal digits without loosing precision.

      Here is the relevant code is located in the method getValueFromSQLType of src-db\org\pentaho\di\core\database\Database.java :

      ...
      1: case java.sql.Types.DECIMAL:
      2: case java.sql.Types.DOUBLE:
      3: case java.sql.Types.FLOAT:
      4: case java.sql.Types.REAL:
      5: case java.sql.Types.NUMERIC:
      6: valtype=ValueMetaInterface.TYPE_NUMBER;
      7: length=rm.getPrecision(index);
      8: precision=rm.getScale(index);
      9: if (length >=126) length=-1;
      10: if (precision >=126) precision=-1;
      11:
      12: if (type==java.sql.Types.DOUBLE || type==java.sql.Types.FLOAT || type==java.sql.Types.REAL)
      13: {
      14: if (precision==0)
      15:

      { 16: precision=-1; // precision is obviously incorrect if the type if Double/Float/Real 17: }

      18:
      19: // If we're dealing with PostgreSQL and double precision types
      20: if (databaseMeta.getDatabaseType()==DatabaseMeta.TYPE_DATABASE_POSTGRES && type==java.sql.Types.DOUBLE && precision==16 && length==16)
      21:

      { 22: precision=-1; 23: length=-1; 24: }

      25:
      26: // MySQL: max resolution is double precision floating point (double)
      27: // The (12,31) that is given back is not correct
      28: if (databaseMeta.getDatabaseType()==DatabaseMeta.TYPE_DATABASE_MYSQL)
      29: {
      30: if (precision >= length)

      { 31: precision=-1; 32: length=-1; 33: }

      34: }
      35: }
      36: else
      37: {
      38: if (precision==0 && length<18 && length>0) // Among others Oracle is affected here.
      39:

      { 40: valtype=ValueMetaInterface.TYPE_INTEGER; 41: }

      42: }
      43: if (length>18 || precision>18) valtype=ValueMetaInterface.TYPE_BIGNUMBER;
      44:
      45: if (databaseMeta.getDatabaseType()==DatabaseMeta.TYPE_DATABASE_ORACLE)
      46: {
      47: if (precision == 0 && length == 38 )
      48:

      { 49: valtype=ValueMetaInterface.TYPE_INTEGER; 50: }

      51: if (precision<=0 && length<=0) // undefined size: BIGNUMBER, precision on Oracle can be 38, too big for a Number type
      52:

      { 53: valtype=ValueMetaInterface.TYPE_BIGNUMBER; 54: length=-1; 55: precision=-1; 56: }

      57: }
      58: break;
      ...

      Line 38 handles integer decimals with a length of 1-17 by assigning them to a PDI integer (Java long).
      Line 43 handles integer decimals with a length greater than 18 by assigning them to a PDI BigNumber (Java BigDecimal).

      Neither of these conditions does handle a DECIMAL(18) thus in such a case a PDI Number (Java double) is used.

      A Java double has significand precision of 52 bits and thus can handle up to 15 decimal digits without loosing precision.
      A Java long can handle up to 18 decimal digits.

      In my opinion, for the case of integer decimals, line 38 should be:

      if (precision==0 && length<=18 && length>0)

      which now would handle an DECIMAL(18).

      The second problem are decimals that fulfill the following condition:

      length > 15 && length <=18 && precision > 0

      They would be assigned to a PDI Number (Java double) which can only handle up to 15 decimal digits. To fix this the lines 36-43 could be replaced by:

      36: else
      37: {
      38: if (precision==0 && length<18 && length>0) // Among others Oracle is affected here.
      39:

      { 40: valtype=ValueMetaInterface.TYPE_INTEGER; 41: }

      else if (precision==0)

      { valtype=ValueMetaInterface.TYPE_BIGNUMBER; }

      42: }
      43: if (length>15 && precision>0) valtype=ValueMetaInterface.TYPE_BIGNUMBER;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                gdavid Golda Thomas
                Reporter:
                kuno@ospald.de Kuno Ospald
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: