Posted by Lazeez Jiddan on 09/02/06 13:44

On Fri, 1 Sep 2006 15:31:33 -0400, Andy Hassall wrote
(in article <pb2hf2t9uaj55862mm7ogi9bhkrkkbm9k2@4ax.com>):

> On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
> <lazeez@storiesonline.removethispart.net> wrote:
>
>> Is mysql unable to store 9.7 as 9.7 float and uses some kind of
>> approximation?
>
> Yes, this is a common issue on computers. The IEEE standard for
> floating point numbers only represents a subset of numbers; some
> precision is traded for range.
>
> The closest you can get to 9.7 is 9.699999809265137.
> The closest you can get to 9.8 is 9.800000190734863.
>
> See http://www.h-schmidt.net/FloatApplet/IEEE754.html

I thought that was the issue, however, I also thought that, when
selecting from a float type column MySQL would convert the comparison
value also to float and would end up with the same value.

So if I store 9.7 in a float column (MySQL actually stores
9.699999809265137) and then I search that column for 9.7, then MySQL
would convert the 9.7 searched for to a float and would get
9.699999809265137, and comparing them both would get a match.

Obviously, I was mistaken.

>> if so, is there a way to get the results that I'm looking for
>> without some stupid workaround like changing 9.7 (or whatever the
>> score sought) to 9.69 (or whatever is close)?
>
> Multiply all your values by 100 and store in an integer format.
> Divide them back down by 100 for display.

Actually, I found something better (it doesn't need the constant and
repeated conversion on the fly; the site is quite busy). Storing the
values in a char(4) column. It's indexed either way, so it give back the
expected results and I detected no slow down.

If somebody knows of a drawback to using a char type column that I
overlooked, I would love to hear it.

Thank you for your reply.

[Back to original message]