How to sort by fetched properties with distinct JPA Specifications

I use Spring Boot 1.5.3.RELEASE and for me it's unclear how to sort by properties of nested objects with distinct and Specifications because of:

Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Spring Data JPA generates wrong query.

Let's see a little example:

Model

@Data @Entity @Table(name = "vehicle") public class Vehicle implements Serializable { @Id @GeneratedValue(strategy = IDENTITY) private Long id; @ManyToOne @JoinColumn(name = "vehicle_type_id") private VehicleType vehicleType; @ManyToOne @JoinColumn(name = "vehicle_brand_id") private VehicleBrand vehicleBrand; } 

We have Vehicle class with nested objects VehicleType and VehicleBrand.

@Data @Entity @Table(name = "vehicle_brand") public class VehicleBrand implements Serializable { @Id @GeneratedValue(strategy = IDENTITY) private Long id; @Column(name = "name") private String name; @ManyToOne @JoinColumn(name = "vehicle_model_id") private VehicleModel model; } 

Class VehicleBrand also contains VehicleModel.

@Data @Entity @Table(name = "vehicle_model") public class VehicleModel implements Serializable { @Id @GeneratedValue(strategy = IDENTITY) private Long id; @Column(name = "name") private String name; } 

Service

Now I want to create a query with JPA Specifications and some sorting by "vehicleBrand.name":

public List<Vehicle> findAll() { Specification<Vehicle> spec = Specifications.where( (root, criteriaQuery, criteriaBuilder) -> { criteriaQuery.distinct(true); return null; } ); return vehicleRepository.findAll(spec, new Sort("vehicleBrand.name")); } 

Spring Data JPA generates following query:

select distinct vehicle0_.id as id1_0_, vehicle0_.gas_type as gas_type2_0_, vehicle0_.vehicle_brand_id as vehicle_4_0_, vehicle0_.vehicle_type_id as vehicle_5_0_, vehicle0_.year_of_issue as year_of_3_0_ from vehicle vehicle0_ left outer join vehicle_brand vehiclebra1_ on vehicle0_.vehicle_brand_id=vehiclebra1_.id order by vehiclebra1_.name asc 

And it fairly doesn't work because of:

Order by expression "VEHICLEBRA1_.NAME" must be in the result list in this case; SQL statement

To fix the issue we have to fetch vehicleBrand in our Specification:

public List<Vehicle> findAll() { Specification<Vehicle> spec = Specifications.where( (root, criteriaQuery, criteriaBuilder) -> { criteriaQuery.distinct(true); root.fetch("vehicleBrand", JoinType.LEFT); //note that JoinType.INNER doesn't work in that case return null; } ); return vehicleRepository.findAll(spec, new Sort("vehicleBrand.name")); } 

Spring Data JPA generates following query:

select distinct vehicle0_.id as id1_0_0_, vehiclebra1_.id as id1_1_1_, vehicle0_.gas_type as gas_type2_0_0_, vehicle0_.vehicle_brand_id as vehicle_4_0_0_, vehicle0_.vehicle_type_id as vehicle_5_0_0_, vehicle0_.year_of_issue as year_of_3_0_0_, vehiclebra1_.vehicle_model_id as vehicle_3_1_1_, vehiclebra1_.name as name2_1_1_ from vehicle vehicle0_ left outer join vehicle_brand vehiclebra1_ on vehicle0_.vehicle_brand_id=vehiclebra1_.id order by vehiclebra1_.name asc 

And now it works because we see vehiclebra1_.name in the selection part.

Question

But what to do If I need to sort by "vehicleBrand.model.name"?
I make an additional fetch, but it doesn't work:

public List<Vehicle> findAll() { Specification<Vehicle> spec = Specifications.where( (root, criteriaQuery, criteriaBuilder) -> { criteriaQuery.distinct(true); root.fetch("vehicleBrand", JoinType.LEFT).fetch("model", JoinType.LEFT); return null; } ); return vehicleRepository.findAll(spec, new Sort("vehicleBrand.model.name")); } 

It generates following query:

select distinct vehicle0_.id as id1_0_0_, vehiclebra1_.id as id1_1_1_, vehiclemod2_.id as id1_2_2_, vehicle0_.gas_type as gas_type2_0_0_, vehicle0_.vehicle_brand_id as vehicle_4_0_0_, vehicle0_.vehicle_type_id as vehicle_5_0_0_, vehicle0_.year_of_issue as year_of_3_0_0_, vehiclebra1_.vehicle_model_id as vehicle_3_1_1_, vehiclebra1_.name as name2_1_1_, vehiclemod2_.name as name2_2_2_ from vehicle vehicle0_ left outer join vehicle_brand vehiclebra1_ on vehicle0_.vehicle_brand_id=vehiclebra1_.id left outer join vehicle_model vehiclemod2_ on vehiclebra1_.vehicle_model_id=vehiclemod2_.id cross join vehicle_model vehiclemod4_ where vehiclebra1_.vehicle_model_id=vehiclemod4_.id order by vehiclemod4_.name asc 

And it doesn't work because of:

Order by expression "VEHICLEMOD4_.NAME" must be in the result list in this case; SQL statement

Take a look on how we select vehiclemod2_.name but make order by vehiclemod4_.name.

I've tried to make sorting in Specification directly but it also doesn't work:

Specification<Vehicle> spec = Specifications.where( (root, criteriaQuery, criteriaBuilder) -> { criteriaQuery.distinct(true); root.fetch("vehicleBrand", JoinType.LEFT).fetch("model", JoinType.LEFT); criteriaQuery.orderBy(criteriaBuilder.asc(root.join("vehicleBrand", JoinType.LEFT).join("model", JoinType.LEFT).get("name"))); return null; } ); 

What should I do to make JPA generate right query so I could make a sorting by nested objects? Does it make sense to upgrade version of Spring Boot from 1.5.3.RELEASE to 2+?
Thanks.