Query-Related Issues
2972
The following example (for versions of MySQL older than 5.0.3) demonstrates the problem. It shows
that even for older
DECIMAL
columns, calculations that are done using floating-point operations
are subject to floating-point error. (Were you to replace the
DECIMAL
columns with
FLOAT
, similar
problems would occur for all versions of MySQL.)
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 should not satisfy the comparison
(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 factors such as computer
architecture or the compiler version or optimization level. For example, different CPUs may evaluate
floating-point numbers differently.
As of MySQL 5.0.3, you will get only the last row in the above result.
The problem cannot be solved by using
ROUND()
[913]
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. For example, on
some machines you may get the “correct” results by multiplying both arguments by 1, as the following
example shows.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
