Member Avatar for 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!

  • 3 Contributors
  • 2 Replies
  • 216 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for niranga

Try using LIKE

SELECT * FROM Table WHERE data LIKE 2.6
Member Avatar for 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)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.