returned an empty result set if columnName = float

[emily-bcot]

select * from Table where data=2.6

After running the above query, db returned an empty result set. But Seen from table, there are many rows matching data=2.6 criterion.

Here the type of 'data' column is FLOAT. If changing the value to an integer like 'where data=2', the matched result sets will be returned.

Please let me know if anyone see the error on the query? Many thanks!

[niranga]

Try using LIKE

SELECT * FROM Table WHERE data LIKE 2.6

[smantscheff]

You should not compare explicit values with float values. Due to rounding and precision issues the value of a float can only be of a limited subset of all real numbers. It is stored as the number which is closest to the input value. Therefore mysql does not store 0.6 in a float field but a value very close to it.
Try this:

create table f(f float);
insert into f values (0.6);
select * from f where f - 0.6 > 0.0000001;
-- Empty set (0.00 sec)
select * from f where f - 0.6 > 0.00000001;
-- f = 0.6
select * from f where f = 0.6;
-- Empty set (0.00 sec)