2

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

user3033467
  • 1,078
  • 15
  • 24
  • It is because you are using float type. Use decimal instead. Read this: http://stackoverflow.com/questions/5150274/difference-between-float-and-decimal-data-type – vaso123 Sep 02 '16 at 13:41
  • @karacsi_maci I probably will do it, and actually did talk about it in my question, but I don't understand why using a CASE is causing this inaccuracy – user3033467 Sep 02 '16 at 13:46

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

The value 815.35 has no exact representation as IEEE-754 float. The value that is actually stored in a float field is an approximation that depends on the number of bits used for the representation.

When you use single-precision float, the value becomes 815.3499755859375, which is what you see printed when you run the query. You can compute the representation that you get with an IEEE-754 calculator (for example, this one).

In order to avoid representation differences like this, use decimal data ty[e to represent values that need precise decimal representation, such as amounts of money.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523