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

Regression, creating a table with full text index and adding it after table creation gives different query results when using that index.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Low
    • Resolution: Unresolved
    • Affects Version/s: 5.7
    • Fix Version/s: None
    • Component/s: None

      Description

      Regression  reported on upstream https://bugs.mysql.com/bug.php?id=94709 .

      The following 2 test scenarios give 1 result each for MySQL versions up to 5.7.20, and only 1st test case returns a result after 5.7.21:

      ----------------------------------------------------------

      use test;

      ----------------------------------------------------------
      ------------------------ 1 result ------------------------

      DROP TABLE IF EXISTS `posts_translations_copy2`;

      CREATE TABLE `posts_translations_copy2` (
      `id` int(11) unsigned NOT NULL,
      `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
      PRIMARY KEY (`id`),
      FULLTEXT KEY `searchablepc` (`content`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      INSERT INTO `posts_translations_copy2` (`id`, `content`)
      VALUES
      (1,'content Spring fling');

      SELECT count
      FROM `test`.`posts_translations_copy2` `PostsTranslations`
      USE INDEX (searchablepc)
      WHERE ((MATCH(PostsTranslations.content) AGAINST('"Spring fling"' in boolean mode)))
      ORDER BY `PostsTranslations`.`id` DESC LIMIT 20 OFFSET 0;

      ----------------------------------------------------------
      ------------------------ 0 result ------------------------

      DROP TABLE IF EXISTS `posts_translations_copy2`;

      CREATE TABLE `posts_translations_copy2` (
      `id` int(11) unsigned NOT NULL,
      `content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      INSERT INTO `posts_translations_copy2` (`id`, `content`)
      VALUES
      (1,'content Spring fling');

      CREATE FULLTEXT INDEX searchablepc ON posts_translations_copy2(content);

      SELECT count
      FROM `test`.`posts_translations_copy2` `PostsTranslations`
      USE INDEX (searchablepc)
      WHERE ((MATCH(PostsTranslations.content) AGAINST('"Spring fling"' in boolean mode)))
      ORDER BY `PostsTranslations`.`id` DESC LIMIT 20 OFFSET 0;
      ------------------------------------------------------------

       

      This is a regression as it changes how query behaves. I suggest returning same result as prior versions for version >= MySQL 5.7.21 , or add a documentation bug showing this incompatible change.

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                carlos.tutte Carlos Tutte
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: