Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14350

Index use with collation utf8mb4_unicode_nopad_ci on LIKE pattern with wrong results

    XMLWordPrintable

Details

    Description

      Steps to reproduce:

      CREATE TABLE `aaa` (
      	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      	`a` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_nopad_ci',
      	`b` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
      	PRIMARY KEY (`id`),
      	INDEX `a` (`a`),
      	INDEX `b` (`b`)
      )
      COLLATE='utf8mb4_nopad_bin'
      ENGINE=InnoDB
      ;
      INSERT INTO `aaa` VALUES (1, '123', '123');
      

      Compare result of

      SELECT * FROM netcon_fahrzeug.aaa WHERE a LIKE '123%'
      

      to

      SELECT * FROM netcon_fahrzeug.aaa IGNORE INDEX (a) WHERE a LIKE '123%'
      

      The results shouldn't be different, the first query has an empty result which is not expected.

      SELECT * FROM netcon_fahrzeug.aaa WHERE b LIKE '123%'
      

      works as expected though so this is happening with the collation utf8mb4_unicode_nopad_ci (and maybe others).

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              cmattick Christian Mattick
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.