MySQL Bugs: #69216: Fulltext search using words with apostrophe (') does not work on INNODB tables

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

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

Hello Chito,

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

Thanks,
Umesh

Noted in 5.6.13, 5.7.2 changelogs.

Full-text search on InnoDB tables failed on searches for words
containing apostrophes.

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);