Problem Statement

When comparing floating point values in a query while working with relational database systems (tested in MySQL and Postgres), some edge cases that are expected to pass the filter clause might fail in practice.

I faced this issue while querying a SQL-based table like:

SELECT * FROM `content_analysis_info` WHERE `metric` >= 0.7

But the records with metric=0.7 were not returned.

Practical Solution

I modified the query in this way:

SELECT * FROM `content_analysis_info` WHERE (`metric` - 0.7) >= -0.0001

Depending on the precision of the storage of metric you might have to choose an even smaller check value, but for the specific case I was working on, metric column values were stored with 3 significant digits, so -0.0001 did the trick.

If you want to know the actual technical reason behind this situation, keep reading:

Technical Explanation

Everything in the digital domain work with bits. So whether we're talking about storing a variable in memory or as a property in a database, the compilers have to work with binary-coded entities.

Floating point numbers are coded in binary domain with the following logic

So the MSB will act as a flag marking positive values with 0 and negative values with 1. Next, the exponent will be determined so that the closest value to the floating number is selected (ceiled to the next digit in absolute value). Finally, significand represents the numbers after zero (0.xxx...)

So as you can see, depending on the standards of the compiler (or the storage system) the precision of a floating point number might be different between two variables even in the same system, depending on the number of bits of significand.