[hibernate-dev] HHH-11042 Count distinct tuples
christian.beikov at gmail.com
Thu Sep 1 06:18:47 EDT 2016
I wanted to start a discussion regarding this issue:
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
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?
More information about the hibernate-dev