Rrepository query methods with 'join fetch' produce duplicated parent entities [DATAJPA-1299]

[spring-projects-issues]

Sergei Poznanski opened DATAJPA-1299 and commented

Repository query methods with 'join fetch' leads to the duplicate parents.
Number of duplicates corresponds to the number of nested children.

To work around this situation we can use 'distinct' in the query. But this still does not work with projections.

The similar methods with @EntityGraph work as expected.

Example:

@Entity
class Parent extends BaseEntity {
	private String name;
	@OneToMany(mappedBy = "parent")
	private List<Child> children;
}

@Entity
class Child extends BaseEntity {
	private String name;
	@ManyToOne(fetch = LAZY)
	private Parent parent;
}

interface ParentProjection {
	Integer getId();
	String getName();
	List<Child> getChildren();
}

interface ParentRepo extends JpaRepository<Parent, Integer> {

	@EntityGraph(attributePaths = "children")
	@Override
	List<Parent> findAll(); // works as expected 

	@Query("select p from Parent p left join fetch p.children")
	List<Parent> findWithQuery(); // has duplicated parents
	
	@Query("select distinct p from Parent p left join fetch p.children")
   	List<Parent> findDistinctWithQuery(); // does not have duplicated parents
	
	@EntityGraph(attributePaths = "children")
	List<ParentProjection> findProjectionsBy(); // works as expected
		
	@Query("select distinct p from Parent p left join fetch p.children")
   	List<ParentProjection> findDistinctProjectionsWithQuery(); // has duplicated parents 
}

More tests is here

---

Affects: 2.0.5 (Kay SR5)

Reference URL: https://github.com/Cepr0/duplicate-parent-entities

1 votes, 4 watchers

[spring-projects-issues]

Jens Schauder commented

From the JPA Specification 4.4.5.3 Fetch Joins:

SELECT d
FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1

A fetch join has the same join semantics as the corresponding inner or outer join, [...]. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.

Therefore it is to be expected that the variants with a join and no distinct return duplicate results.

I still have to look in the projection variant

[spring-projects-issues]

Jens Schauder commented

For the projection variant two things are "interesting"

1. Since we can't convert from the entity type to the projection in a classical sense we are requestion a Tuple, although each row will consist of only one value: the parent.

2. Apparently, the switch to Tuple as a result type makes Hibernate return duplicates

[spring-projects-issues]

Jens Schauder commented

I created an issue for #2: https://hibernate.atlassian.net/browse/HHH-12489 lets see what they say about it

[spring-projects-issues]

Jens Schauder commented

The Hibernate team doesn't consider this a bug on their side

[spring-projects-issues]

Hayrol Reyes commented

To solve a similar situation I used "query.distinct(true);" as pointed in the description.

I decided to use it because my use case is a generic filter API and this will be applied to all entities' filters related to the API... also, after several tests about performance I found there wasn't any significant impact in the results.

In fact, this approach also worked on Projections and Pageable results within Sorts

[gregturn]

I'm afraid that according to the results from https://hibernate.atlassian.net/browse/HHH-12489, this is currently considered "Works as Designed" per the existing JPA specs.

If you wish to attempt to alter the specs, please visit jakartaee/persistence#177 and upvote this proposed change. If this moves forward, feel free to come back and pursue changing Spring Data JPA in the future.