0

I am trying to search for a string with single quote against a FullText index:

select * 
from gems 
where MATCH (gems.title, gems.descr) AGAINST ('"winter\'s discontent"' IN BOOLEAN MODE)

The answer in this post suggests that it should work, but it does not. When I changed that example from MyIsam to InnoDB, it no longer worked. Even in MyIsam, the query above would return a match on 'winter'

Any ideas?

I am using mySql 5.6.11 InnoDB

A sqlFiddle can be found here and it also does not work. It contains the following:

CREATE TABLE gems 
    (
     id int auto_increment primary key, 
     title varchar(20), 
     descr varchar(100),
     FULLTEXT KEY `ft_title_descr` (`title`,`descr`)      
    );


INSERT INTO gems
(title, descr)
VALUES
('Post 1', 'Once upon a time'),
('Post 2', 'My winter\'s discontent');
Community
  • 1
  • 1
mseifert
  • 5,390
  • 9
  • 38
  • 100
  • Hmm interesting it could be something related to the engine try creating the table as myisam and here I created the fiddle it works now http://www.sqlfiddle.com/#!9/828a9/1, and do not use double quote I have removed it. – Abhik Chakraborty Nov 16 '14 at 09:36
  • The reason for the double quote is that it is a phrase and may contain multiple words. I am just simplifying it in the example. MyIsam will work - sort of (it also matches "winter") - but not an option for this table. – mseifert Nov 16 '14 at 09:54

1 Answers1

0

Did some more Googling - seems this was a reported MySQL bug. I upgraded my mySQL version to 5.6.20 and this solved the problem.

mseifert
  • 5,390
  • 9
  • 38
  • 100