16.10 - Operations on Floating Point Values - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
featnum
B035-1143-161K

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.

Here are some of the problems you might encounter:

  • 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 Teradata Database 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.