Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-1090

LP #1689264: INNODB Full text Case sensitive not working

    Details

      Description

      **Reported in Launchpad by Sveta Smirnova last update 09-10-2017 14:05:27

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

        Attachments

          Expenses

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: