2

My exact MySQL version is 5.7.29-0ubuntu0.18.04.1. And I'm beginning to think that I'm getting crazy...

Just try these queries:

CREATE TABLE `float_bug` (
  `v` FLOAT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO float_bug VALUES(92160596);
SELECT * FROM float_bug;

Returns 92160600.

Then I tried these:

SELECT * FROM float_bug WHERE v = 92160596;
SELECT * FROM float_bug WHERE v BETWEEN 92160596 AND 92160600;

Both return nothing!

However this

SELECT * FROM float_bug WHERE v = 92160592;

Returns 92160600.

I'm ready to throw my computer into the wall, but unfortunately that won't help because I have exactly the same behaviour on my remote server and I can't throw it into the wall physically.

Any ideas what is going on here?

6
  • Did you try using OR instead of AND in your SELECT * FROM float_bug WHERE v = 92160596 AND 92160600; statement? Alternative might be to try SELECT * FROM float_bug WHERE v BETWEEN 92160595 AND 92160601 (I specifically added/subtracted 1 from the initial values). By the way: This is a pretty good looking first question. Congratulations and welcome to DBA.SE.
    – John K. N.
    Commented Mar 11, 2020 at 8:33
  • thanks @Johnakahot2use, it was a typo :) fixed with BETWEEN. I've already found the answer. It was float type not allowing more than 7 digits.
    – Stalinko
    Commented Mar 11, 2020 at 9:41
  • @mustaccio thanks but you referred the same what I posted in my own answer :)
    – Stalinko
    Commented Mar 11, 2020 at 13:03
  • Doesn't make it any less of a dupe
    – mustaccio
    Commented Mar 11, 2020 at 13:03
  • @mustaccio even though the source of the issues is the same the questions are different IMHO. That's why I won't confirm it's a duplicate.
    – Stalinko
    Commented Mar 11, 2020 at 13:07

2 Answers 2

2

This is not just a SQL thing: floating-point numbers should always be considered approximations in any language. See https://floating-point-gui.de/ amongst other references.

You can use the DOUBLE type instead which would help, as that can represent integers up to 53 bits in length accurately, but you would have problems later with bigger numbers.

Unless you absolutely must have floating-point numbers, these types should be avoided. Use integer (int, bigint, ...) or fixed-point types (decimal, numeric) instead. As you don't say what you are using these numbers to represent we can't make a more specific recommendation.

3
  • well I knew about floating-point problems but it's first time I face a situation where a number gets rounded to 2 signs BEFORE the floating point. This case was rather related to storage limitations than to floating-math problems.
    – Stalinko
    Commented Mar 11, 2020 at 13:02
  • Yeah, most discussions on the subject talk about the "classic" 0.1+0.2≠0.3 encounter so a lot of people who read the intro & examples but not the detailed explanation assume that it only affects to the right of the decimal point. The key to remember is that the limitation is significant digits, not specifically decimal places. Explanations tend to be written assuming a level of maths intuition that would make that obvious, but for a lot of self-taught coders that isn't ingrained. Commented Mar 11, 2020 at 16:36
  • well I'm not a self-taught, I have a Computer-Science degree and even learned IEEE-754 in details but that didn't save me from getting caught on this issue ))
    – Stalinko
    Commented Mar 12, 2020 at 3:52
1

It turns out I've found an answer :))

https://dba.stackexchange.com/a/144990/172761

FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38.

So bad it's not stated in official MySQL docs. At least I couldn't find it after checking all articles about floating numbers.

6

Not the answer you're looking for? Browse other questions tagged or ask your own question.