I have a simple float column, that doesn't yield the correct value when selected via a
SELECT my_column FROM my_table LIMIT 1;
SELECT (CASE WHEN true THEN my_column ELSE 0 END) AS my_column FROM my_table LIMIT 1;
I think @dasblinkenlight has explained the underlying issue with the representation. Your question is also about the
CASE expression returns a single type. MySQL has to decide on the type when the query is compiled.
CASE is combining two different types, a
float and an
int. I believe that this should be returning a
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.