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

          relates to

          Task - A task that needs to be done. MDEV-9711 NO PAD collations

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            cmattick Christian Mattick created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            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).             		Steps to reproduce:
            {code:sql}
            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');
            {code}
            Compare result of
            {code:sql}
            SELECT * FROM netcon_fahrzeug.aaa WHERE a LIKE '123%'
            {code}
            to
            {code:sql}
            SELECT * FROM netcon_fahrzeug.aaa IGNORE INDEX (a) WHERE a LIKE '123%'
            {code}
            The results shouldn't be different, the first query has an empty result which is not expected.
            {code:sql}
            SELECT * FROM netcon_fahrzeug.aaa WHERE b LIKE '123%'
            {code}
            works as expected though so this is happening with the collation utf8mb4_unicode_nopad_ci (and maybe others).
            elenst Elena Stepanova made changes -
            Component/s Character Sets [ 10801 ]
            Fix Version/s 10.2 [ 14601 ]
            Assignee Alexander Barkov [ bar ]
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2017-12-08 09:20:53.0 2017-12-08 09:20:53.625
            bar Alexander Barkov made changes -
            Fix Version/s 10.2.12 [ 22810 ]
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Confirmed [ 10101 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 83740 ] MariaDB v4 [ 153159 ]

            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.