16.10 - Example: Non-Associativity of Floating Point Arithmetic - 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

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