getOrCreateJoin does not reuse fetch joins

[elmuerte]

spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/QueryUtils.java

Lines 762 to 771 in ee9ef86

	private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute, JoinType joinType) {
	for (Join<?, ?> join : from.getJoins()) {
	if (join.getAttribute().getName().equals(attribute)) {
	return join;
	}
	}
	return from.join(attribute, joinType);
	}

This can result in in duplicate joins. Except for being inefficient, this also has problematic side effects.

In MySQL columns on which you want to order must be included in distinct queries.

In order to do a select distinct e from Entity e order by e.other.name you must join fetch e.other. In Specification terms this fetch join can be done with the predicate

return (root, query, builder) -> {
	if (root.getJavaType().equals(query.getResultType())) {
		// Only add the join fetch when the root is part of the entity
		root.fetch(attribute, JoinType.INNER);
	}
	return null;
};

When the Orders are processed it creates another join (a non-fetch inner) which is used to perform the sorting on.
This results in a SQL query which MySQL does not accept.

JPQL created by Spring Data:

select distinct generatedAlias0
from Entity as generatedAlias0
inner join generatedAlias0.other as generatedAlias2 
inner join fetch generatedAlias0.other as generatedAlias3
order by generatedAlias2.name

Hibernate produces a SQL query similar to:

select distinct generatedAlias0.*, generatedAlias3.*
from Entity generatedAlias0
inner join Other generatedAlias2 on generatedAlias0.id=generatedAlias2.id 
inner join Other generatedAlias3 on generatedAlias0.id=generatedAlias3.id
order by generatedAlias2.name

It used to work with 2.4. I see that in #436 the logic in toExpressionRecursively was changed where it would check if it was already fetched, and thus not create a join.

spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/QueryUtils.java

Lines 641 to 649 in eb023fc

	if (requiresOuterJoin(propertyPathModel, model instanceof PluralAttribute, !property.hasNext(), isForSelection)
	&& !isAlreadyFetched(from, segment)) {
	Join<?, ?> join = getOrCreateJoin(from, segment);
	return (Expression<T>) (property.hasNext() ? toExpressionRecursively(join, property.next(), isForSelection)
	: join);
	} else {
	Path<Object> path = from.get(segment);
	return (Expression<T>) (property.hasNext() ? toExpressionRecursively(path, property.next()) : path);
	}

Note, isAlreadyInnerJoined does check the fetch joins.

[schauder]

That sounds like a duplicate of #2253

[elmuerte]

Yes, it is the same problem. (Closing this duplicate issue.)