Query broken after spring boot upgrade from 2.6.x to 2.7.x-CodePudding

Controller

      @RequestParam(value = "term", required = false) String term,
      @SortDefault(sort = X_.NAME, direction = Direction.ASC) Pageable pageable) {
    Page<SomeResult> results = findSome(..., pageable)

Repository

     @Query("""
      SELECT x FROM X x
      WHERE x.fieldId IN (:fieldIds)
          AND ((:statusList) IS NULL OR t.status IN (:statusList))
          AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
          AND (:excludeId is NULL OR NOT EXISTS (
              FROM Y y
              WHERE y.id.someId = x.id
                AND y.id.someOtherId = :excludeId
              )
          )
       """)
  Page<X> findSome(
      Set<Long> fieldIds,
      List<Status> statusList,
      String term,
      Long excludeId,
      Pageable pageable);

Issue

ERROR org.hibernate.hql.internal.ast.ErrorTracker -  Invalid path: 'y.name'
org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'pt.name' [SELECT x FROM com.package.X x
WHERE t.fieldId IN (:fieldIds)
    AND ((:statusList) IS NULL OR t.status IN (:statusList))
    AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
    AND (:excludeId is NULL OR NOT EXISTS (
        SELECT y FROM com.package.Y y
        WHERE y.id.someId = x.id
          AND y.id.someOtherId = :excludeId
        )
    )
 order by y.name asc];

The pageable acts on the inner FROM Y y.... If i remove that part, it works as expected.

This doesn't happen on spring boot 2.6.3 from which i'm upgrading. I'm using postgresql v14

CodePudding user response：

It seems there were/are some issues with queries when they're part of a multi-lined java string block.

Check this issue in spring data jpa project

The workaround that i've found to make it work was to change the layout of the query from

     @Query("""
      SELECT x FROM X x
      WHERE x.fieldId IN (:fieldIds)
          AND ((:statusList) IS NULL OR t.status IN (:statusList))
          AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
          AND (:excludeId is NULL OR NOT EXISTS (
              FROM Y y
              WHERE y.id.someId = x.id
                AND y.id.someOtherId = :excludeId
              )
          )
       """)

     @Query("""
      SELECT x FROM X x
      WHERE x.fieldId IN (:fieldIds)
          AND ((:statusList) IS NULL OR t.status IN (:statusList))
          AND (:term IS NULL OR LOWER(x.name) LIKE :term OR LOWER(x.code) LIKE :term)
          AND (:excludeId is NULL OR NOT EXISTS 
          ( FROM Y y WHERE y.id.someId = x.id AND 
           y.id.someOtherId = :excludeId)
          )
       """)