Support for DISTINCT usage in aggregates

[anddero]

Without DISTINCT

For a query like

SELECT
    SUM(my_table.my_col)
FROM
    -- ...

you can write the equivalent CriteriaQuery

// ... previously defined CriteriaBuilder builder, CriteriaQuery query, Root<MyTable> root
query.select(builder.sum(root.get("myCol")));
// ...

With DISTINCT

But for a query like

SELECT
    SUM(DISTINCT my_table.my_col)
FROM
    -- ...

I suggest adding the following method to CriteriaBuilder

<X> Expression<X> distinct(Expression<X> x)

so we could write

// ... previously defined CriteriaBuilder builder, CriteriaQuery query, Root<MyTable> root
query.select(builder.sum(builder.distinct(root.get("myCol"))));
// ...

as currently there does not seem to be a way to achieve this. A countDistinct method exists, but what about DISTINCT for other aggregates?

[gavinking]

It's true that we do allow avg(distinct) and sum(distinct) in JPQL—and even the completely meaningless min(distinct) and max(distinct)—but how are these actually useful?

Unless someone can convince me that avg(distinct) is a meaningful and useful thing to write, I would say let's not bloat out the CriteriaBuilder interface.

[anddero]

I don't know what it was at the time of writing. I guess the data could have been a result of a subquery. Now I can't think of anything practical, maybe one of the upvoters can.

[gavinking]

@beikov, @andyjefferson, @dazey3, @TomBeckett you all voted for this. Can someone describe a legit usecase for it?

[beikov]

Sorry, I don't remember what it was that drove me to upvoting this ticket. Maybe consistency with JPQL? I can't think of a real usecase for this feature.

[gavinking]

Thanks. I'm going to close it then. If someone can think of something, it can always be reopened.

[Mettbrot]

Hi, I have a usecase for this: We would like to aggregate all possible values that exist in a column, so we could distribute the values using powers of two which allows us to trace back to the individuals but ONLY if they are distinct before the sum.

@andyjefferson, @dazey3, @TomBeckett any of you remember your usecases?