MySQL bug? or user error?

I've come across what I believe is a bug in MySQL or some schizophrenic
behavior in it.

I tested the following on three versions of mysql:

Linux, MySQL 5.0.18
Mac MySQL 5.0.15 64 bit
Mac MySQL 5.0.24a 64 bit

I have a table of articles that have article title, article score and
article number of votes among other columns.

I try to select articles with a certain score and a certain number of
votes:

articleScore column type is float.

mysql> select articleName, articleScore from articles where articleScore
> 9.8 and articleVotes > 500 order by articleScore desc;

+--------------------------------+-------------+
| articleName |articleScore |
+--------------------------------+-------------+
| Greenies | 9.84 |
| Happy Harry Last of the Hoboes | 9.83 |
| Protect & Serve | 9.83 |
| William Redman Carter | 9.83 |
| What The Future May Bring | 9.81 |
| Ellen Trilby | 9.81 |
| Oscar Meyers | 9.81 |
| General Sid | 9.8 |
| The Orphanage Blues | 9.8 |
| Wagons Ho! - The Early Years | 9.8 |
| Murder Isle | 9.8 |
| Harry and Amy | 9.8 |
| Banner Year | 9.8 |
+--------------------------------+-------------+
13 rows in set (0.00 sec)

Now the last 6 rows (9.8) shouldn't be there, right? But, MySQL returns
them.

Now the following is completely contradictory to the first statement:

mysql> select articleName, articleScore from articles where articleScore
>= 9.7 and articleVotes > 500 order by articleScore desc;

+---------------------------------------+-------------+
| articleName |articleScore |
+---------------------------------------+-------------+
| Greenies | 9.84 |
| Happy Harry Last of the Hoboes | 9.83 |

[snip]

| Twice Lucky II: Time for a Change | 9.71 |
| John and Argent | 9.71 |
+---------------------------------------+-------------+
94 rows in set (0.00 sec)

mysql> select articleName, articleScore from articles where articleScore
= 9.7 and articleVotes > 500 order by articleScore desc;
Empty set (0.00 sec)

However:

mysql> select articleName, articleScore from articles where articleScore
> 9.69 and articleScore < 9.71 order by articleScore desc;

+-----------------------------+-------------+
| articleName |articleScore |
+-----------------------------+-------------+
| Wraith | 9.7 |
| The Last Cowboy | 9.7 |
| Solo Camping | 9.7 |
| The Real Estate Connection | 9.7 |
| Lucky Stiff | 9.7 |
| The Secrets of Kings | 9.7 |
| Twice Lucky III: Divergence | 9.7 |
| Atlantis | 9.7 |
| Transcending the Role | 9.7 |
+-----------------------------+-------------+
9 rows in set (0.00 sec)

So, can anybody explain the above?

Is mysql unable to store 9.7 as 9.7 float and uses some kind of
approximation?

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)?

Re: MySQL bug? or user error?

On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
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

>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.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Re: MySQL bug? or user error?

On Fri, 1 Sep 2006 15:31:33 -0400, Andy Hassall wrote
(in article ):

> On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
> 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.

Re: MySQL bug? or user error?

Lazeez Jiddan wrote:
> On Fri, 1 Sep 2006 15:31:33 -0400, Andy Hassall wrote
> (in article ):
>
> > On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
> > 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.

The drawback is that you're storing numeric data using a datatype that
is meant for storing character data. Why not use an exact numeric type
like decimal?

Re: MySQL bug? or user error?

On Mon, 4 Sep 2006 11:42:02 -0400, ZeldorBlat wrote
(in article <1157384522.367120.232690@b28g2000cwb.googlegroups.com>):

>
> Lazeez Jiddan wrote:
>> On Fri, 1 Sep 2006 15:31:33 -0400, Andy Hassall wrote
>> (in article ):
>>
>>> On Fri, 1 Sep 2006 11:26:09 -0400, Lazeez Jiddan
>>> 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.
>
> The drawback is that you're storing numeric data using a datatype that
> is meant for storing character data. Why not use an exact numeric type
> like decimal?

Thanks, that actually makes more sense.

I didn't notice the DECIMAL column type until you mentioned it.

It works very well.

Although, with char(4) it was the same. No calculations were ever going
to be applied to the value read from that column. The column was simply a
store and search and display only.