mysql binary field lookup using LIKE has a problem

Question

I have a problem with BINARY(16) type (ULID under the hood) and specific ULID 01HRS49374C1EAGTPKTJ0JVT1P (hex 018e32448ce4605ca86ad3d4812de836) when queried using LIKE. LIKE query works with other ULIDs but not with this specific one.

Because its a ULID the length is always 16bytes so its not an issue with size of the field.

Last SELECT statement from the code bellow returns empty result! Why?

CREATE TABLE IF NOT EXISTS new_table (
    id BINARY(16) PRIMARY KEY NOT NULL,
    name VARCHAR(255)
);

INSERT INTO new_table SET id = UNHEX("018e321f579997e1f2a907a72b98f965"), name = "this works fine";
INSERT INTO new_table SET id = UNHEX("018e32448ce4605ca86ad3d4812de836"), name = "try find this using LIKE";

SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e321f579997e1f2a907a72b98f965");
SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836");

Answer 1

It works if we change the ESCAPE character:

mysql> SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836") ESCAPE '@';
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+

It also works if we disable backslash as a metacharacter:

mysql> set sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT HEX(id), name FROM new_table WHERE id LIKE UNHEX("018e32448ce4605ca86ad3d4812de836");
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+

So I tried to view the binary string in the client:

% mysql --skip-binary-as-hex test

mysql> select UNHEX("018e32448ce4605ca86ad3d4812de836");
+-------------------------------------------+
| UNHEX("018e32448ce4605ca86ad3d4812de836") |
+-------------------------------------------+
| ?2D??`\?j?ԁ-?6                                 |
+-------------------------------------------+
        ^

There's the backslash.

The point being that LIKE applies some special meaning to certain characters in the string, like backslash and _ and %.

If you UNHEX a random hex string, you don't know if the bytes will by coincidence become one of those characters that are special to LIKE.

Conclusion: It's not a bug.

LIKE is not the appropriate comparison operator for your search.

In your example, it would be fine to use =, because you are checking for strings being equal, not using pattern-matching with LIKE.

This works:

mysql> SELECT HEX(id), name FROM new_table WHERE id = UNHEX("018e32448ce4605ca86ad3d4812de836");
+----------------------------------+--------------------------+
| HEX(id)                          | name                     |
+----------------------------------+--------------------------+
| 018E32448CE4605CA86AD3D4812DE836 | try find this using LIKE |
+----------------------------------+--------------------------+