Regardless of having supportsTupleDistinctCounts returning false on the selected dialect, Hibernate tries to do a COUNT(DISTINCT col1_, col2_) when counting entities with an embedded id and the JDBC driver throws a Incorrect syntax near ',' exception.
I suppose this is a limitation of SQL Server, but in my opinion this should not throw an exception and also it should be somehow possible to instruct the dialect to replace the COUNT(DISTINCT col1_, col2_) syntax with a COUNT(DISTINCT CHECKSUM(col1_, col2_)) which may work even in SQL Server.
This was initially discussed within HHH-7165.
Thanks, I'll try to bring that up on the mailing list and see how this might be integratable into the core.
Ok so I just implemented this myself and noticed that counting nulls as different values is not ANSI compliant.
The ANSI compliant way of encoding would be:
Note that the columns are concatenated with an empty string to get implicit conversion. NULL is encoded as '\0' and empty strings are encoded as '\0' + element number.
This should produce collision free strings as long as the column values don't contain the '\0' character.
Question regarding that HQL workaround. Does it currently work in the latest hibernate release? I am getting a syntax exception because coalesce cannot be inside a count.
The mentioned expression is SQL and should be used in a SQLFunction implementation.
Ah, my bad. I misunderstood the comment. I am using querydsl right now to create the HQL that gets called and this pops up when I try to use a page request. The library calculates the totals and it fails because the SQL generated is invalid in mssql.