@Query with Sort/Pageable not adding aliases properly in ORDER BY clause

[rodrigo-jaris]

If one is using aliases in the where clause and the property name added to the Sort/Pageable starts with any of the where clause aliases, spring-data-jpa won't add the alias to the ORDER BY clause. In the application linked below, we are sorting by testDuplicateColumnName and in the where clause we have a JOIN with an alias t. Thus, since testDuplicateColumnName starts with t, spring-data-jpa won't add the alias t3. as it should add to the generated ORDER BY clause. The consequence of not adding the alias is that the generated query will FAIL to be executed by Hibernate. It started happening in our application after migration to SpringBoot 3.1.1 and consequently Spring Data JPA 3.1.1.

That happens because IMO there is a bug in the following class: org.springframework.data.jpa.repository.query.JpaQueryTransformerSupport. The method that has the bug is the following:

private boolean shouldPrefixWithAlias(Sort.Order order, String primaryFromAlias) {

     // If there is no primary alias
	if (ObjectUtils.isEmpty(primaryFromAlias)) {
		return false;
     }

     // If the Sort contains a function
	if (order.getProperty().contains("(")) {
		return false;
     }

     // If the Sort references an alias directly
	if (projectionAliases.contains(order.getProperty())) {
		return false;
     }

     // If the Sort property starts with an alias
     // THIS IS THE BUG. IMHO A "." MISSING AND IT SHOULD BE: alias -> order.getProperty().startsWith("%s.".formatted(alias))
	if (projectionAliases.stream().anyMatch(alias -> order.getProperty().startsWith(alias))) {
		return false;
     }
	return true;*
}

Sample application reproducing the issue: https://github.com/rodrigo-jaris/spring-data-jpa-query-bug. This application can be downloaded and there is a test that can be run to show the error happening.

Also, to make the problem worse, we can't create the Sort by manually adding the Alias, as spring-data-jpa will duplicate the alias in that case.

The same issue happens when we have a query with aliases that work without manually adding the alias to the Sort

[gregturn]

I've confirmed the bug, thanks to your reproducer.

[gregturn]

Merged to main and backported to 3.1.x

[rodrigo-jaris]

@gregturn, thanks for the fix. Do you have any clue when 3.1.2 is planned to be out? Thank you!