|
|
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)?
Navigation:
[Reply to this message]
|