List:General Discussion« Previous MessageNext Message »
From:Jani Tolonen Date:May 11 1999 12:22pm
Subject:problem with FLOAT(4) data type
View as plain text  
Andy Fedotov writes:
 > >Description:
 >         problem with FLOAT(4) data type
 > >How-To-Repeat:
 > 
 > mysql> create table test(test float(4));
 > Query OK, 0 rows affected (0.00 sec)
 > 
 > mysql> insert into test values (1234.10);
 > Query OK, 1 row affected (0.00 sec)
 > 
 > mysql> select * from test;
 > +---------+
 > | test    |
 > +---------+
 > | 1234.10 |
 > +---------+
 > 1 row in set (0.01 sec)
 > 
 > mysql> select * from test where test=1234.10;
 > Empty set (0.00 sec)
 > 
 > mysql> select * from test where test<1234.10;
 > +---------+
 > | test    |
 > +---------+
 > | 1234.10 |
 > +---------+
 > 1 row in set (0.00 sec)
 > 
 > mysql> 
 > 

Hi!

If you are able to, please see our discussion with subject
"Error comparing equality with numeric(18,0)".
It was done a short time ago.

The problem is that floating point numbers are not accurate, what you
see isn't _necessarily_ what the floating point number really is.
In your _first_ case the number was a bit under 1234.10
and that is the explanation for the behavior. 

With floating point numbers you have to decide some accuracy
with which to compare them. You can use 'between ... and ...'
And if you are using '<', you have to diminish a small number
from the number you are comparing and if you are comparing
with '>' you'll have to add a small number to the number you
are comparing. A short example:

mysql> show fields from f;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| f     | float(10,6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> select * from f;
+-------------+
| f           |
+-------------+
|   10.000001 |
| 2000.234497 |
| 1000.000610 |
+-------------+

/* The next doesn't work */

mysql> select * from f where f = 1000.000610;
Empty set (0.01 sec) 

/* Changing comparison to STRING type! Will be slower, but works.. */

mysql> select * from f where f like '1000.000610';
+-------------+
| f           |
+-------------+
| 1000.000610 |
+-------------+
1 row in set (0.00 sec)      


/* The right way to do it, the fast way. Comparison is done with numbers */

mysql> select * from f where f between (1000.000610 - 0.00001) and (1000.000610
 + 0.000001);
+-------------+
| f           |
+-------------+
| 1000.000610 |
+-------------+
1 row in set (0.00 sec)      

mysql> select * from f where f < 1000.000610;
+-----------+
| f         |
+-----------+
| 10.000001 |
+-----------+

/* The 1000.000610 is a bit bigger than it seems */

mysql> select * from f where f > 1000.000610;
+-------------+
| f           |
+-------------+
| 2000.234497 |
| 1000.000610 |
+-------------+
2 rows in set (0.01 sec)

/* The right way to compare */

mysql> select * from f where f < (1000.000610 - 0.000001) ;
+-----------+
| f         |
+-----------+
| 10.000001 |
+-----------+
1 row in set (0.01 sec)

/* and the second one ... */

mysql> select * from f where f > (1000.000610 + 0.000001) ;
+-------------+
| f           |
+-------------+
| 2000.234497 |
+-------------+
1 row in set (0.00 sec)

 > >Fix:
 > 
 > 	my solution was convering data type from float(4) to float(8) ;-)

You were lucky! ;) But that was only an incident... ;)

We will in the future fix so that one will be able to use '=' and
other exact comparison operators with floating point numbers, but for
now you'll have to do as above. We might also implement some kind of
'roughly equals' there the value would be in some accuracy range
definable by user.

Regards,

- Jani

-- 
+---------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___       ==  mysql@stripped         |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|       Jani Tolonen             |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|       mailto: jani@stripped      |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|       Helsinki                 |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____  Finland                  |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                           |
|             /*/             \*\                   Developers Team   |
+---------------------------------------------------------------------+
Thread
problem with FLOAT(4) data typeAndy Fedotov11 May
  • problem with FLOAT(4) data typeJani Tolonen11 May