A.5.8. Problems with Floating-Point Comparisons
Note that the following section is relevant primarily to working
with DOUBLE and FLOAT
columns, due to the inexact nature of floating point numbers.
MySQL performs DECIMAL operations with a
precision of 64 decimal digits, which should solve most common
inaccuracy problems when it comes to DECIMAL
columns.
Floating-point numbers sometimes cause confusion because they
are not stored as exact values inside computer architecture.
What you can see on the screen usually is not the exact value of
the number. The data types FLOAT and
DOUBLE are such. DECIMAL
columns store values with exact precision because they are
represented as strings.
The following example demonstrates the problem using
DOUBLE:
mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
-> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
-> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
-> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
-> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
-> (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+-------+------+
| i | a | b |
+------+-------+------+
| 1 | 21.4 | 21.4 |
| 2 | 76.8 | 76.8 |
| 3 | 7.4 | 7.4 |
| 4 | 15.4 | 15.4 |
| 5 | 7.2 | 7.2 |
| 6 | -51.4 | 0 |
+------+-------+------+
The result is correct. Although the first five records look like
they shouldn't pass the comparison test (the values of
a and b do not appear to
be different), they may do so because the difference between the
numbers shows up around the tenth decimal or so, depending on
computer architecture.
If columns d1 and d2 had
been defined as DECIMAL rather than
DOUBLE, the result of the
SELECT query would have contained only one
row — the last one shown above.
