Combination of Specifications, Sorting and Postgres leads to unresolvable SQL error

[schuettec]

Hi, I found a combination of specifications with joins and sorting that leads to an SQL error and I can't find a solution for that.

For example consider the following two entities:

public class Person {
   private String name;
   private Address address;
   // POJO, JPA-stuff etc.
   // other entity references
}
public class Address {
   private String street;
   private String city;
   // POJO, JPA-stuff etc.
}

In my code I build a specification to join Person and Address, sort by city and filter results where city="Dortmund".
Sometimes (based on the application's logic) the specification also joins some other entities so I am able to specify some additional filter attributes. This is why I have to use query.distinct() in the specification to get rid of duplicates that might occur when performing additional joins.

The resulting specification produces something like: SELECT DISTINCT <Person Attributes...> FROM Person LEFT OUTER JOIN Person.addresses addresses WHERE <filter attributes> ORDER BY Address.city

Running that on postgres gives me an SQL error:
"for SELECT DISTINCT, ORDER BY expressions must appear in select list"

From postgres point of view, the order-by clause must appear in select list, but from an API point of view in specifications I am not able to add columns to the select list. And even if I manage to do so, I wonder how Spring Data JPA and JPA would handle the extended select attributes.

When looking at the method signature

List<T> findAll(@Nullable Specification<T> spec, Sort sort);

the sort is handled by Spring Boot and specifies the nested attribute to sort by (in this example "person.address"). The specification is responsible for joining and the projection to <T> is handled by Spring. In this combination I am not able to find a solution because neither the select list nor the joins for sorting are under my control.

What can I do to get the specification working with order-by and distinct?

Background
It might be possible to write a repository method to query and filter, use native queries or even entity manager methods, but I'm currently working in a framework that relies heavily on Specifications, thats why I have to find a solution using Specs.

[schuettec]

To demonstrate the problem I created a demo project:

https://github.com/schuettec/specification-sort-problem

The test of interest is com.github.schuettec.specificationorderbydemo.domain.PersonRepositoryTest

[atishsungum]

The query generated is as expected. But given that you have used Distinct, by doing Order By on attributes other than the Person's attributes will not work unless if you select that attribute.

Follow the link to find more info on Distinct and Order By. It provides a good explanation and concrete examples why it does not work.

[schauder]

I agree with @schuettec the behaviour is as expected and very similar to #2253.
The problem is the DISTINCT which really doesn't have a place in a Specification which is a (leaky) abstraction over a WHERE-clause.
The DISTINCT becomes necessary due to the additional joins so the goal should be to get rid of those.
This can often be achieved by using subselects instead. See this StackOverflow answer for how to write those using the Criteria-API.