Re: float comparison?

[Michael Stassen]

Hassan Schroeder wrote:
> Scott Gifford wrote:
> 
> 
>>>select * from price where amount = 3.45 // doesn't work
> 
> 
>>Floating point numbers are very rarely equal, because of small
>>rounding errors that happen all the time.  Probably the actual number
>>in the database is something like '3.44000000000000001'.
>>
>>Try something like this:
>>
>>   select * from price where amount > 3.44 and amount <= 3.46

I hope the "<=" was a typo?  Depending on your data and preferred method of 
rounding,

   SELECT * FROM price WHERE amount >= 3.445 AND amount < 3.455;

may be better.  In fact, the difference between 3.45 and the actual value stored 
is likely quite small.

mysql> select 3.45 + 0.0000000000000000;
+---------------------------+
| 3.45 + 0.0000000000000000 |
+---------------------------+
|        3.4500000000000002 |
+---------------------------+
1 row in set (0.01 sec)

Problems with FLOAT and equality are documented 
<http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html>.

In general, you could pick a tolerance

   SET @tol = .001;

to set your range

   SELECT * FROM price
   WHERE amount > 3.45 - @tol AND amount < 3.45 + @tol;

If your amounts are all supposed to be strictly 2 decimal places, .01 would work 
for your tolerance.  In that case, though, you should probably be using DECIMAL 
instead of FLOAT 
<http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html>.

> Or maybe             ... where ROUND(amount,2) = 3.45;

Unlike the range queries above, this one cannot use an index on the amount 
column to select rows, so it is a guaranteed full table scan.

Michael