Floats in MySQL

Question

So I have 2 different queries and both have a float as a criteria. One returns nothing when it should and the other returns the correct value.

SELECT *
FROM filters 
WHERE hole = 0.7

Returns nothing but

SELECT *
FROM filters 
WHERE ek = 66

Returns

'143987', '1', '14', '45', '0.7', '66', '0'

With the 0.7 as expected.

However the following

SELECT *
FROM needles 
WHERE needle_dia = 2.5

Returns

'2', 'H1004', '300', '2.5', '2040173', '2040177'

I have checked that they both have exactly the same type - Unsigned FLOAT, not non-null, default expression NULL.

Can there be a reason for this?

I am aware of the face that DECIMAL can be used instead of FLOAT but I would prefer to avoid it.

Answer 1

Hm, maybe it is wrong interpreted in the database.

Try a query like this:

SELECT *
FROM filters 
WHERE hole LIKE '0.7'

This should be independant if it is seen as a string.

Answer 2

Because the floating point numbering system is non-continuous, imprecise, and cannot represent certain values exactly (for example, 0.7), floating point values should never be compared for equality. You should always use a BETWEEN expression, bracketing it with a suitable error value:

WITH cteEpsilon AS (SELECT 0.0001 AS EPSILON FROM DUAL)
SELECT *
  FROM filters f
  CROSS JOIN cteEpsilon e
  WHERE 0.7 BETWEEN f.hole - e.EPSILON
                AND f.hole + e.EPSILON

A better choice, as you've noted in your question, is to use a precise numeric type such as DECIMAL.

---

"WHAT?!?", you say? "PROVE IT!!!"

OK, fair enough. Let's take a simple little program, declare a couple of floating-point constants, and compare them:

#include <stdio.h>

float  f = 0.7;
double d = 0.7;

int main()
  {
  if(f == d)
    printf("Equal\n");
  else
    printf("Not equal\n");

  printf("f = %60.50f\nd = %60.50f\n", f, d);
  }

Now - WITHOUT RUNNING IT OR LOOKING IN THE HIDDEN BOX - predict the output and write your prediction down on a piece of paper.

Done? OK. When compiled and run, the above produces:

Not equal
f = 0.69999998807907104492187500000000000000000000000000
d = 0.69999999999999995559107901499373838300000000000000

That's right - here we have two constants, both specified as 0.7, and they're not equal to one another.

The issue here is that 0.7 is not exactly representable as an IEEE-754 floating point value, no matter what precision we choose. Here the constants have different precision - one is a 4-byte floating point value (type float), the second is a "double precision" floating point value (type double), and because precision of each value is different the resulting value is different. (Right now the smart guy in the back of the room is saying, "Yeah, but they'll always compare correctly if you use integers". Nope, smart guy - change the constant to 1234567890 and see what happens.) Your compiler does the best it can to produce the closest approximation possible to 0.7, but the approximation is still not equal to 0.7, and differs with the precision of the variable used to store the value. This does not mean that IEEE-754 is broken, that your computer is broken, or that the universe has a secret trap door that leads to Candyland. (Drat! :-) It means that floating point numbers are much more complex than first glance might suggest. I suggest reading the following to start getting a grip on why this occurs:

What Every Computer Scientist Should Know About Floating-Point Arithmetic

The Floating Point Gui.de

Why Are Floating Point Numbers Inaccurate,

Floating-Point Numbers: Issues and Limitations

Why Floating-Point Numbers May Lose Precision