JPA Criteria API generates extra inner join with the use of a subtype class

Question

We have 3 classes Case, Meeting and SpecialMeeting (zie objects below). When we use the JPA Criteria Api in the following way:

    static Specification<Case> betweenEnddateSpecialMeeting(LocalDate start, LocalDate end) {
        return (root, query, cb) -> {
            if (start != null || end != null) {
                Join<Case, SpecialMeeting> join = cb.treat(root.join(Case_.meetings), SpecialMeeting.class);
                Path<LocalDate> pathEnddate = join.get(SpecialMeeting_.actuelEnddate);

                return findByStartAndEndDate(start, end, cb, pathEnddate);
            } else {
                return null;
            }
        };
    }
    private static Predicate findByStartAndEndDate(LocalDate start, LocalDate end, CriteriaBuilder cb, Path<LocalDate> pathEnddate) {
        if (start != null && end != null) {
            return cb.and(cb.greaterThanOrEqualTo(pathEnddate, start),
                          cb.lessThanOrEqualTo(pathEnddate, end));
        } else if (start != null) {
            return cb.greaterThanOrEqualTo(pathEnddate, start);
        } else {
            return cb.lessThanOrEqualTo(pathEnddate, end);
        }
    }

Hibernate generates the following query:

    select
        case0_.id as id1_73_,
        case0_.identificationcode as identifi8_73_,
        case0_.version as version15_73_
    from
        l_cases case0_ 
    inner join
        l_meetings meeting1_ 
            on case0_.id=meeting1_.case_id 
    inner join
        l_meetings meeting2_ 
            on case0_.id=meeting2_.case_id 
    inner join
        l_special_meetings meeting2_1_ 
            on meeting2_.id=meeting2_1_.id 
    where
        meeting2_1_.actuel_enddate<=?

The cb.treat(root.join(Case_.meetings), SpecialMeeting.class) which we use for the subtype SpecialMeeting, generates an extra inner join for l_meetings. This results in duplicate records. Can someone help us to get the right query for 1 inner join on l_meetings?

We are using the following versions:

hibernate 5.4.20-Final
spring-boot: 2.3.3.RELEASE

Regards,
Andre Torensma

Entities used in above example.

@Slf4j
@Audited
@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "L_CASES")
@SequenceGenerator(name = "LCN", sequenceName = "L_CASE_SEQ", allocationSize = 1)
public class Case implements IdentifiableEntity {

    @Id
    @Column(nullable = false, precision = 10)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "LCN")
    private Long id;
    @Version
    private Long version;
    @Column(precision = 50)
    private String identificationcode;
    @Valid
    @OneToMany(mappedBy = "case", cascade = { CascadeType.ALL })
    private List<Meeting> meetings = new ArrayList<>();

    @Override
    public String getidentificationCode() {
        return identificationcode;
    }
}


@Slf4j
@Audited
@Getter
@Setter
@Entity
@NoArgsConstructor
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "TYPE", length = 2, discriminatorType = DiscriminatorType.STRING)
@Table(name = "L_MEETINGS")
@SequenceGenerator(name = "LMS", sequenceName = "L_MEETINGS_SEQ", allocationSize = 1)
public class Meeting {
    @Id
    @Column(nullable = false, precision = 10)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "LMS")
    private Long id;
    @Version
    private Long version;
    @Column(length = 36)
    private String identificationcode;
    @Column(nullable = false)
    private LocalDate ldate;
    @Column(name = "DATETIME_REGISTRATION", nullable = false)
    private LocalDateTime datetimeRegistration;
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "MEETING_ID")
    private Meeting relatedMeeting;
    @ManyToOne
    @JoinColumn(name = "CASE_ID", nullable = false)
    private Case case;

    public Optional<SpecialMeeting> getSpecialMeeting() {
        if (this instanceof SpecialMeeting) {
            return Optional.of((SpecialMeeting) this);
        }
        return Optional.empty();
    }
}


@Audited
@Getter
@Setter
@Entity
@NoArgsConstructor
@EqualsAndHashCode(callSuper = true)
@Table(name = "L_SPECIAL_MEETINGS")
@DiscriminatorValue("BB")
public class SpecialMeeting extends Meeting {
    @Column
    private LocalDate startdate;
    @Column
    private LocalDate enddate;
    @Column(name = "ACTUEL_ENDDATE")
    private LocalDate actuelEnddate;
    @Column
    private String description;

}```

Answer 1

The use of treat() in combination with different inheritance strategies is known to be rather troublesome. See HHH-9594 and related issues, it is being worked on.

I can confirm however that hibernate 6.1.3 works better, however the preferred solution has yet to be build.
Generated query with hibernate 6.1.3 and spring-boot 3.0.0-M4

select
    c1_0.id,
    c1_0.identificationcode,
    c1_0.version 
from
    l_cases c1_0 
join
    (l_meetings m1_0 
join
    l_special_meetings m1_1 
        on m1_0.id=m1_1.id) 
            on c1_0.id=m1_0.case_id 
    where
        case 
            when case 
                when m1_1.id is not null then 1 
                when m1_0.id is not null then 0 
            end=1 then m1_1.actuel_enddate 
            else null 
        end>=?

Since you've already built a solution that fits you're specific needs I won't go into detail about several workarounds. There are a lot already out there like this thread.