MySQL Bugs: #98086: Add Variant when modifying UUID with timestamp Low/High swap

Bug #98086	Add Variant when modifying UUID with timestamp Low/High swap
Submitted:	27 Dec 2019 19:16	Modified:	11 Nov 2020 13:35
Reporter:	Brad Lanier	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	UUID Datatype, UUID_TO_BIN() Option TRUE Mod

Description:
When reversing the low/high timestamp portions of the UUID in the UUID_TO_BIN() function, there is no easy way to verify what format of the UUID is stored in the binary format.

How to repeat:
N/A

Suggested fix:
Request you change the UUID variant type to 7x (111x) when performing the Low/High timestamp swap and submit to appropriate authority to make it official.  This would add verification capabilities to what exactly is stored in the binary format.  When reversing the operation, the variant would be changed back.

After looking further, changing the variant value high-order bit from a 1 to 0 would avoid problems with destroying any high order clock sequence bits that a variant value of 7 would cause. Additionally, the likelihood of conflicting with the Apollo specification would be almost non-existent. This solution would also preserve reverting to ANY of the other variants (1,2,or 7) when reverting from the binary high/low format to the original hex low/high format.  Simply setting the high bit when reversing the operation preserves everything.

Hi Mr. Lanier,

Thank you for your feature request.

It is quite well defined. I can see a small problem here, which is when Unix standard makes timestamp an unsigned 32-bit integer.

However, that date is still far away.

Verified as reported.

There is possibly a better way to accomplish this.  Create a new datatype UUID that would do all of the work behind the scenes.  Here is how it could work:

1. Any value assigned to a UUID datatype would be first validated as a valid UUID.
2. Internally, the UUID would be stored as a binary(16), with the time-low and time-high parts swapped.
3. When referenced in any way, it would return a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format (with the time-low and time-high parts in the proper order), making it compatible with any functions/routines used against a UUID.

Thank you, Mr. Lanier.

Your comments are forwarded to our team that deals with feature requests.

Here is a simple solution to solve this issue and all related issues:

Create a UUID Datatype (see bugs 68859, 72899, and 99422) AND a UUIDHL (UUID High-to-Low) Datatype. Functionality would be as follows:

1.  BOTH Datatypes are BIN(16)
2.  UUID datatype ALWAYS contains UUID in NATIVE Format.
3.  UUIDHL datatype ALWAYS contains UUID in High to Low timestamp (reversed) order.
4.  All Built-in functions that return or use the UUID Datatype will automatically handle the UUID/UUIDHL datatypes.
5.  All Convert operations will convert properly to/from the UUID/UUIDHL datatypes.

This is a relatively SIMPLE solution that will resolve ALL of the related requested fixes/features while retaining FULL current and future UUID specifications!

Thank you for yet another contribution, Mr. Lanier.