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

[5 Oct 2021 19:31] Daniel King
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.
[6 Oct 2021 6:43] MySQL Verification Team
Hello Daniel,

Thank you for the report and test case.

regards,
Umesh