MySQL FLOOR shows unexpected results

Question

CREATE TABLE table_name (col_a double(10,2), col_b double(10,2), col_c double(10,2));
INSERT INTO table_name VALUES(36.3, 0, 6.3);

QUERY

SELECT FLOOR(36.3- 0 -6.3), FLOOR(col_a - col_b - col_c) AS calc, col_a, col_b, col_c 
FROM table_name LIMIT 1;

RESULT

---

the first value => selected FLOOR(36.3- 0 -6.3)will result in 30.

the second selected value => FLOOR(col_a - col_b - col_c)equal FLOOR(36.3- 0 -6.3)will result in 29, but I expect30

Why do these samples get two different values?

Answer 1

This is a known issue in MySQL when using doubleor types floatthat are not stored exactly as we see them.

MySQL, , decimal double. , decimal(10,2) :

SQLFiddle

Answer 2

, , .

select 36.3 - 0 - 6.3

--Produces
30.0

, . :

select 36.3E0 - 0E0 - 6.3E0

--Produces
29.999999999999996

, , 29 .

https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

, . .

https://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html

, SQL, .

, .

Answer 3

, (-6,3) 7. 29.

https://msdn.microsoft.com/en-us/library/ms178531.aspx

Answer 4

IEEE 754 float double.

36.3 , double, '36.29999923706055' 6.3 → '6.300000190734863'

You can convert here Link 1 or Link 2

Now the result col_a - col_b - col_cis '29 .99999904632569 '. Now you have applied the floor on it, which will give you the result "29"

FLOOR () returns the largest integer value that does not exceed the number specified as an argument.

floor (col_a - col_b - col_c)

Returns the output field (29.99999904632569), which gives you the answer → 29

As Tim said, you should use the decimal type or use the query below.

        SELECT FLOOR(36.3- 0 -6.3),(col_a - col_b - col_c) 
        AS calc, col_a, col_b, col_c 
        FROM table_name LIMIT 1;

Output: 30