MySQL Bugs: #78048: INNODB Full text Case sensitive not working

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

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)

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

Hello!

Thank you for the report.

Thanks,
Umesh

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.

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.

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.

Hello Sveta,

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

Regards,
Nisha.

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.