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

Bug #93924	Fulltext search using words with apostrophe (') does not work on INNODB tables
Submitted:	14 Jan 2019 17:44	Modified:	14 Jan 2019 21:53
Reporter:	Greg Allen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.6/5.7/8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I think this is another instance of bug #69932 (https://bugs.mysql.com/bug.php?id=69932). I'm not sure that bug was actually fixed.

See the how to repeat section below.

The first query returns zero records (I would expect one) and the second query returns two records (I would expect one).

How to repeat:
DROP TABLE IF EXISTS catalog;

CREATE TABLE `catalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name_ftx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO catalog(`name`) VALUES
  ('Lori O\'Brien'),
  ('Lori Brien');

SELECT * FROM catalog where match (name) against ('+O\'Brien +Lori' IN BOOLEAN MODE);

SELECT * FROM catalog where match (name) against ('+Brien +Lori' IN BOOLEAN MODE);

Thank you for the bug report.

Here's another example.

DROP TABLE IF EXISTS catalog;

CREATE TABLE `catalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name_ftx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO catalog(`name`) VALUES
  ('A teacher\'s and textbook writers handbook on Japan'),
  ('A teacher\'s handbook in health and science for grade III'),
  ('A teacher may pass out a handbook');
  
SELECT * FROM catalog where match (name) against ('+teacher\'s +handbook' IN BOOLEAN MODE);

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

In both queries all three records are returned. I would expect the first query to return the first two records and the second query to return the last.

To me it seems as though the apostrophe is acting like a word break.  And I think this is why it appears the example from https://bugs.mysql.com/bug.php?id=69932 passes.