Bug #37245 Full text search problem
Submitted: 6 Jun 2008 13:18 Modified: 28 Jan 2009 22:00
Reporter: Christophe Turnar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.0.51a OS:Linux
Assigned to: Sergey Vojtovich
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[6 Jun 2008 13:18] Christophe Turnar
Description:
mysql full text search engine doesn't find words in boolean mode in some cases 

How to repeat:
my.cnf:
[mysqld]
ft_min_word_len=1

 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

CREATE TABLE `list` (
  `id` tinyint(3) unsigned NOT NULL default '0',
  `text` varchar(50) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `index_list` (`text`)
) ENGINE=MyISAM /*!40100 DEFAULT CHARSET=utf8*/;

LOCK TABLES `list` WRITE;
/*!40000 ALTER TABLE `list` DISABLE KEYS*/;
INSERT INTO `list` (`id`, `text`) VALUES
	(3,'26'),
	(1,'4'),
	(2,'491'),
	(4,'2'),
	(5,'2');
/*!40000 ALTER TABLE `list` ENABLE KEYS*/;
UNLOCK TABLES;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;

select id, text from list where match(text) against('4* 2* 27*' in boolean mode);

+----+------+
| id | text |
+----+------+
|  1 | 4    |
|  2 | 491  |
|  4 | 2    |
|  5 | 2    |
|  3 | 26   |
+----+------+
5 rows in set (0.00 sec)

No error
----------------------------------------------
select id, text from list where match(text) against('4* 2* 25*' in boolean mode);

+----+------+
| id | text |
+----+------+
|  1 | 4    |
|  2 | 491  |
|  4 | 2    |
|  5 | 2    |
+----+------+
4 rows in set (0.00 sec)

Error : id 3 is missing!
[6 Jun 2008 16:36] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Oct 2008 18:50] Sergey Vojtovich
Analysing and implementing a fix took about 3 hours, thus E2.
The fix is pretty trivial and affects only fulltext boolean mode searches with truncation operator, thus R2.
[13 Nov 2008 15:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58665

2711 Sergey Vojtovich	2008-11-13
      BUG#37245 - Full text search problem
      
      Certain boolean mode queries with truncation operator may
      may not return matching records and calculate relevancy
      incorrectly.
      
      The problem was that we used binary search to determine
      if the word with truncation operator is in the query.
      
      Fixed by implementing a separate function, which is intended
      to handle queries with truncation operator.
[28 Nov 2008 15:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60186

2730 Sergey Vojtovich	2008-11-28
      BUG#37245 - Full text search problem
      
      Certain boolean mode queries with truncation operator did
      not return matching records and calculate relevancy
      incorrectly.
[6 Jan 2009 13:56] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[9 Jan 2009 1:23] Paul Dubois
Noted in 5.0.76 changelog.

Certain boolean-mode FULLTEXT searches that used the truncation
operator did not return matching records and calculated relevance
incorrectly. 

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:38] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[15 Jan 2009 16:30] Paul Dubois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:25] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:03] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 14:52] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:09] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:56] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[28 Jan 2009 22:00] Paul Dubois
Noted in 6.0.10 changelog.