Pageable With Fetch not returning the right alias Name [DATAJPA-1000]

[spring-projects-issues]

Jean Philippe Emond opened DATAJPA-1000 and commented

When using @Query in a JPA repository along with Pageable and attempting to sort on a field in a joined fetch entity, the from entity gets prepended to the sort field. Because the alias got a bad result. For example, if I do the following query:

@Query(value = "SELECT DISTINCT user FROM User user LEFT JOIN FETCH user.authorities authority")

if I simply decide to sort by a field in the Authority entity with this PageRequest:

new PageRequest(0, 10, Direction.DESC, "authority.name");

The query run is:

SELECT DISTINCT user FROM com.admtl.model.user.User user LEFT JOIN FETCH user.authorities authority  order by user.authority.name asc

And I got :

SEVERE: Servlet.service() for servlet [SpringWeb] in context with path [/path] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: could not resolve property: authority of: com.admtl.model.user.User [SELECT DISTINCT user FROM com.admtl.model.user.User user LEFT JOIN FETCH user.authorities authority  order by user.authority.name asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: authority of: com.admtl.model.user.User [SELECT DISTINCT user FROM com.admtl.model.user.User user LEFT JOIN FETCH user.authorities authority  order by user.authority.name asc]] with root cause

Normal because authorities is property of User not authority

But If I remove the FETCH

@Query(value = "SELECT DISTINCT user FROM User user LEFT JOIN user.authorities authority")

With the same PageRequest, my query run is:

SELECT DISTINCT user FROM com.admtl.model.user.User user LEFT JOIN FETCH user.authorities authority  order by authority.name asc

Perfect!

I look a bit on the code of org.springframework.data.jpa.repository.query.QueryUtils
for method getOuterJoinAliases

    static Set<String> getOuterJoinAliases(String query) {

		Set<String> result = new HashSet<String>();
		Matcher matcher = JOIN_PATTERN.matcher(query);

		while (matcher.find()) {

			String alias = matcher.group(QUERY_JOIN_ALIAS_GROUP_INDEX);
			if (StringUtils.hasText(alias)) {
				result.add(alias);
			}
		}

		return result;
	}

The FETCH put the mess with the QUERY_JOIN_ALIAS_GROUP_INDEX

---

Affects: 1.10.4 (Hopper SR4)

Referenced from: commits 3bba394, f765602, 6fedc49

Backported to: 1.10.6 (Hopper SR6), 1.9.7 (Gosling SR7)

[spring-projects-issues]

Oliver Drotbohm commented

That's be fixed and backported. Feel free to give the snapshots a try. And congrats to filing the thousands ticket for SD JPA 😉

[spring-projects-issues]

Jean Philippe Emond commented

I try the Snapshots and that work great! Thanks for the fast response!