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

[michalkrajcovic]

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 43d1438

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

[schauder]

This behaviour is actually correct.
The real problem is that a Specification is really a (leaky) abstraction over a WHERE-clause.
By using fetch and distinct you are not only affecting the WHERE-clause though, but also the projection, i.e. the SELECT clause.
This leads to confusion and ultimately errors.

The problem with a JOIN FETCH is, it isn't really a join. From.getJoins() doesn't return it, which makes sense, since it must not be referenced anywhere in the rest of the statement.
From the Java Persistence API, Version 2.2 Section 4.4.5.3 Fetch Joins

A FETCH JOIN enables the fetching of an association or element collection as a side effect of the exe- cution of a query.
The syntax for a fetch join is
fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression (emphasis is mine)
The association referenced by the right side of the FETCH JOIN clause must be an association or element collection that is referenced from an entity or embeddable that is returned as a result of the query. It is not permitted to specify an identification variable for the objects referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities or elements cannot appear elsewhere in the query.

This talks about JPQL but I think it is save to assume the equivalent holds true for the Criteria-API used by a Specification.

This means the fetch-join must not and can not be used in a WHERE-clause or ORDER-BY-clause.
Therefore Spring Data JPA has to create an additional JOIN for that purpose.

This of course clashes on the SQL level with the DISTINCT since it basically ignores the additional JOIN and therefore it can't be used in the ORDER-BY-clause.

There is a good Stack Overflow answer regarding this topic.

[michalkrajcovic]

@schauder considering the entities described in this issue, if I create a specification

Specification<A> specification =
(root, query, builder) -> {
   query.distinct(true); 
   root.fetch(A_.b, JoinType.LEFT);
   return builder.equal(root.get(A_.b).get(B_.name), “smth”)

};

aRepository.findAll(specification);

please note the equal predicate

then

List<T> findAll(Specification<T> spec);

then creates SQL

select distinct A1.id, A1.b_id, B1.id, B1.name
from A A1
   left join B B1 on A1.b_id = B1.id
where B1.name = "smth"

fetch-join is used in the WHERE clause. No additional join is created.

based on your statement

This means the fetch-join must not and can not be used in a WHERE-clause or ORDER-BY-clause.
Therefore Spring Data JDBC has to create an additional JOIN for that purpose.

Are you sayin that observed behaviour is a bug and additional join should have been created?

[schauder]

Are you sayin that observed behaviour is a bug and additional join should have been created?

According to my understanding of the specification this is indeed wrong behaviour.
It results in an A being loaded without the B.
I might compromise on "inconsistent behaviour" since if the join is reused by a predicate it should be returned by From.getJoins().

I reopen the issue.
I still think it is not us to fix it, but we should create a proper JPA based reproducer so we can ask the JPA maintainers and implementors for their opinion.