Hibernate uuid2 uses lowercase String while SQL Server uniqueidentifier expects an uppercase String


#1

Hello,

after getting some hints on HipChat I was able to track down an unfortunate interaction with the uuid2 generator and MS SQL Servers uniqueidentifier column type for String fields (tested on MS SQL Servier 11.x and 12.x).

The issue I was facing was that seemingly randomly hibernate would fail filling collections (ElementCollection or OneToMany, regardless of eager or lazily fetched). The problems started after we switched from the guid generator to uuid2 due to the deprecation logs hibernate generated.

After some delving into hibernate’s logic for handling collections, I found that the issue was related to how uuid2 generates UUIDs (lower case) and how uniqueidentifier columns are bound to String fields (upper case).

I prepared a test case using the hibernate-orm-5 template: https://github.com/schosin/hibernate-test-case-templates

This issue cannot be reproduced with a H2 database as its specific to how uniqueidentifier columns are returned by the database, so I had to alter the project accordingly.

What is happening is the following:
An entity with any collection is fetched from databaase with a lower case uuid (as generated by uuid2, or through any other means).
The data for the collections is fetched from the database and the owner is retrieved from a map with an EntityKey key, where the EntityKey of the owner is with the lower case uuid and the EntityKey of the collection has an upper case uuid as returned by the database (uniqueidentifer column). This fails, so the collection in the entity stays empty and the filled collection is discarded. See the test case comments for more details.

My current workaround is to implement my own IdentifierGenerator which works similar to org.hibernate.id.UUIDGenerator, but can be configured to return String ids as either upper or lower case (in this case configured to upper case).

Not sure if this is something that can be properly handled by hibernate, but maybe some warning logs for those unfortunate enough to attempt what I have done.

Greetings,
schosins


#2

I created the HHH-12943 Jira issue and added 3 test case in he Hibernate project.

We don’t need to change anything in the Hibernate code. You could either use byte[], UUID or just return the uppercase String:

public String getId() {
    return id.toUpperCase();
}

Therefore, I closed this issue since it’s more a SQL Server problem than a Hibernate one. The spec says that UUIDs should be lowercase and most DBs follow the spec. It’s just that SQL Server does not follow the spec as it should. But since we can work around this, there’s no need to change the Hibernate code just to support this very specific use case.