Search code examples
mysqldatabasedecimalmysql-workbenchalter

Can't alter table field from float to decimal in mysql-workbench

I have created a table with a FLOAT type column. However, I found that when I query the column's MAX, MIN, AVG values I get inaccurate numbers, where the returned value is larger than what is actually stored in the table. For example, this is the actual maximum number in the table: 0.00348675. Somehow, MAX returns this: 0.0034867459908127785

The inaccuracy turned to be due to choosing FLOAT as type. For this reason, I want to change the columns type to DECIMAL.

I use mysql-workbench in Ubuntu 18. When I right-click the table and choose Alter Table I can change the data types. Unfortunately, when I change the concerned field from FLOAT to DECIMAL, FLOAT returns again automatically. The workbench can not choose DECIMAL for some reason. See this picture. I can choose DECIMAL from a list. Then when I move the mouse elsewhere to click on the next empty row, so I can click Apply, the DECIMAL disappear and FLOAT returns again:

enter image description here Can anyone advise me what is the reason for the prolem? how to overcome the issue?

Solution

  • FLOAT and DOUBLE are encoded in binary, not decimal. Hence, most fractional numbers cannot be represented exactly when stored. (Numbers ending with .5, .25, .75, .125, etc) can be stored exactly because they involve powers of 2.

    FLOAT is good to about 7 significant digits; DOUBLE to about 16.

    0.00348675              You stored this decimal
0.003486746000000096    After conversion to binary, then back to decimal
0.0034867459908127785   See below

    (I suspect you actually stored 0.003486756.)

    Certain calculations are done in DOUBLE; I suspect MAX() does such. That involved converting the FLOAT to DOUBLE. Nothing is lost in this conversion.

    OK, I am at a loss to finish the explanation.

    Suffice it to say -- Whenever using FLOAT or DOUBLE, be aware that arithmetic and displaying have quirks like this.

    There are cases where MySQL tries to avoid such problems by using DECIMAL. I would guess that that somehow is involved here because the middle number above has about 14 significant digits, not 16, implying that something other than DOUBLE is involved -- perhaps DECIMAL(..., 16). Note: that is decimal places, as distinct from significant digits_.

    If you would like to pursue this further, provide a short test case in a bug filed at http://bugs.mysql.com and post the like to it here.