Because floating-point numbers are not stored as exact values, inaccuracy is inherent and unavoidable when floating-point numbers are involved in comparisons and computations.
- Identical computations in floating point arithmetic may produce 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 rarely have exact binary representations, calculations involving floating point values can often produce unexpected results.
For example, the decimal number 0.1 is a repeating sequence in binary and does not have a precise binary representation. If you compare the results of a calculation to an expected value, you are unlikely to get exactly the result you expected.
- 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 nonassociativity 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 performing an aggregation, Analytics Database 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 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 may cause 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.