spring-projects / spring-projects/spring-data-jpa
Specifications with sort creates additional join even though the entity was already fetched
@gregturn is already working on this.
Since Jul 12, 2021.
- Dominant language
- Java
- Stars
- 3.3k
- Forks
- 1.6k
- PR merge metrics
- No merged PRs in 30d
Description
Hi, specifications with sort creates additional join even though the entity was already fetched. The result is that the table is then left joined twice. Consider setup:
entities
@Entity
public class A {
@Id
Long id
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "b_id")
B b;
}
@Entity
public class B {
@Id
Long id
String name
}
specifications
Specification<A> specification =
(root, query, builder) -> {
query.distinct(true);
root.fetch(A_.b, JoinType.LEFT);
return builder.equal(root.get(A_.id), 1L);
};
Sort sort = Sort.by(Sort.Direction.ASC, "b.name");
aRepository.findAll(specification, sort);
then
List<T> findAll(Specification<T> spec, Sort sort);
creates SQL query
select distinct A1.id, A1.b_id, B2.id, B2.name
from A A1
left join B B1 on A1.b_id = B1.id
left join B B2 on A1.b_id = B2.id
where A.id = 1
order by B1.name asc
B entity is joined twice. If the query is distinct, then it fails with SQL error
Expression #1 of ORDER BY clause is not in SELECT list, references column ... B1.name ... which is not in SELECT list; this is incompatible with DISTINCT
The SQL error in this case is correct, order by clause is not in select list. The sort should not create additional join if the entity was already fetched or joined.
The commit that introduced this behaviour is https://github.com/spring-projects/spring-data-jpa/commit/43d1438b38747addc548bd69f82df0f01818c983
Previously, it checked whether there already is a fetched entity (isAlreadyFetched() method) if yes then it didn’t create additional join (getOrCreateJoin() method). The logic was changed to check whether the entity was already inner-joined. But even if I change the join type in specification to JoinType.INNER (root.fetch(A_.b, JoinType.INNER);) it still creates additional join.
Possible duplicate of #2206
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.