Bug #43561 can't find some floating point numbers

• Submitted: 11 Mar 2009 13:05
• Modified: 11 Mar 2009 14:20
• Reporter: Mario Dejung
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.70
• OS: Linux
• CPU Architecture: Any
• Tags: <=, =, between, FLOAT

[11 Mar 2009 13:05] Mario Dejung

Description:
I have a table with float values in it, and I try to search for them with a normal "column BETWEEN x AND y".

It happens, that I can't find some values in this way, as specially the value 2.2

I have a short example below, but how can I handle this issue?

How to repeat:
mysql> create table test (test FLOAT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values (2),(2.1),(2.2),(2.21),(2.22),(2.23),(2.24),(2.25),(2.26);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from test where test between 2 and 2.24;
+------+
| test |
+------+
|    2 | 
|  2.1 | 
|  2.2 | 
| 2.21 | 
| 2.22 | 
| 2.23 | 
+------+
6 rows in set (0.00 sec)
>>>>> 2.24 is missing

mysql> select * from test where test between 2 and 2.25;
+------+
| test |
+------+
|    2 | 
|  2.1 | 
|  2.2 | 
| 2.21 | 
| 2.22 | 
| 2.23 | 
| 2.24 | 
| 2.25 | 
+------+
8 rows in set (0.00 sec)
>>>>> 2.25 is found

mysql> select * from test where test between 2 and 2.2;
+------+
| test |
+------+
|    2 | 
|  2.1 | 
+------+
2 rows in set (0.00 sec)
>>>>> 2.2 is missing!

mysql> select * from test where test = 2.2;
Empty set (0.00 sec)
>>>>> Even when I search for the "exact" number I can't find the entry

mysql> select * from test where test = 2.25;
+------+
| test |
+------+
| 2.25 | 
+------+
1 row in set (0.00 sec)
>>>>> 2.25 gets found

[11 Mar 2009 14:14] Valeriy Kravchuk

Sorry, but this is expected and documented behavior. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html. Look:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.80-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test (test FLOAT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into test values (2),(2.1),(2.2),(2.21),(2.22),(2.23),(2.24),(2.25),(2.26);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

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

mysql> select * from test where test<2.3;
+------+
| test |
+------+
|    2 | 
|  2.1 | 
|  2.2 | 
| 2.21 | 
| 2.22 | 
| 2.23 | 
| 2.24 | 
| 2.25 | 
| 2.26 | 
+------+
9 rows in set (0.00 sec)

mysql> select 2.2*1000000, test*1000000 from test where test < 2.21;
+-------------+------------------+
| 2.2*1000000 | test*1000000     |
+-------------+------------------+
|   2200000.0 |          2000000 | 
|   2200000.0 | 2099999.90463257 | 
|   2200000.0 | 2200000.04768372 | 
+-------------+------------------+
3 rows in set (0.00 sec)

So, 2.2 is NOT stored exactly as 2.2 in FLOAT column.

[11 Mar 2009 14:20] Mario Dejung

Thanks, I figured it out just one second ago. I changed my values to DECIMAL, now everything works fine.