XMLWordPrintable

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 4.1.0, EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Symptoms:
      Hide

      Working with DOUBLE values sometimes results in inaccuracy.
      Examples:

      SELECT ROUND(((71222-65504)/65504*100) ,1); 
      /* (71222-65504)/65504*100  = 8.7292379 --> expected result = 8.7 */
      >  8.699999999999999
      SELECT cast(1 as DOUBLE) - cast(1E-16 as DOUBLE) AS approx;
      /*expected result = 0.9999999999999999*/
      > 1
      
      Show
      Working with DOUBLE values sometimes results in inaccuracy. Examples: SELECT ROUND(((71222-65504)/65504*100) ,1); /* (71222-65504)/65504*100 = 8.7292379 --> expected result = 8.7 */ > 8.699999999999999 SELECT cast (1 as DOUBLE ) - cast (1E-16 as DOUBLE ) AS approx; /*expected result = 0.9999999999999999*/ > 1
    • Explanation:
      Hide

      Those inaccuracies are not unexpected when dealing with DOUBLEs. As DOUBLE is an approximative data type, some values can not be stored exactly. This is a general issue of floating point arithmetic.

      Show
      Those inaccuracies are not unexpected when dealing with DOUBLEs. As DOUBLE is an approximative data type, some values can not be stored exactly. This is a general issue of floating point arithmetic.
    • Solution:
      Hide

      Usage of exact numeric type DECIMAL.

      SELECT ROUND(CAST((71222-65504)/65504*100 AS DECIMAL(16,3)) ,1);
      > 8.7
      SELECT cast(1 as DECIMAL(17,16)) - cast(1E-16 as DECIMAL(17,16)) AS exact;
      > 0.9999999999999999
      
      Show
      Usage of exact numeric type DECIMAL. SELECT ROUND( CAST ((71222-65504)/65504*100 AS DECIMAL (16,3)) ,1); > 8.7 SELECT cast (1 as DECIMAL (17,16)) - cast (1E-16 as DECIMAL (17,16)) AS exact; > 0.9999999999999999
    • Category 1:
      SQL - Data types

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: