How does MYSQL represent floats internally?

user_7840294 0
Viewed 85

I ran into this issue wherein inserting a number like 1234567 to a float column leads to a rounded off value of 1234570.

I understand that this is due to float point precision but what confuses me is that if I do some calculation with the above float column, then it uses the actual value (1234567).

If I just use a Select statement on the column it gives out the rounded off value, but if I cast it to decimal or perform some calculations on it, it does so with the actual value.

So my question is what makes MySQL act like this?

EDIT:

For example:

select cast(1234567 as float), cast(1234567 as float) + 1

returns:

1234570 1234568

Here is a db<>fiddle.

floating-point mysql sql
2 Answers

Your question about floating point accuracy isn't really limited to MySQL, it is a valid and similar question for any database or programming language. The bottom line is that due to the way which floats are represented internally, they are not exact, nor is arithmetic done with floats exact.

If you have the need for exact precision, then you should be using an exact type such as NUMERIC or DECIMAL.

user_18632290

According to the recipe we can get data types in your fiddle, and it shows (I run it at 8.0.20/FreeBSD/i386):

netch@localhost [test]> create temporary table t1 select cast(1234567 as float), cast(1234567 as float) + 1, cast(12345678 as float) + 1;
netch@localhost [test]> show create table t1;
+-------+---------------------------------------------------------------------->
| Table | Create Table                                                         >
+-------+---------------------------------------------------------------------->
| t1    | CREATE TEMPORARY TABLE `t1` (
  `cast(1234567 as float)` float NOT NULL DEFAULT '0',
  `cast(1234567 as float) + 1` double NOT NULL DEFAULT '0',
  `cast(12345678 as float) + 1` double NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------->
1 row in set (0.00 sec)

so its rule types result in float in one case and double in another case. 1234567 is exactly represented in float, but, see below for truncating.

Experiments show that CAST(AS float) provides float64 floating value (double in C) while marked as float but real value cutting to float32 only when finally putting to stored DB cell. But, with '+', float is converted to double before adding and this is denoted in addition operator type result, so, auto-creation exposes double. But if declared type remains float, MySQL not just truncates it to float32 - it rounds it to 6 default-printable digits :(

I cannot find these details in MySQL documentation (either this is omitted or I have bad digging skills for this documentation style), but this approach (except cutting to text form) is at least conforming to the common sense manner applied to C/C++. They allow having wider intermediate representation of floating values before placing to output variables.

As such CAST() is new for 8.0 (5.7 didn't support float and double in casts), this is likely of too raw implementation. Feel free to complain to MySQL developers to advance to a proper modus.

user_12896750
Related