Uuid v7 generated in wrong order

[fannyfan414]

Hello, I came across a strange case where the library generated uuid v7 incorrectly in time.

Here example of table id = Uuid::uuid7()->toString() and created_at (server datetime), sorted by id (asc)

id, created_at 018d7fca-3ae5-7306-a040-204628e43dc9, 2024-02-06T18:59:36.000000Z 018d7fdc-0ba8-7006-8047-41a572a695c6, 2024-02-06T19:19:03.000000Z 018d8030-0dab-7206-a042-31238ed9cb48, 2024-02-06T20:50:49.000000Z 018d8041-7c1b-7106-8043-6c5e69c8fbbd, 2024-02-06T21:09:51.000000Z 01a6ce4c-5a19-7006-b045-197ad8210718, 2024-02-06T19:19:54.000000Z

For some reason, ID=01a6ce4c-5a19-7006-b045-197ad8210718 was generated incorrectly

[fannyfan414]

Another wrong generations:

id, created_at 018d8796-b0c2-71ec-a637-7ad6a9a68a3c, 2024-02-08T07:20:16.000000Z 018d8799-185b-72ec-963e-54e7016030d8, 2024-02-08T07:22:54.000000Z 018d8799-5cd1-71ec-863a-f076be6535c6, 2024-02-08T07:23:11.000000Z 018d8799-8d0d-70ec-a637-743e23347b0d, 2024-02-08T07:23:23.000000Z 018d879c-3258-72ec-8638-8a684bdc8bb6, 2024-02-08T07:26:17.000000Z 01f7b992-d225-73ec-863e-d9d832540274, 2024-02-08T07:16:07.000000Z

01f7b992-d225-73ec-863e-d9d832540274 generated from incorrect time

[fannyfan414]

And one more example (this ids use in messages table), messages sorted by "id", since in theory uuid v7 should be monotonically increasing

Therefore, because of this bug, sometimes the wrong order of messages appears in correspondence

id, created_at 018d8537-8800-71ec-9631-9041bbb08aa2, 2024-02-07T20:17:05.000000Z 018d8537-b73c-72ec-a63c-f0dc35e6eb67, 2024-02-07T20:17:17.000000Z 018d8537-b74e-71ec-8637-616a01cbf9c8, 2024-02-07T20:17:17.000000Z 018d8537-b7ce-73ec-9635-37455306d8bc, 2024-02-07T20:17:17.000000Z 018d855d-4059-72ec-9633-ecea8496ee53, 2024-02-07T20:58:17.000000Z 018d8766-2d2e-73ec-b632-eb1529031055, 2024-02-08T06:27:16.000000Z 01b0b44d-a613-71ec-b637-cbb67c05e163, 2024-02-07T20:24:24.000000Z

018d8766-2d2e-73ec-b632-eb1529031055 and 01b0b44d-a613-71ec-b637-cbb67c05e163 generated incorrectly

[ramsey]

1. Are these UUIDs all generated on the same machine? Are they generated within the same process?
2. Are you able to consistently reproduce this issue with a short snippet of code? Can you reproduce it on different machines?
3. Can you reproduce the issue when generating UUIDv7 using symfony/uid? ramsey/uuid borrows code from symfony/uid, so if you can reproduce it with that library, then both of our libraries have the same bug.

[j-fulbright]

Wanted to come here and say we were having this same issue (noticed in the image below - 2nd line should be first) We switched to the symfony package and it worked as expected.

[ramsey]

@j-fulbright According to the created_at column, these records appear to be in descending order by the created_at timestamp.

Since the second record was created at 2024-07-31 13:23:51.037019, it was created earlier than the first record, which was created at 2024-07-31 13:23:51.800479 (763_460 microseconds after the second record).

I did take a look at the UUIDs themselves, though, and I can tell the second one was created after the first one (if the timestamp is to be believed):

> $u1 = Ramsey\Uuid\Uuid::fromString('019108f6-25a7-70cb-80b0-b50530e83edd')
= Ramsey\Uuid\Lazy\LazyUuidFromString {#27275
    uuid: "019108f6-25a7-70cb-80b0-b50530e83edd",
  }

> $u2 = Ramsey\Uuid\Uuid::fromString('019108f6-2749-70c0-9786-6feea192b2f8')
= Ramsey\Uuid\Lazy\LazyUuidFromString {#13450
    uuid: "019108f6-2749-70c0-9786-6feea192b2f8",
  }

> $u1->getDateTime()->format('Y-m-d H:i:s.u')
= "2024-07-31 13:23:54.919000"

> $u2->getDateTime()->format('Y-m-d H:i:s.u')
= "2024-07-31 13:23:55.337000"

I wouldn't expect the timestamps to match with the created_at field in the database, but the fact that the timestamps in the created_at column are several seconds earlier than those on the UUIDs is a bit odd.

Nevertheless, when you ran this query, did you sort the results by created_at DESC? If so, that's why the records aren't in UUID order. If you sort them by uuid DESC, then the second record should appear above the first one.

When compared, the first UUID is definitely less than the second UUID:

> $u1->compareTo($u2)
= -1

So, it should sort in that order, accordingly.

[ramsey]

Addendum: unless you specify an ORDER BY clause in your query, the order of records returned is nondeterministic, so you aren't guaranteed to get them in a certain order. You'll need to either ORDER BY created_at or ORDER BY uuid to get them in the order you expect.

In this case, if you did ORDER BY uuid DESC, then they should be in the order you expect, but it looks like the result set is using ORDER BY created_at DESC instead.

Hope this helps!

[j-fulbright]

@ramsey I am 100% sure the query or the result there, that was just what was passed on to me from one of our developers.

I do know that using the exact same code and switching to Symfony, everything was properly being populated/created.

They used: return Uuid::uuid7($dateTime)->toString();

$dateTime is createdAt that is passed into Carbon with milliseconds etc.