13 messages in com.mysql.lists.mysqlRe: float comparison?
FromSent OnAttachments
Jerry Swanson29 Oct 2005 06:50 
Scott Gifford29 Oct 2005 15:24 
Hassan Schroeder29 Oct 2005 16:04 
Fabricio Mota29 Oct 2005 20:13 
Stewart Smith31 Oct 2005 00:07 
Joerg Bruehe31 Oct 2005 05:33 
Michael Stassen31 Oct 2005 08:37 
Scott Gifford31 Oct 2005 08:52 
Fabricio Mota31 Oct 2005 16:16 
Stewart Smith31 Oct 2005 18:13 
Fabricio Mota31 Oct 2005 18:23 
Logan, David (SST - Adelaide)31 Oct 2005 18:35 
Fabricio Mota31 Oct 2005 19:06 
Subject:Re: float comparison?
From:Michael Stassen (Mich@verizon.net)
Date:10/31/2005 08:37:27 AM
List:com.mysql.lists.mysql

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