DOUBLE values

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:
• 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

People

• Assignee:
Captain EXASOL
Reporter:
Captain EXASOL