[#MDEV-28826] UUID field type + WHERE EXISTS doesn't work unless CASTed

We have some strange issues with the UUID data type (introduced in MariaDB 10.7) when using WHERE EXISTS queries.
After some investigation, we believe that the issue is in the MariaDB.
We have managed to create a minimal reproducible case and would like to share it with you.

Consider the following DDL:

CREATE TABLE `projects` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `uuid` uuid NOT NULL,

  `user_id` int(10) unsigned DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `projects_uuid_index` (`uuid`),

  KEY `projects_user_id_foreign` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `project_media_elements` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `uuid` uuid NOT NULL,

  `project_uuid` uuid DEFAULT NULL,

  `file_path` text COLLATE utf8mb4_unicode_ci NOT NULL,

  `created_at` timestamp /* mariadb-5.3 */ NULL DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `project_media_elements_project_uuid_foreign` (`project_uuid`),

  KEY `project_media_elements_uuid_index` (`uuid`),

  KEY `projects_created_at_index` (`created_at`),

  CONSTRAINT `project_media_elements_project_uuid_foreign` FOREIGN KEY (`project_uuid`) REFERENCES `projects` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

And let's insert sample data into these tables:

INSERT INTO `projects` (`uuid`, `user_id`) VALUES ('1eceb608-5264-6bb0-bab5-77d63d9924a6', '1');

INSERT INTO `project_media_elements` (`uuid`, `project_uuid`, `file_path`, `created_at`) VALUES ('96807ed0-3c38-46ed-a482-34bbfebc4f0b', '1eceb608-5264-6bb0-bab5-77d63d9924a6', '/path/to/file', '2022-06-01 00:00:00');

Let's try to select the project by `uuid` field adding the additional constraint that it shouldn't have expired media elements in it (`created_at` < '2022-05-30').
We expect this query to return the project:

SELECT * FROM `projects` WHERE `uuid` = '1eceb608-5264-6bb0-bab5-77d63d9924a6' AND NOT EXISTS (SELECT * FROM `project_media_elements` WHERE `project_uuid` = `projects`.`uuid` AND `created_at` < '2022-05-30') LIMIT 1;

Strange enough, but this will return an empty result.

To make the query work we need to replace `project_uuid` with CAST(`project_uuid` as UUID) (CAST(`project_uuid` AS VARCHAR(100)) also works):

SELECT * FROM `projects` WHERE `uuid` = '1eceb608-5264-6bb0-bab5-77d63d9924a6' AND NOT EXISTS (SELECT * FROM `project_media_elements` WHERE CAST(`project_uuid` as UUID) = `projects`.`uuid` AND `created_at` < '2022-05-30') LIMIT 1;

This works! (But CAST(), obviously, makes indexes useless and hurts performance).

Is it reproducible on your side?

[MDEV-28826] UUID field type + WHERE EXISTS doesn't work unless CASTed Created: 2022-06-13 Updated: 2022-06-28 Resolved: 2022-06-28
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.7.4
Fix Version/s:	N/A

[MDEV-28826] UUID field type + WHERE EXISTS doesn't work unless CASTed Created: 2022-06-13 Updated: 2022-06-28 Resolved: 2022-06-28