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

UUID primary key filtering return incorrect results

Details

    Description

      See this easy reproducable example:

      CREATE TABLE `uuid_problem` (
      	`id` UUID NOT NULL,
      	PRIMARY KEY (`id`) USING BTREE
      )
      COLLATE='utf8mb4_unicode_ci'
      ENGINE=InnoDB
      ;
       
      INSERT INTO `uuid_problem` (`id`) VALUES ('e8748eee-fabe-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('036d4fc5-fabf-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('2acf42cc-fabf-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('594a8970-fabf-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('4238a6e5-fac1-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('6d001a4d-fac1-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('e4e67615-fad5-11eb-af18-005056bc575d');
      INSERT INTO `uuid_problem` (`id`) VALUES ('1ec69927-30f3-62ca-a0a0-4b98bb6957f8');
      INSERT INTO `uuid_problem` (`id`) VALUES ('1ec6992e-5c9e-6b2a-a21b-fbc054a2075e');
      INSERT INTO `uuid_problem` (`id`) VALUES ('1ec6992e-e5be-6342-8293-e107448b2cd5');
       
      -- This returns 10 rows
      SELECT * FROM `uuid_problem` WHERE id IN (
      "036d4fc5-fabf-11eb-af18-005056bc575d","e8748eee-fabe-11eb-af18-005056bc575d","2acf42cc-fabf-11eb-af18-005056bc575d","594a8970-fabf-11eb-af18-005056bc575d","4238a6e5-fac1-11eb-af18-005056bc575d","6d001a4d-fac1-11eb-af18-005056bc575d","e4e67615-fad5-11eb-af18-005056bc575d","1ec69927-30f3-62ca-a0a0-4b98bb6957f8","1ec6992e-5c9e-6b2a-a21b-fbc054a2075e","1ec6992e-e5be-6342-8293-e107448b2cd5"
      );
       
       
      -- Here we only swap the first 2 values of the `id IN ()` array
      -- This suddenly return 9 rows
      SELECT * FROM `uuid_problem` WHERE id IN (
      "e8748eee-fabe-11eb-af18-005056bc575d","036d4fc5-fabf-11eb-af18-005056bc575d","2acf42cc-fabf-11eb-af18-005056bc575d","594a8970-fabf-11eb-af18-005056bc575d","4238a6e5-fac1-11eb-af18-005056bc575d","6d001a4d-fac1-11eb-af18-005056bc575d","e4e67615-fad5-11eb-af18-005056bc575d","1ec69927-30f3-62ca-a0a0-4b98bb6957f8","1ec6992e-5c9e-6b2a-a21b-fbc054a2075e","1ec6992e-e5be-6342-8293-e107448b2cd5"
      );
      

      Attachments

        Activity

          Probably it's related to the fact the table consists both UUID v1 and V6. This isn't best practice. But still I don't suppose this behaviour should happen.

          karsten684 Karsten Nilsen added a comment - Probably it's related to the fact the table consists both UUID v1 and V6. This isn't best practice. But still I don't suppose this behaviour should happen.

          It's also repeatable without indexes:

          CREATE OR REPLACE TABLE t1 (
            id UUID NOT NULL
          );
           
           
          INSERT INTO t1 (`id`) VALUES ('e8748eee-fabe-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('036d4fc5-fabf-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('2acf42cc-fabf-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('594a8970-fabf-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('4238a6e5-fac1-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('6d001a4d-fac1-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('e4e67615-fad5-11eb-af18-005056bc575d');
          INSERT INTO t1 (`id`) VALUES ('1ec69927-30f3-62ca-a0a0-4b98bb6957f8');
          INSERT INTO t1 (`id`) VALUES ('1ec6992e-5c9e-6b2a-a21b-fbc054a2075e');
          INSERT INTO t1 (`id`) VALUES ('1ec6992e-e5be-6342-8293-e107448b2cd5');
           
          -- This returns 10 rows
          SELECT * FROM t1 WHERE id IN (
          "036d4fc5-fabf-11eb-af18-005056bc575d","e8748eee-fabe-11eb-af18-005056bc575d","2acf42cc-fabf-11eb-af18-005056bc575d","594a8970-fabf-11eb-af18-005056bc575d","4238a6e5-fac1-11eb-af18-005056bc575d","6d001a4d-fac1-11eb-af18-005056bc575d","e4e67615-fad5-11eb-af18-005056bc575d","1ec69927-30f3-62ca-a0a0-4b98bb6957f8","1ec6992e-5c9e-6b2a-a21b-fbc054a2075e","1ec6992e-e5be-6342-8293-e107448b2cd5"
          );
           
          -- Here we only swap the first 2 values of the `id IN ()` array
          -- This suddenly return 9 rows
          SELECT * FROM t1 WHERE id IN (
          "e8748eee-fabe-11eb-af18-005056bc575d","036d4fc5-fabf-11eb-af18-005056bc575d","2acf42cc-fabf-11eb-af18-005056bc575d","594a8970-fabf-11eb-af18-005056bc575d","4238a6e5-fac1-11eb-af18-005056bc575d","6d001a4d-fac1-11eb-af18-005056bc575d","e4e67615-fad5-11eb-af18-005056bc575d","1ec69927-30f3-62ca-a0a0-4b98bb6957f8","1ec6992e-5c9e-6b2a-a21b-fbc054a2075e","1ec6992e-e5be-6342-8293-e107448b2cd5");
          

          bar Alexander Barkov added a comment - It's also repeatable without indexes: CREATE OR REPLACE TABLE t1 ( id UUID NOT NULL );     INSERT INTO t1 (`id`) VALUES ( 'e8748eee-fabe-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '036d4fc5-fabf-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '2acf42cc-fabf-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '594a8970-fabf-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '4238a6e5-fac1-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '6d001a4d-fac1-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( 'e4e67615-fad5-11eb-af18-005056bc575d' ); INSERT INTO t1 (`id`) VALUES ( '1ec69927-30f3-62ca-a0a0-4b98bb6957f8' ); INSERT INTO t1 (`id`) VALUES ( '1ec6992e-5c9e-6b2a-a21b-fbc054a2075e' ); INSERT INTO t1 (`id`) VALUES ( '1ec6992e-e5be-6342-8293-e107448b2cd5' );   -- This returns 10 rows SELECT * FROM t1 WHERE id IN ( "036d4fc5-fabf-11eb-af18-005056bc575d" , "e8748eee-fabe-11eb-af18-005056bc575d" , "2acf42cc-fabf-11eb-af18-005056bc575d" , "594a8970-fabf-11eb-af18-005056bc575d" , "4238a6e5-fac1-11eb-af18-005056bc575d" , "6d001a4d-fac1-11eb-af18-005056bc575d" , "e4e67615-fad5-11eb-af18-005056bc575d" , "1ec69927-30f3-62ca-a0a0-4b98bb6957f8" , "1ec6992e-5c9e-6b2a-a21b-fbc054a2075e" , "1ec6992e-e5be-6342-8293-e107448b2cd5" );   -- Here we only swap the first 2 values of the `id IN ()` array -- This suddenly return 9 rows SELECT * FROM t1 WHERE id IN ( "e8748eee-fabe-11eb-af18-005056bc575d" , "036d4fc5-fabf-11eb-af18-005056bc575d" , "2acf42cc-fabf-11eb-af18-005056bc575d" , "594a8970-fabf-11eb-af18-005056bc575d" , "4238a6e5-fac1-11eb-af18-005056bc575d" , "6d001a4d-fac1-11eb-af18-005056bc575d" , "e4e67615-fad5-11eb-af18-005056bc575d" , "1ec69927-30f3-62ca-a0a0-4b98bb6957f8" , "1ec6992e-5c9e-6b2a-a21b-fbc054a2075e" , "1ec6992e-e5be-6342-8293-e107448b2cd5" );

          Also repeatable with ENGINE=MyISAM

          bar Alexander Barkov added a comment - Also repeatable with ENGINE=MyISAM
          bar Alexander Barkov added a comment - serg , please review a patch: https://github.com/MariaDB/server/commit/189dd04d48c4ed6fbd9b896aee86d0538fc00244

          189dd04d48c4ed6fbd9b896aee86d0538fc00244 is ok to push

          serg Sergei Golubchik added a comment - 189dd04d48c4ed6fbd9b896aee86d0538fc00244 is ok to push

          People

            bar Alexander Barkov
            karsten684 Karsten Nilsen
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.