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

[14 Jan 2019 17:44] Greg Allen
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);
[14 Jan 2019 20:05] MySQL Verification Team
Thank you for the bug report.
[14 Jan 2019 21:53] Greg Allen
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.