How to map left-joined tables to nested objects in JOOQ

I would like to left-join two tables and then map it into the following object where the metadata object should be null if no metadata was joined:

data class ObjectDto(
   val objectId: String,
   val name: String,
   val metadata: MetadataDto?
)

data class MetadataDto(
   val description: String,
   val comment: String
)

I tried to follow the docs about nested objects but was not able to map it correctly with the following code:

    suspend fun getArrangements(objectIds: List<String>): List<ObjectDto> {
        return Flux.from(
            jooqDsl
                .select(
                    OBJECT.OBJECT_ID,
                    OBJECT.NAME,
                    row(
                        METADATA.DESCRIPTION,
                        METADATA.COMMENT,

                    ).mapping(::MetadataDto).`as`("METADATA")
                )
                .from(
                    OBJECT.leftJoin(METADATA)
                        .on(OBJECT.OBJECT_ID.eq(METADATA.OBJECT_ID))
                )
                .where(OBJECT.OBJECT_ID.`in`(objectIds))
        )
            .map {
                it.into(ObjectDto::class.java)
            }
            .collectList()
            .awaitSingle()
    }

The executed query is returning the correct data however the metadata is not mapped and always null. What am I doing wrong?

Solution

It looks like a bug: https://github.com/jOOQ/jOOQ/issues/14541. Seeing that at the time 3.17.7 was the latest version, there doesn't seem to be an integration test that covers top level nested records, so I'd expect this feature simply doesn't work for R2DBC backed queries yet. The issue was fixed for 3.17.8 and 3.18.0

The workaround is to map things programmatically inside of Flux.map (don't call it.into(...) but instead use Records.mapping() for type safe mapping of a Record4<T1, T2, T3, T4> to whatever) and not use the row() operator.