MySQL Bugs: #105142: UUID Binary Representation For UUID_TO

Bug #105142	UUID Binary Representation For UUID_TO_BIN and EF Core Is Incompatible
Submitted:	5 Oct 2021 19:31	Modified:	24 Nov 2022 1:52
Reporter:	Daniel King	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.25, 8.0.26	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
Tested with .NET app running on Win10 (local) and Linux (via Docker) + MySQL running on Linux (via Docker)

## Version Info

- MySQL version: `8.0.25`
- .NET runtime version: `Core 3.1`
- EF Core version: `5.0.5`
- MySQL.EntityFrameworkCore version: `5.0.5`

## Description

When a UUID is added to the database via EF, it does not match the format of the same when specified as `UUID_TO_BIN('<UUID STRING>')` such that it cannot be found via direct SQL, even when the string representation of the UUID matches exactly.

For example, if we insert an item using EF with ID `831136f0-aee0-47bc-b6ba-be04dc858990` and then try to look it up using `WHERE ID = UUD_TO_BIN('831136f0-aee0-47bc-b6ba-be04dc858990')` the output will be empty. When looking at the byte orders of the two representations we see the following:
- EF Core byte order: 240 54 17 131 224 174 188 71 182 186 190 4 220 133 137 144
- DML SQL byte order: 131 17 54 240 174 224 71 188 182 186 190 4 220 133 137 144 

Notice that, when comparing the two representations, the first 4 bytes are reversed, then the next 2 bytes are reversed, then again the next 2 bytes are reversed, before the final 8 bytes match up. This misordering is consistent in all of our tests.

Note, this is without configuring EF in any particular manner--the default behavior is misaligned. There is no known workaround except to not use direct SQL ever (both outside of the application context or within it) which is not tenable.

How to repeat:
## Code to Reproduce

I have created a public GitHub repo with a C# project to reproduce this issue [here](https://github.com/danielloganking/mysql-efcore-bugs). This topic refers to [bug 2](https://github.com/danielloganking/mysql-efcore-bugs#2-directly-inserted-uuids-do-not-matc...) as listed in that repo.

A pseudo-test case which reproduces the bug is included below for convenience:

```csharp
var id = Guid.NewGuid();
dbContext.Entities.Add(new Entity { Id = id });
dbContext.SaveChanges();
var selected = dbContext.Entities.FromSqlInterpolated(@$"
    SELECT * FROM Entities WHERE Id = UUID_TO_BIN({id.ToString()});
").AsEnumerable().FirstOrDefault();

selected.Should().NotBeNull();
```

Suggested fix:
GUIDs should serialize/deserialize in the same byte order as generated by UUID_TO_BIN.

Hello Daniel,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Hi Daniel,

When GUID is converted to a byte array so it can be written in the stream and then stored in the database, the order is swapped as you noticed but this is as per MS specification (https://learn.microsoft.com/en-us/dotnet/api/system.guid.tobytearray?view=net-7.0#remarks), so there's no error in that regards. Respecting the query you're testing to retrieve the values from the table, you're missing something and hence the "bug"; it is the HEX() function (https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_hex). So in the end your code will look like this:

```csharp
var id = Guid.NewGuid();
dbContext.Entities.Add(new Entity { Id = id });
dbContext.SaveChanges();
var selected = dbContext.Entities.FromSqlInterpolated(@$"
    SELECT * FROM Entities WHERE Id = UUID_TO_BIN(HEX({id}));
").AsEnumerable().FirstOrDefault();

selected.Should().NotBeNull();
```

Verified using the latest version of MySQL Connector/NET, v8.0.31.

Thanks,
Daniel V

That function may work to reorder the bytes :
"        public static byte[] ToMySqlOrderedBytes(byte[] binUuid)
        {
            byte[] originalBytes = binUuid;
            byte[] orderedBytes = new byte[16];

            // Reorder bytes according to the specified sequence
            // Reverse the first 4 bytes
            orderedBytes[0] = originalBytes[3];
            orderedBytes[1] = originalBytes[2];
            orderedBytes[2] = originalBytes[1];
            orderedBytes[3] = originalBytes[0];

            // Reverse the next 2 bytes
            orderedBytes[4] = originalBytes[5];
            orderedBytes[5] = originalBytes[4];

            // Reverse the next 2 bytes
            orderedBytes[6] = originalBytes[7];
            orderedBytes[7] = originalBytes[6];

            // Copy the remaining 8 bytes as they are (positions 8-15)
            Array.Copy(originalBytes, 8, orderedBytes, 8, 8);

            return orderedBytes;
        }
"