[hibernate-dev] HHH-11042 Count distinct tuples

[Christian Beikov]

Hey,

I wanted to start a discussion regarding this issue: 
https://hibernate.atlassian.net/browse/HHH-11042

Although the Dialect class contains the method 
"supportsTupleDistinctCount", it is never used, so when doing a count 
distinct on a tuple, it just renders the tuple instead of doing a 
fallback or throwing an error.

I suggested the OP to override the count function in the dialect to do 
whatever he thinks is best but then I realized that the count function 
is not even used as the logic is hard coded in some locations. The 
problematic location in this case is 
"org.hibernate.hql.internal.ast.tree.IdentNode.resolveAsAlias" which 
does not consider the function at all but renders the SQL directly.

After suggesting him to introduce a custom function instead and some 
discussion on how count distinct could be reliably implemented I think I 
found a solution that might work for most databases.

On stackoverflow and other sites it is often suggested to use a checksum 
to workaround this limitation which obviously is not a good idea. I 
proposed to do concatenation with a separator that doesn't appear in the 
string and apparently the character '\0' is a valid character which 
makes it a good candidate as that should normally not appear in a string.

The final solution to the problem looks something like the following

count(distinct case when col1 is null or col2 is null then null else 
col1 || '\0' || col2 end) + count(case when col1 is null or col2 is null 
then 1 end)

The first count does a count distinct on all columns concatenated with 
'\0' where all values are not null. The second just counts the cases 
where one of the column values was null. Together that emits the proper 
count based on the assumption that '\0' does not appear in the columns.

What do you think about that solution? I would like to implement it that 
way and do a PR.

I would also like to make use of the count function registered in the 
dialect to make this overrideable. Hope that's okay?


Regards,
Christian