MySQL Bugs: #34172: The string '2007' can not be found in fulltext search

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

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

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.

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?

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.