Inconsistencies When Used With Common C Library Functions
Use of floating point data in Common C library functions can be inconsistent between different compilers and supporting libraries. For example, trigonometric functions in the C runtime library may return slightly different values depending on the Linux version or distribution.
Comparison and Computation Inaccuracies
Because floating point numbers are not stored as exact values, some inaccuracy is inherent and unavoidable when they are involved in comparisons and computations.
- Identical computations in floating point arithmetic may produce slightly different results on different machines because internal precision differs from computer to computer, and from model to model in the same series of computer.
- Because floating point decimal values generally do not have exact binary representations, calculations involving floating point values can often produce results that are not what you might expect. For example, the common decimal number 0.1 is a repeating sequence in binary and does not have a precise binary representation. If you perform a calculation and then compare the results against some expected value, it is highly unlikely that you get exactly the result you intended.
- If you add or subtract floating point numbers that differ greatly in size, the contribution of the small numbers can effectively be lost. For example, 1E20 + 1.0 and 1E20 - 1.0 evaluate to 1E20.
- Operations involving floating point numbers are not always associative due to approximation and rounding errors: ((A + B) + C) is not always equal to (A + (B + C)).
Although not readily apparent, the non-associativity of floating point arithmetic can also affect aggregate operations: you can get different results each time you use an aggregate function on a given set of floating point data. When SQL Engine performs an aggregation, it accumulates individual terms from each AMP involved in the computation and evaluates the terms in order of arrival to produce the final result. Because the order of evaluation can produce slightly different results, and because the order in which individual AMPs finish their part of the work is unpredictable, the results of an aggregate function on the same data on the same system can vary.
- Conversion of DECIMAL and INTEGER values to FLOAT values might result in a loss of precision or produce a number that cannot be represented exactly.
- GROUP BY on a FLOAT type can produce inconsistent results.
If you need exact results, do not use floating point types.