15.10 - Operations on Floating Point Values - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

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.

    Consider a table where the same values are inserted into two floating point columns, but in a different order:

       CREATE TABLE t1 (i INTEGER, a FLOAT, b FLOAT);
       INSERT t1 (1, 1000.55, 2000.7);
       INSERT t1 (1, 2000.4, 2000.1);
       INSERT t1 (1, 2000.1, 2000.4);
       INSERT t1 (1, 2000.7, 1000.55);

    The conditional expression in the following SELECT statement compares the sums of the values in the two columns:

       SELECT i, SUM(a) as sum_a, SUM(b) as sum_b
       FROM t1
       GROUP BY i
       HAVING sum_a <> sum_b;

    Because the values that the two SUM calculations uses are the same, the obvious result is that no rows are returned. However, the result is:

                 i                   sum_a                   sum_b
       -----------  ----------------------  ----------------------
                 1   7.00175000000000E 003   7.00175000000000E 003

    What appears to be an invisible error has crept into the calculations. The following statement shows the error:

       SELECT ABS(SUM(a) - SUM(b)) FROM t1;

    Here is the result:

       Abs((Sum(a)-Sum(b)))
       ----------------------
        1.81898940354586E-012

    Calculations involving floating point values often produce results that are not what you expect. If you perform a floating point calculation and then compare the results against some expected value, it is unlikely that you get the intended result. Consider the results of “Example 1: Non-Associativity of Floating Point Arithmetic” on page 121.

    Instead of comparing the results of a floating point calculation, make sure that the result is greater or less than what is needed, with a given error. Here is an example of how to rewrite the statement in “Example 1: Non-Associativity of Floating Point Arithmetic” on page 121 and achieve the desired results:

       SELECT i, SUM(a) as sum_a, SUM(b) as sum_b
       FROM t1
       GROUP BY i
       HAVING ABS(sum_a - sum_b) > 1E-10;