Jooq: Add DSL.aggregate() and DSL.aggregateDistinct() as plain SQL AggregateFunctions

[lukaseder]

Currently, only Field<T> objects can be produced using jOOQ's plain SQL capabilities. This question here shows that this might also be useful for producing org.jooq.AggregateFunction<T>:
http://stackoverflow.com/questions/12086831/how-jooq-distinct-or-countdistinct-more-than-one-column/12087220

The obvious scope is:

• [x] Support creating the AggregateFunction reference based on any arbitrary Name and arbitrary number of parameters
• [x] Support a DISTINCT keyword, which will be located in <name>(DISTINCT <args>)
• [x] Support a FILTER clause, which
  
  
  
  • [x] is implemented natively as <name>(<args>) FILTER (WHERE <condition>) if FILTER tends to be natively supported
  
  
  • [x] is implemented using CASE otherwise: <name>(CASE WHEN <condition> THEN <arg1> END). This doesn't work for all aggregate functions, but may be acceptable
  
  
• [x] Support an OVER clause as <name>(<args>) OVER (...). The usual window function clauses will be supported, not special clauses like RESPECT NULLS or IGNORE NULLS

Out of scope: (see #11480)

• [x] Support an ORDER BY clause as <name>(<args> ORDER BY <sort specification>)
• [x] Support a WITHIN GROUP clause as <name>(<args>) WITHIN GROUP (ORDER BY <sort specification>)

[ccjmne]

I'll be following this thread, as I wanna contribute more (and I asked this related question on Stack Overflow).

I'm not quite familiar with jOOQ's codebase yet but if this gets enough attention so that we start discussing its implementation, I wanna be there 🙂.

[lukaseder]

Thanks for the offer, @ccjmne

[lukaseder]

@baev: Assuming fixing https://github.com/jOOQ/jOOQ/issues/11238 won't suffice for you, what are your expectations towards this feature here? I've added the "obvious scope" to the issue description. Any special case I might be missing?

[baev]

JOOQ already has the most of the aggregate functions supported, but still lacks support of some (eg statistic aggregates). In addition, there is a possibility to define user aggregates, so by now the only way is to use raw sql templating (creating multiple util methods: simple, with over clause, with filter clause, with both).

Having DSL.aggregateFunction(name, type, params...) will help a lot.

Assuming #11238 is fixed, the issue in no hurry

[lukaseder]

still lacks support of some (eg statistic aggregates)

Which ones are you missing in particular?

In addition, there is a possibility to define user aggregates

Those should be contained in your generated code:
https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/user-defined-aggregate-functions

Does that work for you?

Having DSL.aggregateFunction(name, type, params...) will help a lot.

Assuming #11238 is fixed, the issue in no hurry

Thanks for the confirmation. However, I've just noticed that the class implementing user-defined aggregate function (org.jooq.impl.DefaultAggregateFunction) is already there. We're just not offering it as public API yet, and are lacking thorough integration tests (thus there might be 1-2 bugs).

So, I think this should still fit for the discussed schedule, and can be backported to 3.14.8.

[baev]

Which ones are you missing in particular?

At the moment everything is fine. But I can see myself using some of https://www.postgresql.org/docs/12/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE in near future

Those should be contained in your generated code:
https://www.jooq.org/doc/dev/manual/sql-building/column-expressions/user-defined-aggregate-functions

Does that work for you?

Yeah, didn't know that

However, I've just noticed that the class implementing user-defined aggregate function (org.jooq.impl.DefaultAggregateFunction) is already there.

Yeah, thats actually the class that I blamed of being internal. From API user perspective I can't see the difference between function & aggregateFunction, so why not provide such API? Plus this will give users additional space to workaround bugs and limitations of JOOQ API like in case with jsonArrayAgg.

[lukaseder]

But I can see myself using some of https://www.postgresql.org/docs/12/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE in near future

We support linear regression, standard deviation, variance, already. Covariance is implemented and ready for 3.15: https://github.com/jOOQ/jOOQ/issues/11157, correlations will be supported as well in 3.15: https://github.com/jOOQ/jOOQ/issues/11470

Yeah, thats actually the class that I blamed of being internal. From API user perspective I can't see the difference between function & aggregateFunction, so why not provide such API?

Well, a function is very simple: Name, return type, parameters. An aggregate function has a lot of special clauses, and changes the query semantics (e.g. produces an implicit GROUP BY, cannot be located in all clauses, etc.). With respect to query transformation, it's important to be able to distinguish between the two. It might not affect you, but it will be important in the near future.

Plus this will give users additional space to workaround bugs and limitations of JOOQ API like in case with jsonArrayAgg.

To some extent, yes. But only as far as aggregate functions are sufficiently standardised. JSON_ARRAYAGG, as specified in the SQL standard and implemented by a few dialects, has so many new, special clauses, including yet another way of handling nulls (NULL ON NULL vs ABSENT ON NULL), a JSON output clause in case a dialect doesn't have an actual JSON type. JSON_OBJECTAGG has the key uniqueness clause, too...

In any case, this will still be useful for 80% of the missing aggregate function support.

[lukaseder]

Another use-case for this is to work around the historic design decisions that COUNT() returns Integer and AVG() and SUM() return BigDecimal, see also: https://github.com/jOOQ/jOOQ/issues/868, https://github.com/jOOQ/jOOQ/issues/3415, https://github.com/jOOQ/jOOQ/issues/9555, and others

[lukaseder]

The backport will include most of the suggested features, but the WITHIN GROUP (ORDER BY ...) and aggregate ORDER BY clauses will need more thought and API design. E.g. both percentileCont() and arrayAgg() do not return any AggregateFunction compatible types, but their own types with their own syntax. I will outsource that work to a new issue, which should ship in 3.15, but will probably not be backported: https://github.com/jOOQ/jOOQ/issues/11480

[lukaseder]

Fixed in jOOQ 3.15.0 and 3.14.8 (#11482)

[lukaseder]

For the record, until 3.14.8 has shipped, licensed customers can access a 3.14.8-SNAPSHOT version for early access here: https://www.jooq.org/download/versions

[lukaseder]

See also #11541 CustomAggregateFunction

[lukaseder]

We support linear regression, standard deviation, variance, already. Covariance is implemented and ready for 3.15: #11157, correlations will be supported as well in 3.15: #11470

@baev: For the record, #11470 is implemented now for jOOQ 3.15 (CORR support). The remaining ones should be available already.