FAQ

[MySQL] float comparison?

Jerry Swanson
Oct 29, 2005 at 1:49 pm
Why when I do select from database and try to compare float field with float
number, I have no results

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?
reply

Search Discussions

5 responses

  • Scott Gifford 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
    number, I have no results

    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.
  • Hassan Schroeder 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 ----------------------------- has...@...com
    Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com

    dream. code.
  • Michael Stassen 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;

    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
  • Scott Gifford 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.
  • Joerg Bruehe at Oct 31, 2005 at 1:32 pm
    Hi!

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

    Why when I do select from database and try to compare float field with float
    number, I have no results

    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.

    For more info, AFAIK this is the standard reference:
    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 Navigation
viewthread | post