MySQL crazy (?) floating point behavior

user_923846 0
Viewed 70

On MySQL 8.0.21, I have an empty table (Tbl) with a single column: num, of type float.

I insert a single line, with the value 0.1:

INSERT INTO Tbl(num) VALUES(0.1)

I run the following query and get the expected value 0.1:

SELECT MAX(num) FROM Tbl

Now, I run a query which I think is semantically equivalent to the first query, and get 0.10000000149011612:

SELECT MAX(IF(TRUE, num, 0)) FROM Tbl

Interestingly, this behavior doesn't reproduce without MAX, i.e. the following returns 0.1:

SELECT IF(TRUE, num, 0) FROM Tbl

I know that floating point numbers cannot always be represented accurately, and I understand why arithmetic operations can cause this sort of issue, but why should using this IF inside MAX make a difference?

floating-accuracy floating-point mysql
3 Answers

They return different types. When you use MAX(), it upsamples the float value to a double:

CREATE TABLE T_MAX AS SELECT MAX(IF(TRUE, num, 0)) AS max_num FROM Tbl;

mysql> SHOW CREATE TABLE T_MAX\G
*************************** 1. row ***************************
       Table: T_MAX
Create Table: CREATE TABLE `T_MAX` (
  `max_num` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE T_IF AS SELECT IF(TRUE, num, 0) AS if_num FROM Tbl;

mysql> SHOW CREATE TABLE T_IF\G
*************************** 1. row ***************************
       Table: T_IF
Create Table: CREATE TABLE `T_IF` (
  `if_num` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We can do a cast from float to double without doing MAX() and see the same effect:

mysql> SELECT CAST(num AS DOUBLE) AS dblnum FROM TBL;
+---------------------+
| dblnum              |
+---------------------+
| 0.10000000149011612 |
+---------------------+

Note that if we make the original stored num a double, it doesn't need to do a conversion, so it doesn't change the representation.

CREATE TABLE TBL2 (num DOUBLE);

INSERT INTO TBL2 (num) VALUES (0.1);

SELECT MAX(IF(TRUE, num, 0)) AS num FROM TBL2;
+------+
| num  |
+------+
|  0.1 |
+------+

So it's the conversion of float to double that introduces the issue.

Bill_Karwin_208600

Float and double can't be saved exactly, so you get fractals

To get rid of them you have two possibilites

CREATE TABLE Tbl(num float(2,1))

INSERT INTO Tbl(num) VALUES(0.1)

SELECT MAX(IF(TRUE, num, 0))   FROM Tbl

| MAX(IF(TRUE, num, 0)) |
| --------------------: |
|                   0.1 |

CREATE TABLE Tbl2(num float)

INSERT INTO Tbl2(num) VALUES(0.1)

SELECT ROUND(MAX(IF(TRUE, num, 0)),1)   FROM Tbl2

| ROUND(MAX(IF(TRUE, num, 0)),1) |
| -----------------------------: |
|                            0.1 |

db<>fiddle here

In both cases you need to know exactly how many digits after the cpmma/point you need

user_51935360

This is perhaps a partial answer, or at least some insight into what might be happening here. If we consider your second query:

SELECT MAX(IF(TRUE, num, 0)) FROM Tbl

Then it is likely that the way it is being evaluated is that the result from the call to IF(...) is being stored in some intermediate floating point variable. That is, the result from the call to IF(), which we know will be the floating point value 0.1, is being assigned to some other intermediate floating point variable. The act of making the assignment can cause the precision to change.

user_18632290
Related