Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28826

UUID field type + WHERE EXISTS doesn't work unless CASTed

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.7.4
    • N/A
    • Data types
    • None
    • CentOS 7 + Official MariaDB 10.7 yum repository.

    Description

      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?

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              Dantist Gri
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.