enterprise-support team mailing list archive

[Sveta Smirnova <sveta@xxxxxxxxxxxxx>]

Public bug reported:

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>----

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: Confirmed

** Affects: percona-server/5.6
     Importance: Undecided
         Status: New

** Affects: percona-server/5.7
     Importance: Undecided
         Status: Confirmed


** Tags: i185729

** Also affects: percona-server/5.7
   Importance: Undecided
       Status: Confirmed

** Also affects: percona-server/5.6
   Importance: Undecided
       Status: New

** Bug watch added: MySQL Bug System #78048
   http://bugs.mysql.com/bug.php?id=78048

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=78048
   Importance: Unknown
       Status: Unknown

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1689264

Title:
  INNODB Full text Case sensitive not working

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1689264/+subscriptions