user3033467 user3033467 - 3 months ago 11
MySQL Question

Floating point inaccuracy in case

I have a simple float column, that doesn't yield the correct value when selected via a

CASE
:

SELECT my_column FROM my_table LIMIT 1;
yields 815.35

But
SELECT (CASE WHEN true THEN my_column ELSE 0 END) AS my_column FROM my_table LIMIT 1;
yields 815.3499755859375

Problem is obviously coming from the case and from the ELSE value (using 'test' rather than 0 works as intended, but using an other float does not)

I could solve it by using
ROUND(my_column,2)
, or using a decimal column instead of a float one, but I'd actually want to understand what's happening here

Answer

I think @dasblinkenlight has explained the underlying issue with the representation. Your question is also about the CASE.

A CASE expression returns a single type. MySQL has to decide on the type when the query is compiled.

Your CASE is combining two different types, a float and an int. I believe that this should be returning a float value.

The rules leave me a bit confused as to why anything is happening; after all, a float to a float sounds like a no-op. But, there are two representations for floats, 4-byte and 8-byte. My guess is that your column is stored as a 4-byte float. The SQL engine decides that the CASE expression should return an 8-byte double. The conversion to the double is the cause of your issue.

In fact, this little SQLFiddle confirms this guess. The issue is a conversion to double.

Comments