Bug in MySql and MariaDB when having an index on utf8mb4 data and the substring ü😋?

Question

When using a table like this:

CREATE TABLE test (
  name VARCHAR(100),
  INDEX name_index (name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

And then inserting some data:

INSERT INTO test(name) VALUES('ü here is a 😋 smiley');
INSERT INTO test(name) VALUES('ü😋 no space smiley');
INSERT INTO test(name) VALUES('ü 😋 space smiley');
INSERT INTO test(name) VALUES('ü without smiley');

This query fails:

SELECT * FROM test WHERE name LIKE ("ü%");

It ommits the 'ü😋 no space smiley' row.

Here is a db-fiddle:

https://www.db-fiddle.com/f/bR6Yx2PXPJdD7iSCLnW65Y/0

Interestingly, the result changes when one deletes the index. So without line 3 in the demo, it behaves as expected.

Is this a known bug?

I tried it in MySql 5.7, MySql 8.0 and MariaDB 10.3 - all showing the same behaviour.

Answer 1

After I have tried your code on another fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7 It worked for MySQL 5.7 and MySQL 5.6 and for MariaDB 10.3 but you have noticed it did not worked for MySQL 8.0.

I have changed your code for CREATE TABLE to this:

CREATE TABLE test (
  name VARCHAR(100),
  INDEX name_index (name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

And now it is ok :) DEMO Hope this helps. Cheers!

For additional info check documentation here:

https://dev.mysql.com/doc/refman/5.7/en/charset.html https://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html

UPDATE

First solution does not work because u = ü In that case use this:

CREATE TABLE test (
  name VARCHAR(100),
  INDEX name_index (name)
) CHARACTER SET gb18030 COLLATE gb18030_chinese_ci;

New DEMO

UPDATE 2

Well if this is the deal: "The character set needs to be utf8mb4" :) then without changing anything you can try this query:

SELECT name from test
where hex(name) like concat('%',hex('ü'),'%')

Here is a DEMO in a new fiddle where you asked to be explained. Cheers.