I'm having an issue with Hibernate "JOIN FETCH" behaviour on a SQL Server database defined with a case-insensitive collation. The version of Hibernate is 3.2.4. Whilst it would be interesting to know if any later version "fixes" this problem upgrading is not a viable short-term option.

Our code performs a query where a number of entity types are joined. One pair of those types has a (yuck) meaningful String primary identifier. In some instances the foreign key half of a relationship is stored with a different case on the referencing table to that on the primary key table. As far as the database is concerned this reference is valid (the case-insensitive collation).

A simplified version of the query:

Where the returned data happens to contain both halves of the reference of the same case the eager fetch is performed as expected. However, when the cases of those String identifiers differ the fetch is not eager. Thus, when non-key properties of the referencing entity are accessed the Hibernate session is again used, and traverses the relationship without problem.

So, the later code is satisfied by the previous (eager-fetched) results if the case of the Primary Key in table PRIMARY matches that of the Foreign Key in table REFERENCE, but causes further use of the Hibernate session if those cases don't match.

The immediate problem is that our code ends the transaction prior to iterating the results and using them in a potentially time-consuming operation. Thus, the session is no longer in a valid state when those extra properties of the referencing entity are accessed (but only in the situation where the case of the key on either side of the reference differs). Extending the transaction to remain open during the iterations is not a desirable solution.

At a superficial level it appears that Hibernate is using a String.equals() style comparison during the query (at least for the "JOIN FETCH" part) but a case-insensitive reference appropriate to the database when the object graph is later traversed.

My questions, then, are:
1. With a case-insensive database why does the query act in a case-sensitive manner for eager fetching?
2. Is there anything I can do to force the "JOIN FETCH" to eager-load mixed-case key pairs?
3. How does Hibernate determine case-sensitivity? I see a SQLServerDialect.areStringComparisonsCaseInsensitive() method that always returns true but am not sure where this is used, and it would not appear to be correct in all cases given that SQL Server might just as well be configured case-sensitive.
4. Why would this referencing behaviour differ between the query and the object graph traversal?

I would be grateful for any assistance on this.

Thanks,
Simon