MySQL Bugs: #327: Bug in fulltext search result using wildchar

Bug #327	Bug in fulltext search result using wildchar
Submitted:	26 Apr 2003 6:15	Modified:	18 Nov 2004 20:54
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Fulltext search returns no result when a wildchar is used inside a particular query.

How to repeat:
Get the table at ftp://support.mysql.com/pub/mysql/secret/fulltextbug.tar.gz

This query works :

SELECT id_stock,prix,ref_mag,marque,magasin,dispo FROM stocks WHERE stocks.maj=1 AND (MATCH (ref_mag) AGAINST ('+a70 +plus' IN BOOLEAN MODE)) ORDER BY prix ASC;

+----------+--------+------------------------------------------------------------------------------------------------------------------+--------+---------+-------------+
| id_stock | prix   | ref_mag                                                                                                          | marque | magasin | dispo       |
+----------+--------+------------------------------------------------------------------------------------------------------------------+--------+---------+-------------+
|   266114 |  43.56 | Microsoft FREELANCER 1.00 DVD CASE STREET DATE 28/05 fr (win-32)[Ref.A70-00034]                                  |        |      10 | Voir site   |
<cut>
|   260118 | 499.00 | Photo numérique CANON Powershot A70+                                                                             |        |      17 | Voir site   |
+----------+--------+------------------------------------------------------------------------------------------------------------------+--------+---------+-------------+
18 rows in set (0.00 sec)

However if a wildchar is added :

SELECT id_stock,prix,ref_mag,marque,magasin,dispo FROM stocks WHERE stocks.maj=1 AND (MATCH (ref_mag) AGAINST ('+a70* +plus*' IN BOOLEAN MODE)) ORDER BY prix ASC;
Empty set (0.00 sec)

This should return more result than the first query.

Not a bug per se.
You may have noticed that some rows do not contain the word "plus"
It's because it's a stopword.
So, when you're searching for "+plus*" no word "plus" is matched as it's stripped from the row text before matching takes place. ("plusplus" would match "+plus*" though).

The real fix is to make the search IN BOOLEAN MODE to ignore stopwords at all. But it's not a small thing, internal stopword handling should be rewritten completely for this to happen.
It's in TODO, of course.

Workaround - and a proper thing to do for you - is to use new ft_stopword_file mysdqld variable (command-line switch). Set it to the list of stopwords you want to use or to /dev/null to have no stopwords.

I've noticed the same problem.  For me it appears to be related to using the +,*, and () operators together.  For example:

mysql> select count(*) from company where match (description) against ('(+power +tools)' in boolean mode);
+----------+
| count(*) |
+----------+
|     4317 |
+----------+

mysql> select count(*) from company where match (description) against ('+power* +tools*' in boolean mode);
+----------+
| count(*) |
+----------+
|     4396 |
+----------+
1 row in set (0.77 sec)

Combine all these operators together and you get nothing:

mysql> select count(*) from company where match (description) against ('(+power* +tools*)' in boolean mode);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

I checked out the list of stopwords at 
http://dev.mysql.com/tech-resources/articles/full-text-revealed.html

Neither of the words were on the list.  I'm using v 4.0.18.

I don't think it's the same problem.
Please, report it separately, and try to provide a repeatable test case.

Hi Sergei, 

I've just reproduced it quite easily on 4.1.7, I will fill a new bug report for Tom ;)

  Jocelyn

Hi Tom,

I've just opened

http://bugs.mysql.com/bug.php?id=6705

for your bug.

Regards,
  Jocelyn