FAQ
Why when I do select from database and try to compare float field with float

amount --- type float

select * from price where amount = 3.45 // doesn't work
select * from price where amount = '3.45' //doesn't work

select * from price where amoun like '3.45' //work

My question is how to compare float field with float number. What operator I
should use?

## Search Discussions

•  at Oct 29, 2005 at 10:24 pm ⇧

Jerry Swanson writes:

Why when I do select from database and try to compare float field with float

amount --- type float

select * from price where amount = 3.45 // doesn't work
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

----Scott.
•  at Oct 29, 2005 at 11:01 pm ⇧

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
Or maybe ... where ROUND(amount,2) = 3.45;

--
Hassan Schroeder ----------------------------- hassan@webtuitive.com
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com

dream. code.
•  at Oct 31, 2005 at 4:36 pm ⇧

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;

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
<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
•  at Oct 31, 2005 at 4:51 pm ⇧

Michael Stassen writes:

Scott Gifford wrote:
[...]
Try something like this:

select * from price where amount > 3.44 and amount <= 3.46
I hope the "<=" was a typo?
Yes.

----Scott.
•  at Oct 31, 2005 at 1:32 pm ⇧
Hi!

Scott Gifford wrote:
Jerry Swanson <pmysql@gmail.com> writes:

Why when I do select from database and try to compare float field with float

amount --- type float

select * from price where amount = 3.45 // doesn't work
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. [[...]]
This is the problem to convert fractions between the decimal and the
binary system.

For a coarse analogy, try to express 1/3 exactly using a finite number
of decimal digits.

http://cch.loria.fr/documentation/IEEE754/ACM/goldberg.pdf

Regards,
Jörg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

## Related Discussions

Discussion Overview
 group mysql categories mysql posted Oct 29, '05 at 1:49p active Oct 31, '05 at 4:51p posts 6 users 5 website mysql.com irc #mysql

### 5 users in discussion

Content

People

Support

Translate

site design / logo © 2018 Grokbase