# problem querying a float column

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on

I have a column in a MySQL database called price and of type FLOAT(8,2).

I then insert two rows into my table, one where price = 100.00 and one
where price = 100.01, and then try to find these rows:

query 1:
SELECT    price
FROM    table
WHERE    price = 100.00

query 2:
SELECT    price
FROM    table
WHERE    price = 100.01

query 1 successfully returns the row in question, while query 2 returns
the empty set.  Using quotes around the search term does not affect it
at all (as it shouldn't since I "should" be searching a float...)

If I try to insert and then search for any number that does not end in
00, I get the empty set.  I tried using LIKE and was able to find the
row, but there must be another way to test actual equality.

## Re: problem querying a float column

Marcus wrote:

Bad idea.  Money variables should be long integers and
stated in cents (pence, centime, etc.)

With floats, equality is a difficult concept.  Computers
do not compute floating-point numbers with absolute
precision.  What you think is 100.00 may in fact be
100.00000000000000002 or 100.00000000000000004, which,
needless to say, will be treated as different numbers.

Cheers,
NC

## Re: problem querying a float column

Marcus wrote:

It's not a good idea to use FLOAT for numbers which are meant to be
exact. A better data type for prices would be DECIMAL.

JP

--
Real e-mail address unavailable. 5000+ spams per month.

## [OT, MySQL] Re: problem querying a float column

Marcus wrote:

FLOAT(8,2).
<snip>

http://dev.mysql.com/doc/mysql/en/problems-with-float.html">http://dev.mysql.com/doc/mysql/en/problems-with-float.html

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com    Blog: http://rajeshanbiah.blogspot.com /