Bug #1030 Bug with fulltext search and numbers
Submitted: 11 Aug 2003 8:52 Modified: 11 Aug 2003 10:57
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.15 (latest bk pull) OS:Linux (Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any

[11 Aug 2003 8:52] jocelyn fournier
Description:
Hi,

Fulltext search seems to not work properly with numbers.

Regards,
  Jocelyn

How to repeat:
CREATE TABLE `fiches_myisam` (
  `id_produit` smallint(5) unsigned NOT NULL default '0',
  `liste_options` text NOT NULL,
  PRIMARY KEY  (`id_produit`),
  FULLTEXT KEY `liste_options` (`liste_options`)
) TYPE=MyISAM;

INSERT INTO fiches_myisam VALUES ('6280','223 226');

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('226');
Empty set (0.00 sec)

=> This should return a result

INSERT INTO fiches_myisam VALUES ('6281','226');

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('226');
Empty set (0.00 sec)

=> Still no result

INSERT INTO fiches_myisam VALUES ('6282','223');

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('226');
Empty set (0.01 sec)

INSERT INTO fiches_myisam VALUES ('6283','223');

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('226');
Empty set (0.01 sec)

INSERT INTO fiches_myisam VALUES ('6284','223 223');

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('226');
+------------+
| id_produit |
+------------+
|       6281 |
|       6280 |
+------------+

=> Will the latest insert (which doesn't contain '226'), the query now works ?!

However :

SELECT id_produit FROM fiches_myisam WHERE MATCH ( fiches_myisam.liste_options ) AGAINST ('223');
Empty set (0.00 sec)

=> still produce no result for '223'

(PS : My ft_min_word_len is set to 1, and ft_stopword_file is set to /dev/null)
[11 Aug 2003 10:57] jocelyn fournier
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Sorry about this bug report, I just forget to use IN BOOLEAN MODE ;)

Regards,
  Jocelyn