Entry

How should I do equality testing on floats?

Why won't WHERE a = 3.12 find the matching rows?

Jan 23rd, 2000 20:42
Nathan Wallace, Wayne Donaho

Ah, takes me back to my collage days.

The way to compare approximate types (floats), is to compare the
difference of the two numbers.

i.e.

    select count(*) from t where b = 3.12;

would be written as:

    select count(*) from t where abs(b-3.12)<0.01;

The 0.01 could be adjusted to match the degree of accuracy that you in
fact need.

Why do it this way?

In the table, the approximation is adequate for two digits of accuracy.
This means that a rounding algorithm is applied.  When you use 3.12 in
the select, the same rounding algorithm may not be used, since there is
no accuracy constraint.  3.12 is the same as 3.12000000 (more digits of
accuracy).  The result is that the underlying binary representation of a
(float(2))3.12 and a (float)3.12 is not equal, thus no results on the
equality test.  However the difference of the approximation of 3.12
stored in the database and the constant 3.12 in the select statement
will be less than 0.01 in all cases (when a float(2) is specified).