problems handling significant digits in mysql converting float to double

Question

I am inserting data from one table into another in a MariaDB database, where the column in the first table is FLOAT, and in the second it's DOUBLE. The data can have values of any size, precision and decimal places.

Here is what happens to the values when I do a straight-forward copy:

INSERT INTO data2 (value) SELECT value FROM data1

The values are given random extra significant figures:

FLOAT in data1           DOUBLE in data2
-0.000000000000454747    -0.0000000000004547473508864641
-122.319                 -122.31932830810547
14864199700              14864220160

CAST(value AS DECIMAL(65,30)) generates exactly the same values as col 2 above, except I see trailing zeroes.

Yet when I just do

UPDATE data2 SET value = 14867199700 WHERE id = 133025046;

the DOUBLE value is accepted.

Do I have to export all the value to an SQL script and re-import them? Isn't there a better way?

Despite hours trying to experimenting with the issue, I'm not much closer to a solution, despite its limited nature. I can see this is problem that besets all technologies, not just MariaDB or databases, so I have probably just missed the answer somewhere. Stackoverflow is desperately trying to guide to a solution with new suggestion features I hadn't seen before, but unfortunately they are no help, like the other suggested answers.

I think your problem lies in understanding what a float is vs a double vs decimal. float and double are by nature imprecise and only should be used for extremely large or small numbers where precision is given up in favor of performance. If you're dealing with specific values then decimal should be used. This answer gets into more specifics: https://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net. or https://softwareengineering.stackexchange.com/questions/188721/when-do-you-use-float-and-when-do-you-use-double — xQbert, Sep 29 '18 at 19:12
Hmmm, OK, maybe, but to cut to the chase, `14864199700` is not a random representation of the value in that field, binary to decimal or however - what I need do is work out how to do that binary -> decimal (like the console is doing) instead of `14864220160`. Otherwise I'm going to have a hard time telling people that they might be able to see that value, but they can't have it. — Adam, Sep 29 '18 at 19:54
So put it simply: if you need exact numbers, then float and double are not options. only decimal will always give you the value you store. Anytime money is being used, decimal i the ONLY choice. If you're dealing with scientific notations and precision doens't matter float and double become options. So what is the data and does precision matter? If it does then you're only choice is decimal and not float/double. decimal(65,30) allows you to store up to 35 digits before decimal and up to 30 after. The trailing zeros is a function of presentation and should drop off in the UI. — xQbert, Sep 29 '18 at 20:11
It may be too late already. Float in your source may not be the value the user entered. and if your intent is to retain those exact values when going to the next system, I would think decimal is your only choice. — xQbert, Sep 29 '18 at 20:15
But how can `14864199700` be unobtainable? I can copy the `float` value to another `float` column and it remains the same. What operations could I do to it to circumvent the change in value I see when it is converted to `DOUBLE` or `VARCHAR`? — Adam, Sep 29 '18 at 20:22
From https://en.wikipedia.org/wiki/Single-precision_floating-point_format:Integers in [ − 16777216 , 16777216 ] can be exactly represented, and outside of this range (which is 25bit only), they need to be rounded. Your 14864199700 is even outside of 32bit range, and it definitely need to be rounded. — Vladislav Vaintroub, Oct 01 '18 at 06:10
@xQbert - Even `DECIMAL` can be problematic -- You have to limit the number of places before and after the decimal point, and division is very likely to have rounding. — Rick James, Oct 10 '18 at 22:50

Rick James · Accepted Answer · 2018-10-10T22:55:29.970

Your test case is flawed. You are feeding in decimal digits, and not testing just the transfer of FLOAT to DOUBLE.

UPDATE tbl SET double_col = float_col will always copy exactly the same value. This because the DOUBLE representation is a superset of the FLOAT representation (53 vs 24 bits of precision; etc).

Literal, with decimal places: UPDATE tbl SET double_col = 123.456 will mangle the number because of rounding from decimal to DOUBLE. Ditto for float_col. Furthermore, the mangled results will be different!

Hole number literal: UPDATE tbl SET double_col = 14867199700 will be stored exactly. But if you put that same literal into a FLOAT, it will be rounded to 24 bits, so it cannot be stored exactly. You lose exactness at about 7 significant digits for FLOAT and about 16 for DOUBLE. The literal in this example has 9 significant digits (after ignoring trailing zeros).

That's just a sampling of the nightmares you can get into.

You must consider FLOAT and DOUBLE to be approximate. You should never compare for equality; you don't know what might have messed with the last bit of the value.

Also, you should not try to guess when MySQL will perform expressions in DECIMAL instead of DOUBLE.

And, keep in mind that division is usually imprecise due to rounding to some number of bits or decimals.

The "mantissa" of 14864199700 is

    1.10111010111111001101100 (binary of FLOAT : 24 bits including 'hidden' leading bit)
    1.1011101011111100110110000000101000000000000000000000 (binary of DOUBLE)
                                  ^ ^  (lost in FLOAT)

Each of those is multiplied by the same power of 2. The DOUBLE gets exactly 14864199700. The FLOAT lost the bits pointed to.

You can play around with such at https://gregstoll.dyndns.org/~gregstoll/floattohex/

Believe it or not, things used to be worse. People would be billed for $0.00 -- due to rounding errors. Or results of what should have been 1+1 showed as 1.99999999.

Nice round-up of the situation (sorry for the bad pun). I'll check that link when I'm home - currently blocked by ridiculous corporate firewall. — Adam, Oct 11 '18 at 13:17

problems handling significant digits in mysql converting float to double

1 Answers1