B.5.5.8 Problems with Floating-Point Values

Floating-point numbers sometimes cause confusion because they
are approximate and not stored as exact values. A
floating-point value as written in an SQL statement may not be
the same as the value represented internally. Attempts to
treat floating-point values as exact in comparisons may lead
to problems. They are also subject to platform or
implementation dependencies. The
FLOAT and
DOUBLE data types are subject
to these issues. Before MySQL 5.0.3,
DECIMAL comparison operations
are approximate as well.

Prior to MySQL 5.0.3, DECIMAL
columns store values with exact precision because they are
represented as strings, but calculations on
DECIMAL values are done using
floating-point operations. As of 5.0.6, MySQL performs
DECIMAL operations with a
precision of 65 decimal digits (64 digits from 5.0.3 to
5.0.5), which should solve most common inaccuracy problems
when it comes to DECIMAL
columns. (If your server is from MySQL 5.0.3 or higher, but
you have DECIMAL columns in
tables that were created before 5.0.3, the old behavior still
applies to those columns. To convert the tables to the newer
DECIMAL format, dump them with
mysqldump and reload them.)

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.)

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() or similar functions,
because the result is still a floating-point number:

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.

Warning

Never use this method in your applications. It is not an
example of a trustworthy method!

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:

On some platforms, the SELECT statement
returns inf and -inf. On
others, it returns 0 and
-0.

An implication of the preceding issues is that if you attempt
to create a replication slave by dumping table contents with
mysqldump on the master and reloading the
dump file into the slave, tables containing floating-point
columns might differ between the two hosts.

Khalid - This is not a mystery. The problem is that Float columns only store 4-bytes per entry. This means that the precision available to the decimal portion of your number depends on the size of the non-decimal portion of your number. The more bytes are requires to represent the non-decimal portion of your number, the fewer bytes are available to represent the approximate decimal value of your number. If you store a sufficiently large number, your entire decimal value will be truncated to 0. You have solved the problem by increasing your per-entry storage to 8 bytes instead of 4.

Selecting a tolerance level is not good, because the tolerance level differs from value to value depending on the number. As I inspected duplicates in my db for example two float stored values both 13442 compared as NOT EQUAL to each other when using too high (0.01) tolerance level, however they were EQUAL when I used lower (0.1) tolerance level.

Therefore I also recommend to change the documentation because the recommended solution (compare the difference to a selected threshold) is not safe.

I translated the equation of Geoffrey Downs to MySQL as follows for FLOAT values: