Hibernate CriteriaBuilder generates wrong sql output while using subquery as a selection

Question

We have two related entity - Customer and User. There is OneToMany-ManyToOne relation between this entities as I added below. I'm trying to get many side(User) count while getting Customer pageable list with subquery. I saw that when I debug hibernate generates irrelevant sql output, associates join unlike subquery is. Entities:

Customer

@Getter
@Setter
@Entity
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String code;

    @Column(nullable = false)
    private String name;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "customer")
    private Set<User> users = new HashSet<>();

---

User

@Getter
@Setter
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @NotEmpty
    private String firstName;

    private String lastName;

    @Email
    private String email;

    @NotEmpty
    private String password;

    private String code;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "customer_user",
            joinColumns = {@JoinColumn(name = "user_id")},
            inverseJoinColumns = {@JoinColumn(name = "customer_id")})
    private Customer customer;

Repository method is like below:

  public PageableData<List<CustomerBaseDto>> getAllCustomers(PageableBaseRequest<GetFilteredCustomers> request) {
        CriteriaQuery<CustomerBaseDto> query = criteriaBuilder.createQuery(CustomerBaseDto.class);
        Root<Customer> root = query.from(Customer.class);

        Subquery<Long> userSubquery = query.subquery(Long.class);
        Root<User> userRoot = userSubquery.from(User.class);

        userSubquery.select(criteriaBuilder.count(userRoot.get(User_.id)))
                .where(criteriaBuilder.equal(userRoot.get(User_.customer).get(Customer_.id), root.get(Customer_.id)));

        query.multiselect(
                root.get(BaseEntity_.id),
                root.get(BaseEntity_.enabled),
                root.get(Customer_.code),
                root.get(Customer_.email),
                root.get(Customer_.name),
                userSubquery);

        return getPageableData(request.getPageNumber(), request.getPageSize(), session, root, query);
    }

and sql output which hibernate does:

select c.id                          as customer_id,
       c.code                        as customer_code,
       c.name                        as customer_name,
       (select count(u.id)
        from users u
                 left outer join customer_user cu on u.id = cu.user_id
        where cu.customer_id = u.id) as user_count
from customer c
limit ? offset ?

As you see subquery where clause must be cu.customer_id = c.id instead of cu.customer_id = u.id.

---

Versions

#	Version
Java	1.8
Spring Boot	2.7.17
Hibernate	5.6.15.Final
Hibernate Jpamodelgen	5.6.15.Final

Expected query must be like below

select c.id                          as customer_id,
       c.code                        as customer_code,
       c.name                        as customer_name,
       (select count(u.id)
        from users u
                 left outer join customer_user cu on u.id = cu.user_id
        where cu.customer_id = c.id) as user_count
from customer c
limit ? offset ?

Answer 1

You can start by optimizing the target sql:

SELECT c.id AS customer_id,
       c.code AS customer_code,
       c.name AS customer_name,
       COUNT(cu.user_id) AS user_count
FROM customer c
LEFT JOIN user u ON c.id = u.customer_id
GROUP BY c.id, c.code, c.name
LIMIT ? OFFSET ?

The next spec query implementation will be simpler:

@Autowired
EntityManager entityManager;

private record CustomerBaseDto(Long id, String code, String name, Long userCount) {
}

@Test
void testCountSubTable() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<CustomerBaseDto> query = cb.createQuery(CustomerBaseDto.class);
    Root<Customer> root = query.from(Customer.class);
    Join<Customer, User> join = root.join("users", JoinType.LEFT);
    query.multiselect(root.get("id"), root.get("code"), root.get("name"), cb.count(join.get("id")))
            .groupBy(root.get("id"), root.get("code"), root.get("name"));
    List<CustomerBaseDto> resultList = entityManager.createQuery(query).setMaxResults(10).getResultList();
}

Hibernate sql output:

select c1_0.id,c1_0.code,c1_0.name,count(u1_0.user_id) from customer c1_0 left join customer_user u1_0 on c1_0.id=u1_0.customer_id group by 1,2,3 fetch first ? rows only