when multiple aggregations are used in Query.select(String), the further ones appear in the group by

[Incanus3]

only non-aggregation clauses from the select string should be added to the group by part of the query. this is not the case though:

@Entity
@DbName(TEST_DB_NAME)
data class TestEntity(
    @Id
    var id: String = UUID.randomUUID().toString(),

    @Column
    var name: String = "",
) {
    @Aggregation("count(*)")
    val count: Int = 0

    @Aggregation("group_concat(id)")
    val ids: String = ""

    override fun hashCode() = id.hashCode()
    override fun equals(other: Any?) = other is TestEntity && other.id == id
}

class MultipleAggregationsTest {
    private val dsConfig = DataSourceConfig().also {
        it.username = "sa"
        it.password = "sa"
        it.url = "jdbc:h2:mem:test"
        it.driver = "org.h2.Driver"
    }

    private val dbConfig = DatabaseConfig().also {
        it.name = TEST_DB_NAME

        it.isDdlRun = true
        it.isDdlGenerate = true
        it.isDefaultServer = false

        it.setDataSourceConfig(dsConfig)

        it.addClass(TestEntity::class.java)
        it.addClass(TestResourceWithLobAndAggregation::class.java)
    }

    private val db = DatabaseFactory.create(dbConfig)

    @Test
    fun `multiple aggregations`() {
        db.insert(TestEntity(name = "1"))
        db.insert(TestEntity(name = "1"))
        db.insert(TestEntity(name = "2"))

        LoggedSql.start()
        val results = db.createQuery(TestEntity::class.java)
            .select("name, count(*) as count, group_concat(id) as ids")
            .findList()
        val sql = LoggedSql.stop().joinToString("\n").also { println(it) }

        println("results:")
        results.forEach {
            println("name: ${it.name} count: ${it.count} ids: ${it.ids}")
        }
    }
}

the query in this case should be:

select t0.name, count(*) count, group_concat(t0.id) ids from test_entity t0 group by t0.name;

but this is run instead:

select t0.name, count(*) count, group_concat(t0.id) ids from test_entity t0 group by t0.name, group_concat(t0.id);

when

QTestEntity(db).select(QTestEntity._alias.name, QTestEntity._alias.count, QTestEntity._alias.ids)

is used instead, it works fine (presumably because ebean knows both the count and ids properties represent aggregations), but sadly I can't do that for two reasons:

• there is @Aggregation is applied to all queries if entity also has a @Lob column #3682 and my entity does have a @Lob column
• I need to be able to make this query platform agnostic, but only mysql and h2 support group_concat, other dbs have for example array_agg, so I need to use a dynamic query here

[rbygrave]

.select("name, count(*) as count, group_concat(id) as ids")

In FormulaPropertyPath there is the defined known list of aggregation functions being:

  private static final String[] AGG_FUNCTIONS = {"count", "max", "min", "avg", "sum"};

So this is how ebean is determining that count is an aggregate function ... and group_concat is not in that list and so not determined to be an aggregate.

Q: Should group_concat [and array_agg `] somehow be added to this list of known aggregate functions?

That is what is needed for the .select("name, count(*) as count, group_concat(id) as ids") to work, to specifically determine that the group_concat is a known aggregation.

.select(QTestEntity._alias.name, QTestEntity._alias.count, QTestEntity._alias.ids)

This is selecting by the "logical property names", and thus it knows the properties that make up the projection [and therefore which are aggregation properties].

Which leads to another future potential option. Some other annotations like @Formula have a platform attribute and that allows those annotations to have platform specific formula/mapping etc.

Q: Should @Aggregation have a platform attribute added to it support cases with platform specific aggregation?

I don't know how useful/successful/applicable that would be. This is something we should keep in mind and see if there are good use cases where it would work well etc.

---

So I think the action/fix for this specific issue would be to add group_concat to the list of known aggregation functions [and probably others like listagg].

[Incanus3]

hi @rbygrave and thanks for your time. well, for my current use-case I would need group_concat (mysql, h2), string_agg (postgre, mssql) and listagg (oracle), but this seems to me like a pretty annoying path for you - every time someone stumbles on another function that doesn't work, they will open another issue and you'll have to decide whether to support it. adding platform param to @Aggregation definitely seems like a much nicer solution, which would also fix the problem for typed query building. another solution I could imagine would be to add an option to configure this list of known aggregation functions on DatabaseConfig or DataSourceConfig level.

[rbygrave]

need group_concat (mysql, h2), string_agg (postgre, mssql) and listagg (oracle),

Linked PR adds support for those.

every time someone stumbles on another function that doesn't work

Adds a AggregationFormulaContext which can be set on the DatabaseBuilder / DatabaseConfig such that apps can override and register new aggregation functions etc

adding platform param to @Aggregation

Going to leave this option for now and look to add that in a later followup.

[Incanus3]

amazing as usual. again, thank you so much, not just for this, but all your work.