
Type: Bug

Status: Closed

Severity: High

Resolution: Fixed

Affects Version/s: 3.2.0 GA

Component/s: Database, Kitchen, Transformation

Labels:None

Notice:
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 srcdb\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:
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:
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)
34: }
35: }
36: else
37: {
38: if (precision==0 && length<18 && length>0) // Among others Oracle is affected here.
39:
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:
51: if (precision<=0 && length<=0) // undefined size: BIGNUMBER, precision on Oracle can be 38, too big for a Number type
52:
57: }
58: break;
...
Line 38 handles integer decimals with a length of 117 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 3643 could be replaced by:
36: else
37: {
38: if (precision==0 && length<18 && length>0) // Among others Oracle is affected here.
39:
else if (precision==0)
{ valtype=ValueMetaInterface.TYPE_BIGNUMBER; }42: }
43: if (length>15 && precision>0) valtype=ValueMetaInterface.TYPE_BIGNUMBER;