Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
MariaDB 12.3.3-MariaDB-ubu2404 on Linux x86_64
Description
A binary column compared to itself with LIKE ... ESCAPE produces correct scalar values but incorrect filtering in MariaDB 12.3.3-MariaDB-ubu2404. For the stored value a!b, scalar evaluation reports c LIKE c ESCAPE '!' as false and its negation as true. The corresponding WHERE clauses instead include that row for LIKE and exclude it for NOT LIKE.
This occurs when a binary column is compared to itself with LIKE or NOT LIKE and an ESCAPE character within the stored value changes the pattern interpretation.
Save the following as case.sql:
CREATE DATABASE like_test; |
USE like_test; |
CREATE TABLE samples ( |
id INT PRIMARY KEY, |
c VARBINARY(16) NOT NULL |
);
|
INSERT INTO samples VALUES (1,0x6162),(2,0x612162); |
|
|
SELECT 'projection' AS phase, id, HEX(c), |
c LIKE c ESCAPE '!' AS matches, |
c NOT LIKE c ESCAPE '!' AS nonmatches |
FROM samples |
ORDER BY id; |
|
|
SELECT 'like_filter' AS phase, |
COALESCE(GROUP_CONCAT(id ORDER BY id),'empty') AS ids |
FROM samples |
WHERE c LIKE c ESCAPE '!'; |
|
|
SELECT 'not_like_filter' AS phase, |
COALESCE(GROUP_CONCAT(id ORDER BY id),'empty') AS ids |
FROM samples |
WHERE c NOT LIKE c ESCAPE '!'; |
|
|
EXPLAIN EXTENDED
|
SELECT id FROM samples WHERE c NOT LIKE c ESCAPE '!'; |
SHOW WARNINGS;
|
|
|
DROP DATABASE like_test; |
Run it against the server:
mariadb < case.sql |
The recorded output was:
phase id HEX(c) matches nonmatches
|
projection 1 6162 1 0
|
projection 2 612162 0 1
|
phase ids
|
like_filter 1,2
|
phase ids
|
not_like_filter empty
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
Level Code Message
|
Note 1003 select `like_test`.`samples`.`id` AS `id` from `like_test`.`samples` where 0
|
Row 1 contains ab and correctly matches its own pattern. Row 2 contains a!b; with ! as the escape character, its scalar result correctly shows that it does not match its own interpreted pattern. Filtering must preserve those predicate results: the LIKE query should return only id 1, and the NOT LIKE query should return only id 2. Instead, the optimizer treats the LIKE expression as always true and its negation as always false; the latter is shown as Impossible WHERE and rewritten to where 0.