JOOQ MappingException when using multiset select

Question

Hi I am attempting to perform a jooq multiset select to map a one to many relationship into my POJOs.

org.jooq.exception.MappingException: An error ocurred when mapping record to class io.comp.biz.core.task.model.TaskDetails

    at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:916)
    at org.jooq.impl.DefaultRecordMapper.map(DefaultRecordMapper.java:610)
    at org.jooq.impl.AbstractRecord.into(AbstractRecord.java:804)
    at org.jooq.impl.ResultQueryTrait.fetchOneInto(ResultQueryTrait.java:530)
    at io.comp.biz.core.task.query.TaskQueryRepository.findTaskDetailsById(TaskQueryRepository.java:172)

..... 

Caused by: org.jooq.exception.DataTypeException: No Converter found for types org.jooq.Converters$UnknownType and java.util.List
    at org.jooq.impl.Tools.converterOrFail(Tools.java:1216)
    at org.jooq.impl.Tools.converterOrFail(Tools.java:1225)
    at org.jooq.impl.AbstractRecord.get(AbstractRecord.java:351)
    at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:943)
    at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:873)
    ... 112 more

These are my POJOS:

@NoArgsConstructor
@AllArgsConstructor
@Builder
@Data
public class TaskDetails {
    private UUID id;
    private UUID tenantId;
    private String title;
    private String description;
    private LocalDateTime createdAt;
    private String priority;
    private Long numericId;
    private UUID assigneeId;
    private UUID statusId;
    private UUID reporterId;
    private Boolean deleted;
    private LocalDateTime modifiedAt;
    private UUID modifiedBy;
    private List<TaskDate> taskDates;
}

@NoArgsConstructor
@AllArgsConstructor
@Builder
@Data
public class TaskDate {
    private UUID id;
    private UUID taskId;
    private String type;
    private LocalDate date;
    private LocalDateTime createdAt;
    private String createdBy;
    private String modifiedBy;
    private LocalDateTime modifiedAt;
}

This is my jooq query:

public Optional<TaskDetails> findTaskDetailsById(UUID taskId) {
        TaskDetails taskDetails =
                dslContext.select(
                                TASK.ID,
                                TASK.TENANT_ID,
                                TASK.TITLE,
                                TASK.DESCRIPTION,
                                TASK.CREATED_AT,
                                TASK.PRIORITY,
                                TASK.NUMERIC_ID,
                                TASK.ASSIGNEE_ID,
                                TASK.STATUS_ID,
                                TASK.REPORTER_ID,
                                TASK.DELETED,
                                TASK.MODIFIED_AT,
                                TASK.MODIFIED_BY,
                                multiset(dslContext.select(TASK_DATE.ID,
                                        TASK_DATE .TASK_ID,
                                        TASK_DATE.TYPE,
                                        TASK_DATE.DATE,
                                        TASK_DATE.CREATED_AT,
                                        TASK_DATE.CREATED_BY,
                                        TASK_DATE.MODIFIED_BY,
                                        TASK_DATE.MODIFIED_AT).from(TASK_DATE).where(TASK_DATE.TASK_ID.eq(TASK.ID))).as("taskDates").convertFrom(r -> r.map(mapping(this::mapTaskDate))))
                        .from(TASK)
                        .where(TASK.ID.in(taskId)).fetchOneInto(TaskDetails.class);


        return Optional.ofNullable(taskDetails);
}

    private TaskDate mapTaskDate(UUID id, UUID taskId, String type, LocalDate taskDate, LocalDateTime createdAt, String createdBy, String modifiedBy, LocalDateTime modifiedAt) {
        return TaskDate.builder()
                .id(id)
                .taskId(taskId)
                .type(type)
                .date(taskDate)
                .createdAt(createdAt)
                .createdBy(createdBy)
                .modifiedBy(modifiedBy)
                .modifiedAt(modifiedAt)
                .build();
    }

• spring-boot: (2.6.2) with spring-boot-starter-jooq dependency
• jooq version: 3.15.1

From debugging the code i can see that jooq's AbstractRecord.java is throwing the exception when attempting to get the type of taskDates but I am unsure how to resolve this exception

Answer 1

jOOQ added MULTISET support in version 3.15.0, just one patch release before the version that you're using. Since then, there had been a ton of bugfixes in this area, see the release notes. Until version 3.17.6, the release notes reference the word "MULTISET" 65 times. I vaguely recall a bug like this.

For example, there's #12208, which was fixed in jOOQ 3.16.0 and 3.15.3. You should definitely upgrade, at least to the latest 3.15 patch release, possibly even to 3.17.