Spring JpaRepository query method with EntityGraph duplicates left join expression in Hibernate 6.5.3

Hibernate ORM
1

I’m experimenting some issue while upgrading from SpringBoot 3.3.3 (Hibernate 6.5.2.Final) to SpringBoot 3.3.4+ (Hibernate 6.5.3 Final) with a duplicated left join expressions by Hibernate.

Currently I’m using SpringBoot 3.3.3 + Hibernate 6.5.2.Final, and I have a spring data JpaRepository method that use @EntityGraph and at same time filter by some attribute of the attribute defined as @EntityGraph.

Below is the code:

    @Entity
    @Table(name = "section")
    public class Section {

        @Id
        private String id;

        private String tenantId;

        private String name;

        @OneToMany(mappedBy = "section", cascade = CascadeType.ALL, orphanRemoval = true)
        @OnDelete(action = OnDeleteAction.CASCADE)
        private List<SectionFilter> filters = new ArrayList<>();
    }

    @Entity
    @Table(name = "section_filter")
    public class SectionFilter {

        @Id
        private String id;

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "sectionId")
        private Section section;

        private String name;

        private boolean isSectionDefault;
    }

    public interface SectionRepository extends JpaRepository<Section, String> {

        @EntityGraph(attributePaths = {"filters"})
        Stream<SectionJPA> findAllByTenantIdAndFiltersIsSectionDefault(String tenantId, boolean isSectionDefault);
    }

When I call this SectionRepository method with SpringBoot 3.3.3 + Hibernate 6.5.2.Final, the query generated by hibernate is:

select sj1_0.id,f1_0.sectionId,f1_0.id,f1_0.isSectionDefault,f1_0.name,sj1_0.name,sj1_0.tenantId
  from customer_schema.qualityreview_sections sj1_0 
  left customer_schema.qualityreview_filters f1_0 on sj1_0.id=f1_0.sectionId 
  where sj1_0.tenantId=? and f1_0.isSectionDefault=? order by sj1_0.name

As you can see, it manages correctly the left join, so it detects that a join is needed by the query itself (findAllByTenantIdAndFiltersIsSectionDefault) but also by the @EntityGraph and is smart to use a single Left join.

However, when I migrate to SpringBoot 3.3.4 or higher + Hibernate 6.5.3.Final, the generated query by hibernate contains two Left Join, so we received more records than expected:

select sj1_0.id,f2_0.sectionId,f2_0.id,f2_0.isSectionDefault,f2_0.name,sj1_0.name,sj1_0.tenantId
  from customer_schema.qualityreview_sections sj1_0 
  left join customer_schema.qualityreview_filters f1_0 on sj1_0.id=f1_0.sectionId 
  left join customer_schema.qualityreview_filters f2_0 on sj1_0.id=f2_0.sectionId 
  where sj1_0.tenantId=? and f1_0.isSectionDefault=? order by sj1_0.name

As the select is only filtering f1_0.isSectionDefault the other left join with table f2_0 is returning all the records without apply the filter.

Some clue or idea about what is happening?

Thanks

2

@jpenalver this is intended: as you can see your left join f1_0 is included in the where clause of your query, this means that elements contained in that joined collection will be filtered. This would violate the entity graph requirement of fetching all associated filters, hence why Hibernate needs to create a separate join to ensure you get the full results.

This was actually a bug that was recently resolved, see: Jira

3

Hello, thanks for your answer @mbladel !

Ok, so I understand that is a design decision about the concept of @EntityGraph.

Let me ask you a question, assuming my context described above, what is in your opinion the best way to achieve this in terms of simplicity and performance?

Thank you so much

4

If you want to fetch the whole filters collection while filtering on an associated filter you can so something like the following HQL query:

select s from Section s join fetch s.filters where exists 
(select 1 from SectionFilter f where f.section = s and f.tenantId = :tenantId and f.isSectionDefault = :isSectionDefault)
5

I think this is the tricky point…

I don’t want to get the whole filters collection associated to a Section, I want to get the Section with only the Filters marked as default. So basically, get the Section with a subset of their filters according to a condition.

Is the behavior we have with Hibernate 6.5.2 using the combination of spring-data-jpa Specifications and EntityGraph.

Perhaps this is opposite to the idea of a OneToMany relationship.

Any alternative to achieve this behavior in a good way?

Thanks.

6

Yes, that is not what you want. The entity mappings are an exact Object representation of what is found on the database, so if the filters collection wouldn’t contain something it means that there is no association on the database.

If you need only a portion of the filters associated to a specific session, you should retrieve them through a dedicated query:

select f from SectionFilter f where f.section = :section and ...
7

Ok, thanks @mbladel, in my opinion was a nice feature to improve some situations like mine, where I want to reduce the queries over DB and reduce the N+1 queries (the entities are more complex than I presented), but I understood that is reasonably deviating from the entity relations and EntityGraph design principles.

Perhaps something like Hibernate @Filters could be added to be used in Spring jpa repositories.

Thanks