|
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
|