Floating-point fields in MySQL

It is a well-known fact that floating-point values should not be compared for equality, due to rounding problems. MySQL puts an interesting twist to it, as demonstrated on the linux.org.ru forum. To see the strange effect, run the following SQL statements:

mysql> create table test (field float);
mysql> insert into test(field) values(0.3);
mysql> select * from test;
+-------+
| field |
+-------+
|   0.3 |
+-------+
1 row in set (0.00 sec)

mysql> select * from test where field = 0.3;
Empty set (0.00 sec)

I.e., it looks like MySQL successfully stores the value, but cannot find it later.

To see why it happens, recall that 0.3 cannot be represented exactly in binary form. It is a recurring binary fraction 0b0.0100110011... that has to be truncated somewhere when stored by a computer. When stored as a float data type in the table, 24 significant binary digits are retained. However, when 0.3 is mentioned directly in the query, it is treated as a double-precision number, and 53 significant bits are kept. The truncation thus occurs in different places, and the resulting numbers are just different:

mysql> select cast(field as decimal(15,15)) from test;
+-------------------------------+
| cast(field as decimal(15,15)) |
+-------------------------------+
|             0.300000011920929 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select cast(0.3 as decimal(15,15));
+-----------------------------+
| cast(0.3 as decimal(15,15)) |
+-----------------------------+
|           0.300000000000000 |
+-----------------------------+
1 row in set (0.00 sec)

MySQL documentation contains more information on problems with floating-point values.