bug on filtering/querying fractional values

User, date Message
Written by erik-foxcroft
1 month ago
Category: General
2 posts since Thu, 26 Jun 14
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?
Written by denethMoney, Euro
1 month ago
2 posts since Thu, 26 Jun 14
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.
Written by erik-foxcroft
1 month ago
2 posts since Thu, 26 Jun 14
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.
Written by kalvaro
4 weeks ago
587 posts since Thu, 29 Nov 07
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.