– Falsehoods programmers believe about UUID ordering in ClickHouse
588 words, ~3 minutes
This is another one of those posts where I accept that things just are the way they are, but write down things for others, so that the Google query “clickhouse uuid ordering” or “clickhouse uuid sorting” isn’t completely devoid of relevant results.
Recently I got to tinker with a ClickHouse database. I’m still new to it, but I guess you can tell it’s a good database cause the client prompt is a
I’m pretty sure we can all also agree that UUIDs are pretty cool, and much cooler than incrementing IDs.
Did you know that ClickHouse’s ordering of UUIDs is not lexicographic?
A quick hopefully reproducible example:
This should be enough to get us some data to look at. Now, because of how we created the table, the data is already ordered by the
uuid column, but let’s still make sure:
And the result I get is:
Not particularly sorted to the human eye, is it? What we expected is probably more like this:
But it’s different! Hopefully this will never be useful to you, but if you or someone you know writes code where the ordering of UUIDs matters (this also applies to aggregate functions like min and max), you have to pay special attention to it unfortunately.
Frequently screamed questions
What the hell? Is the ordering here stable? How are they ordered anyway?
Yeah, actually, if you look closely at the first result list, you should be able to spot the pattern. Which is, that you can split the digits of the UUID into two groups AAAAAAAA-AAAA-AAAA-BBBB-BBBBBBBBBBBB and then the data is ordered lexicographically by (B, A) tuple.
And I guess it makes sense to some extent. The UUID type in ClickHouse is typedef’d to a 128-bit unsigned integer. Most of the machines people run ClickHouse on are 64-bit, and I imagine there are some shenaningans in the part that converts between the string representation and the integer value that cause the two halves of the number to be swapped.
How can this hurt me? How did you discover this?
I was looking into a bug where the affected feature paginated through a query. It would fetch certain items ordered by a (timestamp, uuid) tuple in batches of 10 at a time, and then get another batch by filtering items which were larger in ordering. This will work fine if you use the same ordering in both ORDER BY and WHERE parts of your solution.
In the affected application, stringified ordering was used. However, a query optimizer in the application saw
WHERE toString(uuid) < 'previous-best-uuid', and decided that stringification was unnecessary. It didn’t touch the ORDER BY clause though. This caused the database to incorrectly filter rows, skipping some unprocessed rows as well as repeating some already processed rows.
I guess the bigger lesson here is that when you paginate by a cursor, you have to be confident that filtering and ordering compare elements the same way. Which is not easy when you have a behaviour like what ClickHouse does here.