Bug #78048 INNODB Full text Case sensitive not working
Submitted: 13 Aug 2015 6:06 Modified: 5 Oct 2017 18:18
Reporter: NA NA Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.6.26 , 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: case-sensitive, fulltext, innodb

[13 Aug 2015 6:06] NA NA
Description:
The InnoDB fulltext search returns case insensitive matches, regardless of collation

I ran the exact same queries on InnoDB and MyISAM tables with same data, same table structure and everything the same except the table engine and it showed the correct results (case sensitive)  with MyISAM, but showed incorrect results with InnoDB , tested on both windows 7 and linux (centos)

How to repeat:
CREATE TABLE `test_innodb` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_cs',
	PRIMARY KEY (`id`),
	FULLTEXT INDEX `str` (`str`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

CREATE TABLE `test_myisam` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_cs',
	PRIMARY KEY (`id`),
	FULLTEXT INDEX `str` (`str`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=MyISAM;

INSERT INTO test_myisam(str) VALUES ('abcd'),('ABCD');

INSERT INTO test_innodb(str) VALUES ('abcd'),('ABCD');

mysql>  SELECT * FROM test_innodb where match(str) AGAINST ('abcd' IN BOOLEAN MODE);
+----+------+
| id | str  |
+----+------+
|  1 | abcd |
|  2 | ABCD |
+----+------+
2 rows in set (0.00 sec)

mysql>  SELECT * FROM test_myisam where match(str) AGAINST ('abcd' IN BOOLEAN MODE);
+----+------+
| id | str  |
+----+------+
|  1 | abcd |
+----+------+
1 row in set (0.01 sec)
[13 Aug 2015 6:17] NA NA
I don't know if relevant but this affects all modes, IN BOOLEAN, Natural, etc. and even double quotes it seems like the collation mode is not checked and just skips it and assumes case insensitive
[13 Aug 2015 7:04] Umesh Shastry
Hello!

Thank you for the report.

Thanks,
Umesh
[26 May 2017 23:14] Sveta Smirnova
Proposed fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug78048.patch (text/x-patch), 3.15 KiB.

[26 May 2017 23:15] Sveta Smirnova
test case for MTR

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug78048.test (application/octet-stream, text), 3.23 KiB.

[26 May 2017 23:16] Sveta Smirnova
Result file for MTR test

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug78048.result (application/octet-stream, text), 4.21 KiB.

[5 Oct 2017 3:12] Nisha Gopalakrishnan
Hello Sveta,

Thanks for your contribution. It was a good reference
point to solve the issue.

Regards,
Nisha.
[5 Oct 2017 18:18] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.6.39, 5.7.21, 8.0.4 release, and here's the changelog entry:

Full-text search on indexed columns that use a binary collation did not
return case sensitive matches. 

Thank you for the bug report.