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.

Floats are approximate values. I guess .7 is not stored as exactly .7 and the where condition will convert .7 to a different float value.. — P.Salmon, Jul 31 '18 at 11:23
Floating point is *evil*! See [What Every Computer Scientist Should Know About Floating-Point Arithmetic](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html), [The Floating Point Gui.de](http://floating-point-gui.de/), [Why Are Floating Point Numbers Inaccurate](https://stackoverflow.com/questions/21895756/why-are-floating-point-numbers-inaccurate), [Floating-Point Numbers: Issues and Limitations](https://docs.python.org/2/tutorial/floatingpoint.html), and [Why Floating-Point Numbers May Lose Precision](https://msdn.microsoft.com/en-us/library/c151dt3s.aspx), for a start… — Bob Jarvis - Слава Україні, Jul 31 '18 at 11:28
'The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value' - https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html — P.Salmon, Jul 31 '18 at 11:30
A likely cause of the problem is that the values in the database are not exactly equal to the values represented by `2.5` or `0.7` but are displayed as “2.5” and “0.7” because a limited number of digits is used to display them. In this case, searching for the exact value would work. You would have to display the values more fully to see their true values. — Eric Postpischil, Jul 31 '18 at 11:54
@P.Salmon: Floating-point objects represent specific numbers exactly. Floating-point operations round their results. So all errors occur in floating-point operations, not in values. This is clearly specified in IEEE 754, and understanding it is important to reasoning about floating-point arithmetic correctly. — Eric Postpischil, Jul 31 '18 at 11:55
@P.Salmon: It is generally not good advice to recommend that floating-point comparisons be performed with a tolerance. Accepting as equal numbers that differ decreases false negatives (occasions on which a comparison reports two numbers are unequal when they would, if computed with exact mathematics, be equal) but increases false positives (occasions on which two numbers are reported as equal when they would, if computed with exact mathematics, be unequal). All such trade-offs are application-dependent, and there can be no general recommendation. — Eric Postpischil, Jul 31 '18 at 12:00
@Eric Postpischil 'Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values' https://stackoverflow.com/questions/51612028/floats-in-mysql?noredirect=1#comment90190346_51612028 — P.Salmon, Jul 31 '18 at 12:00
@P.Salmon: Most people attempting to compare floating-point values are [using it incorrectly in the first place](https://stackoverflow.com/a/49685306/298225). A correct recommendation is to consider why they need comparison in the first place. — Eric Postpischil, Jul 31 '18 at 12:01
@Eric Postpischil I'm not advising anything that's what the manual says — P.Salmon, Jul 31 '18 at 12:01
@P.Salmon: Your link points to my comment, but, as the quote comes from [this mySQL page](https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html), that appears to be the link you intended. The mySQL documentation is wrong. As I wrote, the IEEE 754 standard for floating-point arithmetic is clear on this matter, and it is authoritative when IEEE 754 arithmetic is used. — Eric Postpischil, Jul 31 '18 at 12:03
@P.Salmon: I did not say you were advising anything. I said it is generally not good advice. But you did repeat this bad advice from the mySQL documentation. It is generally not good to repeat or promote bad advice. — Eric Postpischil, Jul 31 '18 at 12:04

Bob Jarvis - Слава Україні · Answer 1 · 2018-07-31T14:26:00.913

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

score 0 · Answer 2 · edited Aug 01 '18 at 14:31

0

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.

edited Aug 01 '18 at 14:31

marc_s

732,580
175
1,330
1,459

answered Jul 31 '18 at 11:24

Fikret Basic

350
1
7

Thanks alot for your help. That seems to have worked. I also tried it with another value I was having trouble with, 1.2, and it worked there too. I'd love to know though why it works in one table and not the other. – njun Jul 31 '18 at 11:32
So I did a few experiments. If I change the 2.5 from the successful query to 0.7 or 2.7, no results are returned. Then only with LIKE am I able to get the correct value. – njun Jul 31 '18 at 11:39

Floats in MySQL

2 Answers2