spring-data-jpa: Order by expression missing in select list after update to Spring Boot 3.0.1 with DISTINCT queries

Hi there,

Spring Boot: 3.0.1 Database: postgres 14

I am seeing an issue after updating from Spring Boot 2.7x to Spring Boot 3.0.1. I am raising this issue here as I think the issue lies within Spring Data JPA after some debugging, specifically in QueryUtils in converting a Sort to an Expression.

I provide the following Sort as a default for a query

Sort.by(Sort.Order.desc(Study_.DATE), Sort.Order.desc(Study_.TIME), Sort.Order.asc("patient.lastName"), Sort.Order.asc("patient.firstName"))

I pass this through to findAll(Specification, Sort) and the following query is generated

select 
  distinct s1_0.id, 
  s1_0.date, 
  s1_0.description, 
  s1_0.patient_id, 
  s1_0.size, 
  s1_0.study_instance_uid, 
  s1_0.time
from 
  patient.studies s1_0 
  join patient.patients p1_0 on p1_0.id = s1_0.patient_id 
where 
  s1_0.customer_id = ?
order by 
  s1_0.date desc, 
  s1_0.time desc, 
  p1_0.last_name asc, 
  p1_0.first_name asc offset ? rows fetch first ? rows only

Note that this query is DISTINCT. This query will fail with PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list. If I remove the distinct and run this the query will execute successfully but will give the wrong results.

If I try to work around this by using an entity graph to fetch the patient record eagerly, Spring Data will not detect the join in QueryUtils and join the record again and then use that alias in the order by, once again running into the same issue.

select 
  distinct s1_0.id, 
  s1_0.date,  
  s1_0.description, 
  p2_0.id, 
  p2_0.attributes_id, 
  p2_0.created_at, 
  p2_0.customer_id, 
  p2_0.deceased, 
  p2_0.deceased_at, 
  p2_0.deleted_at, 
  p2_0.dob, 
  p2_0.first_name, 
  p2_0.last_name, 
  p2_0.middle_name, 
  p2_0.prefix, 
  p2_0.sex, 
  p2_0.suffix, 
  p2_0.updated_at, 
  s1_0.size, 
  s1_0.study_instance_uid, 
  s1_0.time
from 
  patient.studies s1_0 
  join patient.patients p1_0 on p1_0.id = s1_0.patient_id 
  left join patient.patients p2_0 on p2_0.id = s1_0.patient_id 
where 
  s1_0.customer_id = ? 
order by 
  s1_0.date desc, 
  s1_0.time desc, 
  p1_0.last_name asc, 
  p1_0.first_name asc offset ? rows fetch first ? rows only

I think this issue is likely caused by a combination of the new aliasing in Hibernate and the related changes made to QueryUtils about 8 months ago.

From searching through Hibernate bug tracker, there are a few reports of this issue going way back to early 5.x releases of Hibernate. eg https://hibernate.atlassian.net/browse/HHH-13434. The feedback from the maintainers is a fetch() should be used rather than a join in these cases.

About this issue

Original URL
State: open
Created a year ago
Reactions: 4
Comments: 15 (4 by maintainers)

Most upvoted comments

Hi Greg,

Unfortunately it’s a complicated dynamic query, if I could have done it with @Query I certainly would have.

Basically, the only change required to make it work in Query Utils is this

private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute, JoinType joinType, boolean distinct) {

        for (Fetch<?, ?> fetch : from.getFetches()) {
            if (fetch.getAttribute().getName().equals(attribute))
                return (Join<?, ?>) fetch;
        }

        if (!distinct) {
            for (Join<?, ?> join : from.getJoins()) {

                if (join.getAttribute().getName().equals(attribute)) {
                    return join;
                }
            }
        }

        return distinct ?
                (Join<?, ?>) from.fetch(attribute, joinType) : from.join(attribute, joinType);
    }

The other changes are all just allowing that isDistinct param to be passed around up to where we have access to the query variable and can get query.isDistinct()

With this change made, all the tests pass however there are a few method signatures that need to be updated to pass isDistinct through to this method.

All of our more complicated or dynamic queries are done via a Specification and we are currently having this issue in 3.1 but with this small patch its working well.

william00179 on Jun 1, 2023