bug on filtering/querying fractional values

erik-foxcroft posted 3 years ago in General
I have a column of type FLOAT called `eu_max`. If I enter a filter string of:
eu_max = 0.01
then I get no results even though there are many entries of 0.01 in the table. Heidi uses the sql statement:
SELECT * FROM `dbname`.`tablename` WHERE eu_max = 0.01 ORDER BY `eu_max` ASC LIMIT 1000;
for this.

If I use the filter:
eu_max < 1 and eu_max > 0
then I get all the entries of value 0.01 and those of 0.1. If I then sort on this column it sorts values correctly.

If I go to the query tab and enter query string:
SELECT eu_max FROM `channels` WHERE eu_max = 0.01;
I also get no results.

The same applies for values >1 which have a decimal part, if I filter on a value of 23.5 I get no results even though I know this value exists in the filtered column.

Why can I only filter or select integer values even though the column type is FLOAT?
deneth posted 3 years ago
use WHERE eu_max LIKE 0.01 instead of WHERE eu_max = 0.01 in your query.

usually, i use DECIMAL, instead of FLOAT to store decimal values.
With DECIIMAL you can use WHERE eu_max = 0.01.
erik-foxcroft posted 3 years ago
Many thanks deneth. LIKE works fine.

I only need to find exact matches when doing manual searches, all code uses > and < etc. so FLOAT values are not a problem there.
kalvaro posted 3 years ago
It's worth noting that if the server does not return rows for a given query, there isn't much that HeidiSQL can do about it.

0.01 is the classical example of base 10 number that does not have a exact representation in base 2. In base 10 it takes 3 digits, in base 2 it takes infinite ones (it's periodic). Thus it's physically impossible to store an exact 0.01 number in any computer registry that uses binary logic.

In general, exact matches against floating point numbers are problematic in most computer languages.

Please login to leave a reply, or register at first.