Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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.

Activity

Gary RosalesApril 19, 2017 at 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.

Christian BeikovApril 19, 2017 at 5:55 AM

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

Gary RosalesApril 19, 2017 at 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 BeikovSeptember 21, 2016 at 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.

Christian BeikovSeptember 1, 2016 at 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.

Fixed

Details

Assignee

Reporter

Components

Fix versions

Priority

Created August 12, 2016 at 1:50 PM
Updated October 11, 2021 at 6:26 PM
Resolved September 23, 2021 at 4:04 PM