INNODB Full text Case sensitive not working
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
High
|
Unassigned | |||
5.7 |
Triaged
|
High
|
Unassigned |
Bug Description
Originally reported at https:/
----<q>----
[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_
PRIMARY KEY (`id`),
FULLTEXT INDEX `str` (`str`)
)
COLLATE=
ENGINE=InnoDB;
CREATE TABLE `test_myisam` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`str` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_
PRIMARY KEY (`id`),
FULLTEXT INDEX `str` (`str`)
)
COLLATE=
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)
----</q>----
Upstream bug fixed in 5.6.39, 5.7.21, 8.0.4