0

i got a weird problem using mariadb.

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE testing (afloat FLOAT NULL);
INSERT INTO testing VALUES(123456789.0);
SELECT * FROM testing;

But instead of returning 123456789 it returns 123457000.

I'm aware of the precision with floating numbers in IEEE 754 but even considering this the actual value stored is 123456792.

I came across this documentation but I am not that experienced with the ISO C standard. https://dev.mysql.com/doc/internals/en/floating-point-types.html

Can somebody explain why mariadb is not storing the expected value please?

PS: I know the problem could be solved using a double but I want to understand why it is not working with a float.

6
  • Try SELECT format(afloat, 0) as afloat from testing ; Commented Sep 27, 2021 at 13:28
  • or select round(afloat, 0) as afloat from testing ; Commented Sep 27, 2021 at 13:44
  • thanks of course this will work but it doesn't explain the actual reason why the float is interpreted as 123457000 - it has to be something with how the bytes are interpreted
    – JeffreyH
    Commented Sep 27, 2021 at 13:48
  • The float is displayed with accuracy of 6 digits because anything after that is garbage. Commented Sep 27, 2021 at 14:04
  • is this behaviour documented somewhere in the mysql/mariadb documentation?
    – JeffreyH
    Commented Sep 27, 2021 at 15:18

1 Answer 1

2

why the float is interpreted as 123457000

The outout formatting process did that. (Since you have not specified what tool was used to do the SELECT, I cannot address it specifically.)

Any output process has to decide what to do with imprecise data.

  1. How many decimal places should it show for SELECT 1/3;?

  2. Since FLOAT has only about 6-7 significant digits, why bother showing more than that? (DOUBLE: 16)

  3. Should the output be designed so that reading the string back in will get the same binary value?

From this, I see that your example violates #3:

    hex           float        check
    4ceb79a3 --> 123456792 --> 4ceb79a3
    4ceb79bd --> 123457000 --> 4ceb79bd - off by 26 ULPs

From these, I see that the output should have used 8 digits to satisfy #3:

    4ceb79a4 --> 123456800 --> 4ceb79a4 - 7 digits is not quite close enough (1 ULP)

Feel free to file a bug report against the specific product in question. Provide the link here and I will probably add my 2-cents worth.

"why mariadb is not storing the expected value" -- Try SELECT 1.0*flt FROM ... to see what was stored. That should provide the value displayed as DOUBLE. I claim the problem is with the display, not the storage.

3
  • I'm using the good old mysql cli - I also think it's the way the value is read/displayed. When changing the data type to double after inserting the correct value is displayed.
    – JeffreyH
    Commented Sep 29, 2021 at 15:19
  • > Since FLOAT has only about 6-7 significant digits... can you provide a documentation? This was what I was looking for before I created this post but I couldn't find much
    – JeffreyH
    Commented Sep 29, 2021 at 15:24
  • Since virtually every computer and language uses IEEE 754, the information about it is assumed without necessarily stating it. en.wikipedia.org/wiki/IEEE_754 is a starting point. (However, that document is a bit overwhelming.)
    – Rick James
    Commented Sep 29, 2021 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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