Bug #92142 Having different result set with a full text index on different MySQL versions.
Submitted: 23 Aug 2018 7:30 Modified: 24 Aug 2018 15:05
Reporter: Chaitanya Tondlekar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.7.19 and 5.7.21 OS:Other (AWS RDS)
Assigned to: CPU Architecture:Other
Tags: full text search, utf8_bin collation, utf8_general_ci collation

[23 Aug 2018 7:30] Chaitanya Tondlekar
Description:
Hello,

We are trying to perform a Full-Text search for given phrase using the operator double quote("). 
In version 5.7.19 for collation utf8_bin the results are returned as expected but in 5.7.21 it does not return any relevant results especially if there is a space between the searched word.
But for collation utf8_general_ci  it works fine in both the mentioned versions.

How to repeat:
CREATE TABLE `data_sample` (
 `text_sample` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 FULLTEXT KEY `ft_text_sample` (`text_sample`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO data_sample VALUES ('Telefe SD'),('Telefé SD'),('Telefé'),('Telefé Channel1'),('Telefe Channel1'),('textsample1'),('textsample2'),('textsample test')

SELECT * FROM data_sample
WHERE MATCH(text_sample) AGAINST ('"Telefe Channel1"');

SELECT * FROM data_sample
WHERE MATCH(text_sample) AGAINST ('"Telefe"');

ALTER TABLE data_sample MODIFY text_sample VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci;

SELECT * FROM data_sample
WHERE MATCH(text_sample) AGAINST ('"Telefe Channel1"');

Suggested fix:
We should be able to retrieve data using double quotes operator in utf8_bin collation in MySQL 5.7.21.
[24 Aug 2018 15:05] Sinisa Milivojevic
Hi,

I have ran your 100 % exact test case. 

With 5.7.19 I get this output:

text_sample
Telefe Channel1
text_sample
Telefe SD
Telefe Channel1
text_sample
Telefé Channel1
Telefe Channel1

With 5.7.22 I get this output:
 
text_sample
Telefe SD
Telefe Channel1
text_sample
Telefé Channel1
Telefe Channel1

Verified as reported.