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.