Description:
I think I've found a pretty interesting bug, FULLTEXT users should be aware
of.
Consider this simple select:
----------------------------------------------------------------------------
SELECT id, price,
MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE) score1,
MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE) score2
FROM products as p
WHERE MATCH(`fulltext`) AGAINST ('hp* l1955*' IN BOOLEAN MODE)
ORDER BY score1 DESC ,price ASC
----------------------------------------------------------------------------
The problem is with `score1` and `score2`, SOMETIMES THESE TWO ARE NOT
EQUAL(this is happening very rarely and only in the first row of the
recordset)!
The problem goes away after removing [,price ASC ] from the ORDER clause
Normaly there should be only one score field, but sometimes it has
incorrect value. The second score2 field is allways correct.
What's interesting is that when score1 value is incorrect ordering by score1 is done correctly, so it tells us that internally the value is ok, but somehow get's wrongly displayed.
my server version: 5.0.24a-Debian_2.dotdeb.0-log
How to repeat:
It is hard to reproduce this bug. Just get any fulltext indexed table and try to do ordering like in the example: first order by score DESC and then by any other field ASC.
Try to use data I have, it is provided below. If it doesn't help do many thousands of selects until score1 != score2
So far I have found 2 records which are allways having this problem, even after recreating the table.
-----------------
1)
-----------------
fulltext field data: "NEC 90GX2 Datori >>> Monitori >>> LCD"
... MATCH(`fulltext`) AGAINST (' nec* 90gx2* ' IN BOOLEAN MODE)
-----------------
2)
-----------------
fulltext field data: "HP L1955 (19") LCD monitors Datoru monitori >>> LCD MONITORI >>> HEWLETT-PACKARD"
...MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE)
Description: I think I've found a pretty interesting bug, FULLTEXT users should be aware of. Consider this simple select: ---------------------------------------------------------------------------- SELECT id, price, MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE) score1, MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE) score2 FROM products as p WHERE MATCH(`fulltext`) AGAINST ('hp* l1955*' IN BOOLEAN MODE) ORDER BY score1 DESC ,price ASC ---------------------------------------------------------------------------- The problem is with `score1` and `score2`, SOMETIMES THESE TWO ARE NOT EQUAL(this is happening very rarely and only in the first row of the recordset)! The problem goes away after removing [,price ASC ] from the ORDER clause Normaly there should be only one score field, but sometimes it has incorrect value. The second score2 field is allways correct. What's interesting is that when score1 value is incorrect ordering by score1 is done correctly, so it tells us that internally the value is ok, but somehow get's wrongly displayed. my server version: 5.0.24a-Debian_2.dotdeb.0-log How to repeat: It is hard to reproduce this bug. Just get any fulltext indexed table and try to do ordering like in the example: first order by score DESC and then by any other field ASC. Try to use data I have, it is provided below. If it doesn't help do many thousands of selects until score1 != score2 So far I have found 2 records which are allways having this problem, even after recreating the table. ----------------- 1) ----------------- fulltext field data: "NEC 90GX2 Datori >>> Monitori >>> LCD" ... MATCH(`fulltext`) AGAINST (' nec* 90gx2* ' IN BOOLEAN MODE) ----------------- 2) ----------------- fulltext field data: "HP L1955 (19") LCD monitors Datoru monitori >>> LCD MONITORI >>> HEWLETT-PACKARD" ...MATCH(`fulltext`) AGAINST (' hp* l1955* ' IN BOOLEAN MODE)