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