Why does Hibernate execute two SELECT queries instead of one when using @ManyToOne(fetch = FetchType.EAGER)

Question

I have the following entities

@Builder
@Getter @Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "Employee")
@Table(schema = "core", name = "employee")
public class EmployeeEntity {

    @Id
    @SequenceGenerator(schema = "core", name = Sequence.EMPLOYEE_ID_SEQ, sequenceName = Sequence.EMPLOYEE_ID_SEQ)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = Sequence.EMPLOYEE_ID_SEQ)
    @Column(name = "id", nullable = false, updatable = false)
    private Integer id;

    @Column(name = "person_id", nullable = false)
    private Integer personId;

    @Column(name = "user_account_id")
    private Integer userAccountId;

    @Column(name = "employee_type_id", nullable = false)
    private Integer employeeTypeId;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "person_id", insertable = false, updatable = false)
    private PersonEntity person;

    @ManyToOne(fetch = FetchType.EAGER, targetEntity = EmployeeTypeEntity.class)
    @JoinColumn(name = "employee_type_id", insertable = false, updatable = false)
    private EmployeeTypeEntity employeeType;

}

and

@Builder
@Getter @Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "EmployeeType")
@Table(schema = "type", name = "employee_type")
@Cacheable @Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
public class EmployeeTypeEntity implements TypeTable {

    @Id
    @Column(name = "id", nullable = false, updatable = false)
    private Integer id;

    @Column(name = "name", length = 100, nullable = false)
    private String name;

    @Column(name = "abbreviation", length = 10)
    private String abbreviation;

    @Column(name = "description", length = 100)
    private String description;

    @Builder.Default
    @Enumerated(EnumType.STRING)
    @Column(name = "active", length = 1, nullable = false)
    private Active active = Active.Y;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "employeeType", targetEntity = EmployeeEntity.class)
    private List<EmployeeEntity> employees;
}

Im using JPA Hibernate with panache in Quarkus and Im using the following method

    @Override
    public Pageable<EmployeeEntity> findAll(PageRequest request) {
        var query = employeePanacheRepository.findAll(
                        Sort.by(request.getSortBy(), Direction.valueOf(request.getSortDirection()))
                )
                .page(Page.of(request.getPage(), request.getSize()));

        return Pageable.<EmployeeEntity>builder()
                .content(query.list())
                .totalPages(query.pageCount())
                .hasPrevious(query.hasPreviousPage())
                .hasNext(query.hasNextPage())
                .build();
    }

The question here is why when query.list() is call Hibernate makes two Select

Hibernate: 
    select
        ee1_0.id,
        ee1_0.employee_type_id,
        ee1_0.person_id,
        ee1_0.user_account_id 
    from
        core.employee ee1_0 
    order by
        ee1_0.id 
    offset
        ? rows 
    fetch
        first ? rows only

Hibernate: 
    select
        ete1_0.id,
        ete1_0.abbreviation,
        ete1_0.active,
        ete1_0.description,
        ete1_0.name 
    from
        type.employee_type ete1_0 
    where
        ete1_0.id = any (?)

And things become more confusing for me when i have this other entity which is parent of EmployeeEntity and get all types table in one select

@Builder
@Getter @Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "Person")
@Table(schema = "core", name = "person")
public class PersonEntity {

    @Id
    @SequenceGenerator(schema = "core", name = Sequence.PERSON_ID_SEQ, sequenceName = Sequence.PERSON_ID_SEQ)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = Sequence.PERSON_ID_SEQ)
    @Column(name = "id", nullable = false, updatable = false)
    private Integer id;

    @Column(name = "first_name", length = 100, nullable = false)
    private String firstName;

    @Column(name = "last_name", length = 100, nullable = false)
    private String lastName;

    @Column(name = "document_number", length = 50, nullable = false)
    private String documentNumber;

    @Column(name = "document_type_id", nullable = false)
    private Integer documentTypeId;

    @Column(name = "gender_type_id", nullable = false)
    private Integer genderTypeId;

    @Column(name = "birth_date", nullable = false)
    private LocalDate birthDate;

    @Column(name = "email", nullable = false)
    private String email;

    @Column(name = "mobile_number", nullable = false)
    private String mobileNumber;

    @UpdateTimestamp
    @Column(name = "update_date", nullable = false)
    private LocalDateTime updateDate;

    @CreationTimestamp
    @Column(name = "create_date", nullable = false)
    private LocalDateTime createDate;

    @Builder.Default
    @Enumerated(EnumType.STRING)
    @Column(name = "active", length = 1, nullable = false)
    private Active active = Active.Y;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "document_type_id", insertable = false, updatable = false)
    private DocumentTypeEntity documentType;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "gender_type_id", insertable = false, updatable = false)
    private GenderTypeEntity genderType;

}

Hibernate: 
    select
        pe1_0.id,
        pe1_0.active,
        pe1_0.birth_date,
        pe1_0.create_date,
        pe1_0.document_number,
        dt1_0.id,
        dt1_0.abbreviation,
        dt1_0.active,
        dt1_0.description,
        dt1_0.name,
        pe1_0.document_type_id,
        pe1_0.email,
        pe1_0.first_name,
        gt1_0.id,
        gt1_0.abbreviation,
        gt1_0.active,
        gt1_0.description,
        gt1_0.name,
        pe1_0.gender_type_id,
        pe1_0.last_name,
        pe1_0.mobile_number,
        pe1_0.update_date 
    from
        core.person pe1_0 
    left join
        type.document_type dt1_0 
            on dt1_0.id=pe1_0.document_type_id 
    left join
        type.gender_type gt1_0 
            on gt1_0.id=pe1_0.gender_type_id 
    where
        pe1_0.id = any (?)

Someone can explain to me why this strange behavior with EmployeeEntity but no with PersonEntity

Edit:

Reading the Hibernate docks provided by @lansana-diomande aeverything points to it being a normal behavior so i make a workaround which is have two queries one for retrieve the data an another for make the count which looks like follow

@Override
    public Pageable<EmployeeEntity> findAll(PageRequest request) {
        var query = employeePanacheRepository.find(
                        "SELECT e FROM Employee e JOIN FETCH e.employeeType et JOIN FETCH e.person p JOIN FETCH p.documentType JOIN FETCH p.genderType",
                        Sort.by("e." + request.getSortBy(), Direction.valueOf(request.getSortDirection()))
                )
                .page(Page.of(request.getPage(), request.getSize()));

        var countQuery = employeePanacheRepository.findAll(
                        Sort.by("e." + request.getSortBy(), Direction.valueOf(request.getSortDirection()))
                )
                .page(Page.of(request.getPage(), request.getSize()));

        return Pageable.<EmployeeEntity>builder()
                .content(query.list())
                .totalElements(countQuery.count())
                .totalPages(countQuery.pageCount())
                .hasPrevious(countQuery.hasPreviousPage())
                .hasNext(countQuery.hasNextPage())
                .build();
    }

This gives me the behavior i was looking for which is just one query for the data an another for the count

Hibernate: 
    select
        ee1_0.id,
        et1_0.id,
        et1_0.abbreviation,
        et1_0.active,
        et1_0.description,
        et1_0.name,
        ee1_0.employee_type_id,
        p1_0.id,
        p1_0.active,
        p1_0.birth_date,
        p1_0.create_date,
        p1_0.document_number,
        dt1_0.id,
        dt1_0.abbreviation,
        dt1_0.active,
        dt1_0.description,
        dt1_0.name,
        p1_0.document_type_id,
        p1_0.email,
        p1_0.first_name,
        gt1_0.id,
        gt1_0.abbreviation,
        gt1_0.active,
        gt1_0.description,
        gt1_0.name,
        p1_0.gender_type_id,
        p1_0.last_name,
        p1_0.mobile_number,
        p1_0.update_date,
        ee1_0.person_id,
        ee1_0.user_account_id 
    from
        core.employee ee1_0 
    join
        type.employee_type et1_0 
            on et1_0.id=ee1_0.employee_type_id 
    join
        core.person p1_0 
            on p1_0.id=ee1_0.person_id 
    join
        type.document_type dt1_0 
            on dt1_0.id=p1_0.document_type_id 
    join
        type.gender_type gt1_0 
            on gt1_0.id=p1_0.gender_type_id 
    order by
        ee1_0.id 
    offset
        ? rows 
    fetch
        first ? rows only

Hibernate: 
    select
        count(*) 
    from
        core.employee ee1_0

Answer 1

this is actually normal, as the fetchMode is ignored on requests to fetch multiple entities.

I haven't been able to find a reference to this in the new documentation, but this old documentation states that :

The fetch strategy defined in the mapping document affects:

• retrieval via get() or load()
• retrieval that happens implicitly when an association is navigated
• Criteria queries
• HQL queries if subselect fetching is used

The behavior is also mentioned on this more up-to-date documentation

To resolve your problem, you can use join fetch or entity graph