spring-projects / spring-projects/spring-data-jpa

Specifications with sort creates additional join even though the entity was already fetched

Open
#2,253 10 comments 3 reactions 2 assignees View on GitHub

@gregturn is already working on this.

Since Jul 12, 2021.

status: blocked
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.