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.
