Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.11.7
-
None
-
MariaDB 10.11.7-MariaDB-1:10.11.7+maria~ubu2204 (running in Docker)
Queries are executed from an IntelliJ console using MariaDB connector J v3.0.7
Description
"NOT" comparisons between an UUID and a value doesn't behave as expected when the value doesn't respect UUID structure. Here are a few samples:
CREATE TABLE TEST |
(
|
ID UUID PRIMARY KEY, |
NAME VARCHAR(50), |
AGE INT |
);
|
|
|
INSERT INTO TEST (ID, NAME, AGE) VALUES (UUID(), 'John', 25); |
|
|
-- Returns 0 row whereas returning John would have made more sense
|
SELECT * FROM TEST |
WHERE ID != 'not uuid'; |
|
|
-- Returns 1 row even if this UUID type is V7 and the single one in database is V1, as expected
|
SELECT * FROM TEST |
WHERE ID != '018e31a8-8112-7979-9b7b-fd353e714f9d'; |
|
|
-- Returns 0 row whereas returning John would have made more sense
|
SELECT * FROM TEST |
WHERE ID NOT IN ('not uuid'); |
|
|
-- Returns 1 row on 10.11.5 as expected, but 0 row on 10.11.6 and 10.11.7.
|
SELECT * FROM TEST |
WHERE ID NOT IN ('not uuid', '018e31a8-8112-7979-9b7b-fd353e714f9d'); |
|
|
-- Returns 1 row as expected
|
SELECT * FROM TEST |
WHERE ID NOT IN ('018e31a8-8112-7979-9b7b-fd353e714f9d'); |
Legacy softwares planning to migrate from Varchar(36) to UUID might suffer from this behavior.