Bug #34172 The string '2007' can not be found in fulltext search
Submitted: 30 Jan 2008 18:31 Modified: 31 Jan 2008 11:13
Reporter: Jean-Pierre Praz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.1.22-rc OS:Linux (Fedora 6)
Assigned to: CPU Architecture:Any

[30 Jan 2008 18:31] Jean-Pierre Praz
Description:
In a table with fulltext-index on the column "co_object_data" the string '2007' can not be found like this:

SELECT * FROM `cO_publications` p WHERE match(p.co_object_data) against('2007');

Replacing 2007 by 2006 or 2008 in the same place will lead to the row being found like this:

SELECT * FROM `cO_publications` p WHERE match(p.co_object_data) against('2006');

OR

SELECT * FROM `cO_publications` p WHERE match(p.co_object_data) against('2008');

In my.cnf there is the following:
ft_min_word_len = 3
ft_stopword_file =

and

SHOW VARIABLES;

shows that the above values are applied on startup. The table was repaired and reindexed many times but it did not help. The same issue appears on two different machines, both running 5.1.22-rc.

How to repeat:
Create a DB, create a table with a column "co_object_data" of type longtext, create a fulltext-index of that column, insert the value 2007 in it and do

SELECT * FROM `cO_publications` p WHERE match(p.co_object_data) against('2007');

no match.

Then modify the record and insert 2006 instead of 2007 and do

SELECT * FROM `cO_publications` p WHERE match(p.co_object_data) against('2006');

one match.

Suggested fix:
?
[30 Jan 2008 18:56] Paul Dubois
How many rows are in the table, and in how many of them does 2007 occur? If it's half or more, 2007 will be considered "common" and ignored.
[31 Jan 2008 9:05] Jean-Pierre Praz
The rows which match '2007' are more than half of the rows.

So, what I discribed is not a bug but a feature :-)

By the way, can this feature be configured to behave different?
[31 Jan 2008 11:13] Valeriy Kravchuk
This is not a bug. Please, read http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html.

Use boolean search mode as a workaround, see http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html for the details.