Re: [hibernate-dev] HHH-11042 Count distinct tuples

wrote:

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 _______________________________________________ hibernate-dev mailing list hibernate-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev