Added information about floating point numbers.

1 files changed, 128 insertions, 11 deletions

diff --git a/Docs/manual.texi b/Docs/manual.texi
index ae2f1248458..ffa5bdfaafb 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -44369,6 +44369,7 @@ the server runs, for example, in @code{safe_mysqld} or @code{mysql.server}.
 * Problems with alias::         Problems with @code{alias}
 * Deleting from related tables::  Deleting Rows from Related Tables
 * No matching rows::            Solving Problems with No Matching Rows
+* Problems with float::         Problems with floating point comparison
 @end menu
 
 @node Case sensitivity, Using DATE, Query Issues, Query Issues
@@ -44611,7 +44612,7 @@ id's per query if the @code{related_column} is an index.  If the
 number of arguments in the @code{IN} clause.
 
 
-@node No matching rows,  , Deleting from related tables, Query Issues
+@node No matching rows, Problems with float, Deleting from related tables, Query Issues
 @appendixsubsec Solving Problems with No Matching Rows
 
 @cindex no matching rows
@@ -44639,16 +44640,10 @@ the table that was last removed from the query.
 
 @item
 If you are comparing @code{FLOAT} or @code{DOUBLE} columns with numbers that
-have decimals, you can't use @code{=}!  This problem is common in most
-computer languages because floating-point values are not exact values:
-
-@example
-mysql> SELECT * FROM table_name WHERE float_column=3.5;
-   ->
-mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
-@end example
-
-In most cases, changing the @code{FLOAT} to a @code{DOUBLE} will fix this!
+have decimals, you can't use @code{'='}. This problem is common in most
+computer languages because floating-point values are not exact values.
+In most cases, changing the @code{FLOAT} to a @code{DOUBLE} will fix this.
+@xref{Problems with float}.
 
 @item
 If you still can't figure out what's wrong, create a minimal test that can
@@ -44666,6 +44661,128 @@ shell> mysql test2 < query.sql
 Post the test file using @code{mysqlbug} to @email{mysql@@lists.mysql.com}.
 @end enumerate
 
+@node Problems with float,  , No matching rows, Query Issues
+@appendixsubsec Problems with floating point comparison
+
+Floating point numbers cause confusion sometimes, because these numbers
+are not stored as exact values inside computer architecture. What one
+can see on the screen usually is not the exact value of the number.
+
+Field types @code{FLOAT} and @code{DECIMAL} are such.
+
+@example
+CREATE TABLE t1 (i int, d1 decimal(9,2), d2 decimal(9,2));
+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 |
++------+--------+-------+
+@end example
+
+The result is correct. Although the first five records look like they
+shouldn't pass the comparison test, they may do so because the
+difference between the numbers show up around tenth decimal, or so
+depending on computer architecture.
+
+The problem cannot be solved by using ROUND() (or similar function),
+because the result is still a floating point number. Example:
+
+@example
+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 |
++------+--------+-------+
+@end example
+
+This is what the numbers in row 'a' look like:
+@example
+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 |
++------+----------------------+-------+
+@end example
+
+Depending on the computer architecture you may or may not see similar results.
+Each CPU may evaluate floating point numbers differently. For example in
+some machines you may get 'right' results by multiplaying both arguments
+with 1, an example follows.
+
+@strong{WARNING: NEVER TRUST THIS METHOD IN YOUR APPLICATION, THIS IS
+AN EXAMPLE OF A WRONG METHOD!!!}
+
+@example
+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 |
++------+--------+------+
+@end example
+
+The reason why the above example seems to be working is that on the
+particular machine where the test was done, the CPU floating point
+arithmetics happens to round the numbers to same, but there is no
+rule that any CPU should do so, so it cannot be trusted.
+
+The correct way to do floating point number comparison is to first
+decide on what is the wanted tolerance between the numbers and then do
+the comparsion against the tolerance number. For example, if we agree on
+that floating point numbers should be regarded the same, if they are
+same with precision of one of ten thousand (0.0001), the comparsion
+should be done like this:
+
+@example
+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)
+@end example
+
+And vice versa, if we wanted to get rows where the numbers are the same,
+the test would be:
+
+@example
+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 |
++------+-------+-------+
+@end example
 
 @node Table Definition Issues,  , Query Issues, Problems
 @appendixsec Table Definition Related Issues