[MDEV-31926] UUID v7 are compared incorrectly - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.9.8, 10.10.6, 10.11.5, 11.0.3
Fix Version/s: 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2
Component/s: Data types
Labels:
None

Description

create table t1 (id uuid, model_value char(10));

insert t1 values ('0189f81d-498e-72f8-b0fa-246d7be1adb1', '30'),

                 ('0189f81d-335c-71c7-a620-3f093ccc39c3', '29'),

                 ('0189f81d-2f49-72fa-8ed9-4bd2fe509e59', '28'),

                 ('0189f81d-2ae6-7204-b694-84602a2560c7', '27'),

                 ('0189f81d-2620-717b-b873-1829f25a4d53', '26'),

                 ('0189f81c-f940-7161-9c48-9677e11bc40c', '25'),

                 ('0189f81c-d4cf-71c1-93f1-ddd005eadb8b', '24'),

                 ('0189f81c-d07f-7143-8c8b-b19457138aef', '23'),

                 ('0189f81c-cbec-707d-9b45-b007920b5ef6', '22'),

                 ('0189f81c-c795-71d0-bc3f-8b7b74abe044', '21'),

                 ('0189f81c-c33d-7100-a39c-80ea40a090fc', '20'),

                 ('0189f81c-bf35-70a0-a288-8d80473bc015', '19'),

                 ('0189f81c-baf8-718a-870c-cd41e677c3f4', '18'),

                 ('0189f81c-b5f1-721d-b135-22c38153aab6', '17'),

                 ('0189f81c-b011-71cd-bf2a-d6be3cea6dd7', '16'),

                 ('0189f81c-9934-721f-90dc-05a65a79f4b6', '15'),

                 ('0189f81c-94d6-733d-9953-2addf3a83be4', '14'),

                 ('0189f81c-9016-71a6-a340-ec300186a4d9', '13'),

                 ('0189f81c-8bca-73c9-8ffb-225a39a5dc49', '12'),

                 ('0189f81c-868b-71d1-830e-9b4ae08aa782', '11'),

                 ('0189f81c-8219-731a-b648-0e19e64d0ec0', '10'),

                 ('0189f81c-7e01-710a-91ce-49e62163fa29', '9'),

                 ('0189f81c-7a05-724e-a7e1-9baf65a91ecb', '8'),

                 ('0189f81c-75b8-7223-bd6f-b60601be3aa1', '7'),

                 ('0189f81c-71fc-7303-b850-4538b119c5fc', '6'),

                 ('0189f81c-6e06-71a3-a47a-664a606b08d2', '5'),

                 ('0189f81c-6a38-710e-8730-8babd48c28f4', '4'),

                 ('0189f81c-650e-7171-b962-045cc4033000', '3'),

                 ('0189f81c-4eff-70c7-a361-caffaf2c0666', '2'),

                 ('0189f81c-49e9-7279-8087-9f8e25730957', '1');

select id, model_value from t1 where id < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;

select id, model_value from t1 where concat(id) < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;

drop table t1;

because it compares segments backwards independently from the version:

template <bool force_swap>

class UUID: public FixedBinTypeStorage<MY_UUID_SIZE, MY_UUID_STRING_LENGTH>

...

  // Compare two in-memory values

  static int cmp(const LEX_CSTRING &a, const LEX_CSTRING &b)

    DBUG_ASSERT(a.length == binary_length());

    DBUG_ASSERT(b.length == binary_length());

    int res;

    if ((res= segment(4).cmp_memory(a.str, b.str)) ||

        (res= segment(3).cmp_memory(a.str, b.str)) ||

        (res= segment(2).cmp_memory(a.str, b.str)) ||

        (res= segment(1).cmp_memory(a.str, b.str)) ||

        (res= segment(0).cmp_memory(a.str, b.str)))

      return  res;

    return 0;

...

Attachments

Issue Links

relates to

MDEV-4958 Adding datatype UUID

Closed

MDEV-29959 UUID Sorting

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Golubchik created issue - 2023-08-15 09:44

Sergei Golubchik made changes - 2023-08-15 09:44

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-29959~~ [ ~~MDEV-29959~~ ]

Sergei Golubchik made changes - 2023-08-15 09:45

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2023-08-15 09:53

Description

{code:sql}
create table t1 (id uuid, model_value char(10));
insert t1 values ('0189f81d-498e-72f8-b0fa-246d7be1adb1', '30'),
                 ('0189f81d-335c-71c7-a620-3f093ccc39c3', '29'),
                 ('0189f81d-2f49-72fa-8ed9-4bd2fe509e59', '28'),
                 ('0189f81d-2ae6-7204-b694-84602a2560c7', '27'),
                 ('0189f81d-2620-717b-b873-1829f25a4d53', '26'),
                 ('0189f81c-f940-7161-9c48-9677e11bc40c', '25'),
                 ('0189f81c-d4cf-71c1-93f1-ddd005eadb8b', '24'),
                 ('0189f81c-d07f-7143-8c8b-b19457138aef', '23'),
                 ('0189f81c-cbec-707d-9b45-b007920b5ef6', '22'),
                 ('0189f81c-c795-71d0-bc3f-8b7b74abe044', '21'),
                 ('0189f81c-c33d-7100-a39c-80ea40a090fc', '20'),
                 ('0189f81c-bf35-70a0-a288-8d80473bc015', '19'),
                 ('0189f81c-baf8-718a-870c-cd41e677c3f4', '18'),
                 ('0189f81c-b5f1-721d-b135-22c38153aab6', '17'),
                 ('0189f81c-b011-71cd-bf2a-d6be3cea6dd7', '16'),
                 ('0189f81c-9934-721f-90dc-05a65a79f4b6', '15'),
                 ('0189f81c-94d6-733d-9953-2addf3a83be4', '14'),
                 ('0189f81c-9016-71a6-a340-ec300186a4d9', '13'),
                 ('0189f81c-8bca-73c9-8ffb-225a39a5dc49', '12'),
                 ('0189f81c-868b-71d1-830e-9b4ae08aa782', '11'),
                 ('0189f81c-8219-731a-b648-0e19e64d0ec0', '10'),
                 ('0189f81c-7e01-710a-91ce-49e62163fa29', '9'),
                 ('0189f81c-7a05-724e-a7e1-9baf65a91ecb', '8'),
                 ('0189f81c-75b8-7223-bd6f-b60601be3aa1', '7'),
                 ('0189f81c-71fc-7303-b850-4538b119c5fc', '6'),
                 ('0189f81c-6e06-71a3-a47a-664a606b08d2', '5'),
                 ('0189f81c-6a38-710e-8730-8babd48c28f4', '4'),
                 ('0189f81c-650e-7171-b962-045cc4033000', '3'),
                 ('0189f81c-4eff-70c7-a361-caffaf2c0666', '2'),
                 ('0189f81c-49e9-7279-8087-9f8e25730957', '1');
select id, model_value from t1 where id < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;
select id, model_value from t1 where concat(id) < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;
drop table t1;
{code}
because it compares segments backwards independent from the version:
{code:cpp}
template <bool force_swap>
class UUID: public FixedBinTypeStorage<MY_UUID_SIZE, MY_UUID_STRING_LENGTH>
{
  ...
  // Compare two in-memory values
  static int cmp(const LEX_CSTRING &a, const LEX_CSTRING &b)
  {
    DBUG_ASSERT(a.length == binary_length());
    DBUG_ASSERT(b.length == binary_length());
    int res;
    if ((res= segment(4).cmp_memory(a.str, b.str)) ||
        (res= segment(3).cmp_memory(a.str, b.str)) ||
        (res= segment(2).cmp_memory(a.str, b.str)) ||
        (res= segment(1).cmp_memory(a.str, b.str)) ||
        (res= segment(0).cmp_memory(a.str, b.str)))
      return res;
    return 0;
  }
  ...
}
{code}

{code:sql}
create table t1 (id uuid, model_value char(10));
insert t1 values ('0189f81d-498e-72f8-b0fa-246d7be1adb1', '30'),
                 ('0189f81d-335c-71c7-a620-3f093ccc39c3', '29'),
                 ('0189f81d-2f49-72fa-8ed9-4bd2fe509e59', '28'),
                 ('0189f81d-2ae6-7204-b694-84602a2560c7', '27'),
                 ('0189f81d-2620-717b-b873-1829f25a4d53', '26'),
                 ('0189f81c-f940-7161-9c48-9677e11bc40c', '25'),
                 ('0189f81c-d4cf-71c1-93f1-ddd005eadb8b', '24'),
                 ('0189f81c-d07f-7143-8c8b-b19457138aef', '23'),
                 ('0189f81c-cbec-707d-9b45-b007920b5ef6', '22'),
                 ('0189f81c-c795-71d0-bc3f-8b7b74abe044', '21'),
                 ('0189f81c-c33d-7100-a39c-80ea40a090fc', '20'),
                 ('0189f81c-bf35-70a0-a288-8d80473bc015', '19'),
                 ('0189f81c-baf8-718a-870c-cd41e677c3f4', '18'),
                 ('0189f81c-b5f1-721d-b135-22c38153aab6', '17'),
                 ('0189f81c-b011-71cd-bf2a-d6be3cea6dd7', '16'),
                 ('0189f81c-9934-721f-90dc-05a65a79f4b6', '15'),
                 ('0189f81c-94d6-733d-9953-2addf3a83be4', '14'),
                 ('0189f81c-9016-71a6-a340-ec300186a4d9', '13'),
                 ('0189f81c-8bca-73c9-8ffb-225a39a5dc49', '12'),
                 ('0189f81c-868b-71d1-830e-9b4ae08aa782', '11'),
                 ('0189f81c-8219-731a-b648-0e19e64d0ec0', '10'),
                 ('0189f81c-7e01-710a-91ce-49e62163fa29', '9'),
                 ('0189f81c-7a05-724e-a7e1-9baf65a91ecb', '8'),
                 ('0189f81c-75b8-7223-bd6f-b60601be3aa1', '7'),
                 ('0189f81c-71fc-7303-b850-4538b119c5fc', '6'),
                 ('0189f81c-6e06-71a3-a47a-664a606b08d2', '5'),
                 ('0189f81c-6a38-710e-8730-8babd48c28f4', '4'),
                 ('0189f81c-650e-7171-b962-045cc4033000', '3'),
                 ('0189f81c-4eff-70c7-a361-caffaf2c0666', '2'),
                 ('0189f81c-49e9-7279-8087-9f8e25730957', '1');
select id, model_value from t1 where id < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;
select id, model_value from t1 where concat(id) < '0189f81c-c795-71d0-bc3f-8b7b74abe044' order by id desc;
drop table t1;
{code}
because it compares segments backwards independently from the version:
{code:cpp}
template <bool force_swap>
class UUID: public FixedBinTypeStorage<MY_UUID_SIZE, MY_UUID_STRING_LENGTH>
{
  ...
  // Compare two in-memory values
  static int cmp(const LEX_CSTRING &a, const LEX_CSTRING &b)
  {
    DBUG_ASSERT(a.length == binary_length());
    DBUG_ASSERT(b.length == binary_length());
    int res;
    if ((res= segment(4).cmp_memory(a.str, b.str)) ||
        (res= segment(3).cmp_memory(a.str, b.str)) ||
        (res= segment(2).cmp_memory(a.str, b.str)) ||
        (res= segment(1).cmp_memory(a.str, b.str)) ||
        (res= segment(0).cmp_memory(a.str, b.str)))
      return res;
    return 0;
  }
  ...
}
{code}

Sergei Golubchik made changes - 2023-10-25 20:28

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2023-10-27 17:10

Status

Confirmed [ 10101 ]

Open [ 1 ]

Sergei Golubchik made changes - 2023-10-27 17:10

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Sergei Golubchik made changes - 2023-10-28 07:46

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Sergei Golubchik made changes - 2023-10-28 21:22

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-10-28 21:23

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-10-28 21:23

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2023-10-30 02:56

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-10-30 10:58

Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s		11.2.2 [ 29035 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.1 [ 28549 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-12-19 11:09

Link

This issue causes MDEV-33065 [ MDEV-33065 ]

Sergei Golubchik made changes - 2023-12-19 11:12

Link

This issue causes MDEV-33065 [ MDEV-33065 ]

Alexander Barkov made changes - 2024-02-14 08:38

Link

This issue relates to ~~MDEV-4958~~ [ ~~MDEV-4958~~ ]

People

Assignee:: Sergei Golubchik

Reporter:: Sergei Golubchik

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-08-15 09:44

Updated:: 2024-02-14 08:38

Resolved:: 2023-10-30 10:58

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration