How can I use unionAll in Jooq to union two selects for two different records but with records correspondents to tables with same columns and types

I have two records correspondent to two different tables (recordA, tableA, recordB and tableB). TableA and TableB have the same column's names and same types. How can I select then using Jooq and map the result to the different records?

I've tried this approach:

fun findEntitiesByGroupId(groupId: UUID): ResultData {
    val entityAQuery = DSL.using(configuration)
        .selectFrom(ENTITY_A)
        .where(ENTITY_A.GROUP_ID.eq(groupId))
    val entityBQuery = DSL.using(configuration)
        .selectFrom(ENTITY_B)
        .where(ENTITY_B.GROUP_ID.eq(groupId))

    val entityA = mutableListOf<EventHistory>()
    val entityB = mutableListOf<EventHistory>()

    entityAQuery.unionAll(entityBQuery).forEach {
        when (it) {
            is EntityARecord -> entityA.add(mapEntityA(it))
            is EntityBRecord -> entityB.add(mapEntityB(it))
            else -> throw AssertionError("Invalid entity type")
        }
    }
    return ResultData(entityA, entityB)
}

I'm getting the compile error in unionAll method:

Type mismatch.
Required:
Select<out EntityARecord!>!
Found:
SelectConditionStep<EntityBRecord!>

I didn't find any Jooq documentation about this union scenario, just for cases where we have the same record type.

Solution

Regarding the compilation error:

The two types aren't compatible in Java / Kotlin, so you cannot use selectFrom(table) the way you're doing. You could just use select().from(table) instead, removing row type safety from the query, or alternatively, list all columns explicitly.

Regarding the mapping expectations:

It's not possible for jOOQ to automatically map rows originating from UNION or similar SQL operators to the "correct" mapper. I've written an entire blog post on this topic to illustrate the details of why this is impossible:

https://blog.jooq.org/how-to-use-jooqs-converters-with-union-operations/

In short, there's no way for jOOQ to know which record belongs to which UNION ALL subquery, automatically. As such, jOOQ will only ever consider the row type (and associated converters / mappers) of the first UNION ALL subquery.

You can, however, add a discriminator column to your unions and make the decision yourself based on that, e.g.:

SELECT a.*, 'a' AS discriminator
FROM a
UNION ALL
SELECT b.*, 'b' AS discriminator
FROM b

And then, e.g.:

.forEach {
    when (it["discriminator"]) {
        "a" -> entityA.add(mapEntityA(it))
        "b" -> entityB.add(mapEntityB(it))
        else -> throw AssertionError("Invalid entity type")
    }
}