DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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