(mysql.info.gz) Problems with float
Info Catalog
(mysql.info.gz) No matching rows
(mysql.info.gz) Query Issues
A.5.8 Problems with Floating-Point Comparisons
----------------------------------------------
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 column
types `FLOAT', `DOUBLE', and `DECIMAL' are such. `DECIMAL' columns
store values with exact precision because they are represented as
strings, but calculations on `DECIMAL' values may be done using
floating-point operations.
The following example demonstrate the problem. It shows that even for
the `DECIMAL' column type, calculations that are done using
floating-point operations are subject to floating-point error.
mysql> CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
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.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
| 6 | -51.40 | 0.00 |
+------+--------+-------+
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.
The problem cannot be solved by using `ROUND()' or similar functions,
because the result is still a floating-point number:
mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i | a | b |
+------+--------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
| 6 | -51.40 | 0.00 |
+------+--------+-------+
This is what the numbers in column `a' look like when displayed with
more decimal places:
mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,
-> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;
+------+----------------------+-------+
| i | a | b |
+------+----------------------+-------+
| 1 | 21.3999999999999986 | 21.40 |
| 2 | 76.7999999999999972 | 76.80 |
| 3 | 7.4000000000000004 | 7.40 |
| 4 | 15.4000000000000004 | 15.40 |
| 5 | 7.2000000000000002 | 7.20 |
| 6 | -51.3999999999999986 | 0.00 |
+------+----------------------+-------+
Depending on your computer architecture, you may or may not see similar
results. Different CPUs may evaluate floating-point numbers
differently. For example, on some machines you may get the "correct"
results by multiplying both arguments by 1, as the following example
shows.
*Warning:* Never use this method in your applications. It is not an
example of a trustworthy method!
mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b
-> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+------+
| i | a | b |
+------+--------+------+
| 6 | -51.40 | 0.00 |
+------+--------+------+
The reason that the preceding example seems to work is that on the
particular machine where the test was done, CPU floating-point
arithmetic happens to round the numbers to the same value. However,
there is no rule that any CPU should do so, so this method cannot be
trusted.
The correct way to do floating-point number comparison is to first
decide on an acceptable tolerance for differences between the numbers
and then do the comparison against the tolerance value. For example, if
we agree that floating-point numbers should be regarded the same if
they are same within a precision of one in ten thousand (0.0001), the
comparison should be written to find differences larger than the
tolerance value:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+--------+------+
| i | a | b |
+------+--------+------+
| 6 | -51.40 | 0.00 |
+------+--------+------+
1 row in set (0.00 sec)
Conversely, to get rows where the numbers are the same, the test should
find differences within the tolerance value:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) <= 0.0001;
+------+-------+-------+
| i | a | b |
+------+-------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
+------+-------+-------+
Info Catalog
(mysql.info.gz) No matching rows
(mysql.info.gz) Query Issues
automatically generated byinfo2html