MySQL Bugs: #77054: Fulltext search with wildcard and words with apostrophe (‘) works incorrectly

Bug #77054	Fulltext search with wildcard and words with apostrophe (‘) works incorrectly
Submitted:	16 May 2015 3:23	Modified:	3 Dec 2015 9:10
Reporter:	Mick Pope	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.6.24, 5.6.29, 5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Full-text search INNODB BOOLEAN Apostrophe wildcard

Description:
When you perform a full-text search IN BOOLEAN MODE for words using a wildcard and the word contains an apostrophe (e.g. D’Tan*) incorrect results are returned.  This is the case for both MyISAM and InnoDB tables.  Example result for search on ‘d\’tan*’ would be D’Tandoor and Tanglewood.  I would expect only D’Tandoor.

How to repeat:
/* Create the MyISAM table */
CREATE TABLE myisam_tbl(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(64),
PRIMARY KEY (id),
FULLTEXT (name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/* Add records */
INSERT INTO myisam_tbl (name) VALUES ('D\'Tandoor');
INSERT INTO myisam_tbl (name) VALUES ('Tanglewood');

/* Perform search with varying syntax */
SELECT * FROM myisam_tbl WHERE MATCH(name) AGAINST ('"d\'tandoor*"' IN BOOLEAN MODE);
SELECT * FROM myisam_tbl WHERE MATCH(name) AGAINST ('"d\'tan*"' IN BOOLEAN MODE);
SELECT * FROM myisam_tbl WHERE MATCH(name) AGAINST ('d\'tan*' IN BOOLEAN MODE);
SELECT * FROM myisam_tbl WHERE MATCH(name) AGAINST ("d\'tan*" IN BOOLEAN MODE);
SELECT * FROM myisam_tbl WHERE MATCH(name) AGAINST ("d'tan*" IN BOOLEAN MODE);

/* Create the InnoDB table */
CREATE TABLE innodb_tbl(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(64),
PRIMARY KEY (id),
FULLTEXT (name)
) ENGINE=innodb DEFAULT CHARSET=utf8;

/* Add records */
INSERT INTO innodb_tbl (name) VALUES ('D\'Tandoor');
INSERT INTO innodb_tbl (name) VALUES ('Tanglewood');

/* Perform search with varying syntax */
SELECT * FROM innodb_tbl WHERE MATCH(name) AGAINST ('"d\'tandoor*"' IN BOOLEAN MODE);
SELECT * FROM innodb_tbl WHERE MATCH(name) AGAINST ('"d\'tan*"' IN BOOLEAN MODE);
SELECT * FROM innodb_tbl WHERE MATCH(name) AGAINST ('d\'tan*' IN BOOLEAN MODE);
SELECT * FROM innodb_tbl WHERE MATCH(name) AGAINST ("d\'tan*" IN BOOLEAN MODE);
SELECT * FROM innodb_tbl WHERE MATCH(name) AGAINST ("d'tan*" IN BOOLEAN MODE);

Hello Mick Pope,

Thank you for the report and test case.
Observed this with 5.6.29, 5.7.11 builds.

Thanks,
Umesh

Tried the suggestion here:
http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html

Successfully created the "latin1_fulltext_ci" collation, restart mysql service, created the sample table, and inserted the rows. But when I try the match against query, it returns an error:
ERROR 1064 (42000): syntax error, unexpected '-'

I also have tried this for names with apostrophes and have tried switching the collation to lowercase for apostrophe (0x27) and, while there is not an error, the search always treats the apostrophe as a word break