problem querying a float column

[Marcus]

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.

Thanks very much in advance.

[Jan Pieter Kunst]

Marcus wrote:

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

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

--
Sorry, <de*****@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

[NC]

Marcus wrote:

I have a column in a MySQL database called price and
of type FLOAT(8,2).
Bad idea. Money variables should be long integers and
stated in cents (pence, centime, etc.)
there must be another way to test actual equality.

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

[R. Rajesh Jeba Anbiah]

Marcus wrote:

I have a column in a MySQL database called price and of type

FLOAT(8,2).
<snip>

http://dev.mysql.com/doc/mysql/en/pr...ith-float.html

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