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

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

                Dates

                Created:
                Updated:
                Resolved: