Posted by Lazeez Jiddan on 09/01/06 15:26

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

[Back to original message]