MySQL FLOOR function 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

---

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

second selected value => FLOOR(col_a - col_b - col_c) which is equals to FLOOR(36.3- 0 -6.3) result in 29 but i am expecting 30

Why these selects getting two different values?

Answer 1

This is a known problem in MySQL when using the double or float type, which are not stored internally exactly as we see them.

If you read the MySQL documentation, you will find a suggested workaround which is to use decimal instead of double. You can see in the following Fiddle that all is working as expected when using decimal(10,2) as your column types:

SQLFiddle

Answer 2

The values you put in the select are automatically taken as decimal and that's why the result is correct.

select 36.3 - 0 - 6.3

--Produces
30.0

Floating point types are not stored exactly, so you'll not get the exact results. Try this:

select 36.3E0 - 0E0 - 6.3E0

--Produces
29.999999999999996

and hence floor gives you 29 in the output.

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

Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

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

A floating-point value as written in an SQL statement may not be the same as the value represented internally.

As Tim advised, you should use Decimal type instead.

Answer 3

This is because floor(-6.3) is 7 .Hence it will become 29.

Further details you can check on https://msdn.microsoft.com/en-us/library/ms178531.aspx

Answer 4

Binary floating point it is based on the IEEE 754 standard for float and double.

when you insert value 36.3 into the column which has datatype double then its stores as '36.29999923706055' and for 6.3 - > '6.300000190734863'

You can convert from here Link 1 or Link 2

Now Result col_a - col_b - col_c is '29.99999904632569'. Now you applied floor on it which give you result '29'

FLOOR() returns the largest integer value not greater than a number specified as an argument.

floor (col_a - col_b - col_c)

Returns output floor(29.99999904632569) which give you answer - > 29

As Tim advised, you should use Decimal type instead or use below query.

        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