Double Joins when using EntityGraph

Hibernate ORM
1

Hi, when I upgraded our Spring application, the Hibernate version bumped from 6.5.2 to 6.5.3. This version change in Hibernate caused it to generate a different query for the existing repository function.

My main entity:

@Entity
@Table(name = "auth_user")
@NamedEntityGraph(name = "AuthUserEntity.detail",
        attributeNodes = {
                @NamedAttributeNode(value = "customers", subgraph = "customers"),
        },
        subgraphs = {
                @NamedSubgraph(name = "customers",
                        attributeNodes = {
                                @NamedAttributeNode(value = "userAccounts", subgraph = "userAccounts"),
                                @NamedAttributeNode(value = "productPermissions", subgraph = "productPermissions")
                        }),
                @NamedSubgraph(name = "userAccounts",
                        attributeNodes = @NamedAttributeNode(value = "permissions", subgraph = "permissions")),
                @NamedSubgraph(name = "productPermissions",
                        attributeNodes = @NamedAttributeNode(value = "product", subgraph = "product")),
        })
public class AuthUserEntity {
    ...
    @OneToMany(cascade = {CascadeType.REFRESH, CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
    @JoinColumn(name = "auth_user_id")
    private Set<AuthUserCustomerEntity> customers;
}

@Entity
@Table(name = "auth_user_customers")
public class AuthUserCustomerEntity {

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

    @Column(name = "auth_user_id")
    private Long userId;

    @Column(name = "customer_id")
    private String customerNo;

    ...

    @OneToMany(cascade = {CascadeType.REFRESH, CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
    @JoinColumn(name = "customer_id")
    private Set<AuthUserAccountEntity> userAccounts;

    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.DETACH})
    @JoinTable(name = "user_product_permission",
            joinColumns = {@JoinColumn(name = "user_customer_id", referencedColumnName = "id")},
            inverseJoinColumns = {@JoinColumn(name = "product_permission_id", referencedColumnName = "id")})
    Set<ProductPermissionEntity> productPermissions = new HashSet<>();

@Entity
@Table(name = "auth_user_accounts")
public class AuthUserAccountEntity {

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

    @Column(name = "customer_id")
    private Long customerId;

    @Column(name = "account_no")
    private String accountNo;

    @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
    @JoinColumn(name = "account_id")
    private Set<AuthUserAccountPermissionEntity> permissions;
    ...
}

My Spring Data Repository function:

    @EntityGraph(value = "AuthUserEntity.detail", type = EntityGraph.EntityGraphType.FETCH)
    @Query("select u from AuthUserEntity u inner join u.customers c where u.id = ?1 and c.customerNo = ?2")
    Optional<AuthUserEntity> findByIdAndCustomerId(Long userId, String customerId);

When I run this function with the Hibernate version 6.5.2 it creates the query written below:

SELECT
       ...
FROM auth_user aue1_0
         JOIN auth_user_customers c1_0
              ON aue1_0.id = c1_0.auth_user_id
         LEFT JOIN user_product_permission pp1_0 ON c1_0.id = pp1_0.user_customer_id
         LEFT JOIN product_permission pp1_1 ON pp1_1.id = pp1_0.product_permission_id
         LEFT JOIN product p1_0 ON p1_0.id = pp1_1.product_id
         LEFT JOIN auth_user_accounts ua1_0 ON c1_0.id = ua1_0.customer_id
         LEFT JOIN auth_user_account_permissions p2_0 ON ua1_0.id = p2_0.account_id
WHERE aue1_0.id = ?
  AND c1_0.customer_id = ?

When I run this function with the Hibernate version 6.5.3 it creates the query written below:

SELECT
       ...
FROM auth_user aue1_0
         JOIN auth_user_customers c1_0
              ON aue1_0.id = c1_0.auth_user_id
         LEFT JOIN auth_user_customers c2_0 ON aue1_0.id = c2_0.auth_user_id
         LEFT JOIN user_product_permission pp1_0 ON c2_0.id = pp1_0.user_customer_id
         LEFT JOIN product_permission pp1_1 ON pp1_1.id = pp1_0.product_permission_id
         LEFT JOIN product p1_0 ON p1_0.id = pp1_1.product_id
         LEFT JOIN auth_user_accounts ua1_0 ON c2_0.id = ua1_0.customer_id
         LEFT JOIN auth_user_account_permissions p2_0 ON ua1_0.id = p2_0.account_id
WHERE aue1_0.id = ?
  AND c1_0.customer_id = ?

You can see that auth_user_customers table joined twice. One with inner join other with left. I don’t know what is wrong. Is it our problem or is its bug introduced in the version 6.5.3.

2

Try using Hibernate ORM 6.6.1.Final and if the problem persists, please try to create a reproducer with our test case template to show the problem in an isolated Hibernate ORM only fashion.

3

Same problem here, we have tried to update but even in 7.0.0.Beta4 it’s still the same bug. @pyaqubzade did you create a bug for that?

4

No, I didn’t. I just downgraded the hibernate version to 6.5.2.Final from 6.5.3.Final for Spring Boot 3.3.4

5

We actually tried to create a test case for it and we found that our usage was wrong.

This is a bit oversimplified, but I hope it gets the point across:
If you have a relation between entities A and B and you select A fetch join B where B.attr=“…” then in 6.5.2 it would only fetch the B entities where attr=“…”, however since you select for A as the base entity, this would result in missing relations (not all B entities contained in the collection).
In 6.5.3 this was corrected and thus the relation A.B will always contain the correct entities from B.

We had previously relied on the old logic and changed our code now.