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

Bug #69932	Fulltext search using words with apostrophe (') does not work on INNODB tables
Submitted:	6 Aug 2013 4:01	Modified:	6 Sep 2013 16:11
Reporter:	Chito Angeles	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.6.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Full-text search INNODB BOOLEAN Apostrophe

Description:
This report is related to Bug #69216.

Full-text search using words with apostrophe (') does not work on INNODB tables with Full-text Index using Boolean operators.

The "phrase" search was fixed in Version 5.6.13, but fails when using Boolean Operators (+ or -).

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

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

Message: 1 row(s) returned

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

Message: 0 row(s) returned

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > create database dx;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > use dx
Database changed
mysql 5.6 > 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;
Query OK, 0 rows affected (3.67 sec)

mysql 5.6 > /* Add records */
mysql 5.6 > 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');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > 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 |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > SELECT * FROM catalog where match (title) against ('+teacher\'s +handbook' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql 5.6 > drop database dx;
Query OK, 1 row affected (0.42 sec)

mysql 5.6 > create database dx;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > use dx
Database changed
mysql 5.6 > CREATE TABLE `catalog` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `title` text NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   FULLTEXT KEY `title_ftx` (`title`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql 5.6 > 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');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > 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 |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > SELECT * FROM catalog where match (title) against ('+teacher\'s +handbook' IN BOOLEAN MODE);
+----+----------------------------------------------------------+
| id | title                                                    |
+----+----------------------------------------------------------+
|  1 | A teacher's and textbook writers handbook on Japan       |
|  2 | A teacher's handbook in health and science for grade III |
+----+----------------------------------------------------------+
2 rows in set (0.00 sec)

Noted in 5.6.14, 5.7.3 changelogs.

Full-text search on InnoDB tables failed on searches for words
containing apostrophes when using boolean operators.

Posted by developer:
 
The changelog entry was updated as follows:

Full-text search on InnoDB tables failed on searches for words containing
apostrophes when using boolean operators. 

The innodb_ft_max_token_size maximum value was incorrectly defined as 252,
which is the maximum byte length. The maximum innodb_ft_max_token_size
value is now 84, which is the maximum character length. 

The change to innodb_ft_max_token_size is referenced in Bug #19595362.