Details

    • Type: Explanation
    • Status: Obsolete
    • 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
    • Solution:
      Hide

      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:

      https://community.exasol.com/t5/database-features/double-values/ta-p/213

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Problem

      Working with DOUBLE values sometimes results in inaccuracy.

      Diagnosis

      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.

      You can verify the data type of your column by creating a table using the query, for example: 

      CREATE TABLE TEST AS SELECT ROUND(((71222-65504)/65504*100) ,1); -- Creates a Double
      

      Solution

      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

      Additional References

      https://docs.exasol.com/sql_references/data_types/datatypedetails.htm#NumericDataTypes

      Show
      Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base: https://community.exasol.com/t5/database-features/double-values/ta-p/213 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Problem Working with DOUBLE values sometimes results in inaccuracy. Diagnosis 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. You can verify the data type of your column by creating a table using the query, for example:  CREATE TABLE TEST AS SELECT ROUND(((71222-65504)/65504*100) ,1); -- Creates a Double Solution 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 Additional References https://docs.exasol.com/sql_references/data_types/datatypedetails.htm#NumericDataTypes
    • 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:
              Resolved: