Cannot count distinct on SQL Server on embedded ids

Description

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.

Environment

None

Activity

Show:
Christian Beikov
September 1, 2016, 9:48 AM

Thanks, I'll try to bring that up on the mailing list and see how this might be integratable into the core.

Christian Beikov
September 21, 2016, 7:03 AM

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.

Gary Rosales
April 19, 2017, 2:20 AM

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.

Christian Beikov
April 19, 2017, 5:55 AM

The mentioned expression is SQL and should be used in a SQLFunction implementation.

Gary Rosales
April 19, 2017, 7:00 AM

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.

Assignee

Unassigned

Reporter

Giovanni Lovato

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure