Example: Non-Associativity of Floating Point Arithmetic

Teradata Vantageā„¢ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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