Converter issue with jOOQ when mapping nested records

Viewed 212

I have two basically identical queries, one using multiset and the other using row, but they behave differently with regard to converting from SQL datatype to Java/Kotlin.

For example, here's the multiset:

multiset(
    select(
        CALENDAR_ENTRIES.ID,
        CALENDAR_ENTRIES.EVENT_DATE
    )
        .from(CALENDAR_ENTRIES)
        .where(cond)
).`as`("calendar_entry")
    .convertFrom { r: Result<Record2<Long, LocalDateTime>> ->
        r.map(
            Records.mapping { id: Long?, eventDate: LocalDateTime? ->
                CalendarEntry(id, eventDate!!)
            }
        )
    }

and here's the row:

row(
    CALENDAR_ENTRIES.ID,
    CALENDAR_ENTRIES.EVENT_DATE
)
    .mapping { id: Long?, eventDate: LocalDateTime ->
        CalendarEntry(id, eventDate)
    }

For argument's sake, assuming the DB has 1 record, with eventDate being (as a String) '2022-02-21 09:30:00'.

With the first query, I successfully get my Field<List<CalendarEntry>> instance. With the second query (row()), I get "java.time.format.DateTimeParseException: Text '2022-02-21 09:30:00' could not be parsed at index 10".

It seems that jOOQ is able to correctly apply a converter for the multiset call, but not the row call. Am I doing something wrong?

1 Answers

I'm assuming you're using PostgreSQL.

You probably ran into this bug here: https://github.com/jOOQ/jOOQ/issues/13117. The difference in behaviour is explained by the fact that as of jOOQ 3.16:

  • MULTISET gets emulated using JSONB functionality
  • ROW gets
    • emulated using JSONB functionality when nested within multiset
    • implemented natively using PostgreSQL ROW expressions when used at the top level

Bug https://github.com/jOOQ/jOOQ/issues/13117 seems to affect also top level ROW types, not just UDTs. It has been fixed recently, and might be backported to 3.16 and 3.15.

Related