MySQL Bugs: #92142: Having different result set with a full text index on different MySQL versions.

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

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.

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.

Any updates on this?

Hi Mr. Tondlekar,

Thank you for your interest.

Nobody can reply to your question, because bug fixing scheduling is unknown, for both of the supported versions 5.7 and 8.0.

Scheduling is done with each of the teams in charge of the category and version. Scheduling is kept only within those teams and changes all the time.

Still failing in 8.0.31