Bug #69216 Fulltext search using words with apostrophe (') does not work on INNODB tables
Submitted: 13 May 2013 14:03 Modified: 6 Aug 2013 2:25
Reporter: Chito Angeles Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6.13 OS:Windows (Win 7)
Assigned to: CPU Architecture:Any
Tags: full-text search, innodb

[13 May 2013 14:03] Chito Angeles
Description:
When you search for words with apostrophe (e.g., teacher's) using full-text search IN BOOLEAN MODE on an INNODB table, it does not return anything, but works fine on MyISAM table. 

Search syntax: SELECT * FROM catalog where match (title) against ('"teacher\'s handbook"' IN BOOLEAN MODE);

How to repeat:
/* Create the table */
CREATE TABLE `catalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_ftx` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* Add records */
INSERT INTO catalog(`title`) VALUES 
  ('A teacher\'s and textbook writers handbook on Japan'),
  ('A teacher\'s handbook in health and science for grade III');

/* Execute full-text search */
SELECT * FROM catalog where match (title) against ('"teacher\'s handbook"' IN BOOLEAN MODE);

Message: 0 row(s) returned

/* Try changing storage engine to MyISAM */
ALTER TABLE `catalog` ENGINE = MyISAM;

/* Repeat the full-text search */
SELECT * FROM catalog where match (title) against ('"teacher\'s handbook"' IN BOOLEAN MODE);

+----+----------------------------------------------------------+
| id | title                                                    |
+----+----------------------------------------------------------+
| 2  + A teacher's handbook in health and science for grade III |
+---------------------------------------------------------------+
Message: 1 row(s) returned
[13 May 2013 16:53] MySQL Verification Team
Hello Chito,

Thank you for the report.
Verified as described on reported and later versions.

Thanks,
Umesh
[10 Jun 2013 17:55] Paul DuBois
Noted in 5.6.13, 5.7.2 changelogs.

Full-text search on InnoDB tables failed on searches for words
containing apostrophes.
[6 Aug 2013 2:25] Chito Angeles
Fixed the "phrase" search in 5.6.13 but still fails when using Boolean Operators (+ or -), example:

SELECT * FROM catalog where match (title) against ('+teacher\'s +handbook' IN BOOLEAN MODE);