INNODB Full text Case sensitive not working

Bug #1689264 reported by Sveta Smirnova on 2017-05-08
6
This bug affects 1 person
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://bugs.mysql.com/bug.php?id=78048

----<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_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)
----</q>----

tags: added: innodb upstream
Sveta Smirnova (svetasmirnova) wrote :
Sveta Smirnova (svetasmirnova) wrote :
Sveta Smirnova (svetasmirnova) wrote :
tags: added: sfr-79
Sveta Smirnova (svetasmirnova) wrote :

Upstream bug fixed in 5.6.39, 5.7.21, 8.0.4

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1090

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.