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

    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: