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

Different resultset when using prefix index and utf8mb4

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.4, 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.4(EOL)
    • Character Sets, Server
    • Linux

    Description

      Hola MariaDB Team!

      I'm not sure if it's a bug or a feature.

      With a column in utf8mb4, and a prefix on it, the result set is not the same depending on which index is used.
      Once doing some tests, I realized that the collation may change depending if we use a prefix index or not.

      Sample test:

       
      # cat testcase_utf8mb4.sql
      SET NAMES utf8mb4;
      DROP DATABASE if EXISTS db_utf8mb4;
      CREATE DATABASE db_utf8mb4 CHARSET utf8mb4;
      USE db_utf8mb4;
      CREATE TABLE tbl_utf8mb4 (id serial, path varchar(255), KEY(path), KEY path_prefix (path(200))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC charset utf8mb4 collate utf8mb4_unicode_ci;
      INSERT INTO tbl_utf8mb4 (path) VALUES (CONCAT('/',CHAR(4036994689)));
      SELECT * FROM tbl_utf8mb4 WHERE path LIKE '/%' ;
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path) WHERE path LIKE '/%' ;
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path_prefix) WHERE path LIKE '/%' ;
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path_prefix) WHERE path LIKE '/%' COLLATE utf8mb4_general_ci;
      

      Output (emoji replaced with plaintext as Jira doesn't seem to support it)

      # mysql  -v < testcase_utf8mb4.sql
      --------------
      SET NAMES utf8mb4
      --------------
       
      --------------
      DROP DATABASE if EXISTS db_utf8mb4
      --------------
       
      --------------
      CREATE DATABASE db_utf8mb4 CHARSET utf8mb4
      --------------
       
      --------------
      CREATE TABLE tbl_utf8mb4 (id serial, path varchar(255), KEY(path), KEY path_prefix (path(200))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC charset utf8mb4 collate utf8mb4_unicode_ci
      --------------
       
      --------------
      INSERT INTO tbl_utf8mb4 (path) VALUES (CONCAT('/',CHAR(4036994689)))
      --------------
       
      --------------
      SELECT * FROM tbl_utf8mb4 WHERE path LIKE '/%'
      --------------
       
      id	path
      1	/(LION)
      --------------
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path) WHERE path LIKE '/%'
      --------------
       
      id	path
      1	/(LION)
      --------------
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path_prefix) WHERE path LIKE '/%'
      --------------
       
      --------------
      SELECT * FROM tbl_utf8mb4 FORCE INDEX (path_prefix) WHERE path LIKE '/%' COLLATE utf8mb4_general_ci
      --------------
       
      id	path
      1	/(LION)
      

      I could reproduce the same between MySQL 5.5 and MariaDB 10.4 (Also tested 10.3 and 10.2)

      Thanks for checking!
      Joffrey

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              joffrey92 Joffrey MICHAIE
              Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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