Wrong table used in sort with a complex query

[Noodlex]

I have an issue with the sort in version 2.6.4 (it works in 2.6.3 with the same request) on this request :
@Query(value = "select distinct prepR from PrepRun prepR " +
"where (:#{#prepRunSearchRequest.periodFormatted} is null or prepR.periodFormatted like %:#{#prepRunSearchRequest.periodFormatted}%) " +
"and (:#{#prepRunSearchRequest.name} is null or prepR.name like %:#{#prepRunSearchRequest.name}%) " +
"and (:#{#prepRunSearchRequest.status} is null or prepR.status = :#{#prepRunSearchRequest.status}) " +
"and (:#{#prepRunSearchRequest.plateSizeCode} is null or exists (select 1 from prepR.refPlateSize ps where ps.code like %:#{#prepRunSearchRequest.plateSizeCode}%)) " +
"and (:#{#prepRunSearchRequest.comment} is null or prepR.comment like %:#{#prepRunSearchRequest.comment}%) " +
"and (:#{#prepRunSearchRequest.department} is null or prepR.department like %:#{#prepRunSearchRequest.department}%) " +
"and (:#{#prepRunSearchRequest.period} is null or prepR.period = :#{#prepRunSearchRequest.period}) " +
"and (:#{#prepRunSearchRequest.group} is null or prepR.group like %:#{#prepRunSearchRequest.group}%) " +
"and (:#{#prepRunSearchRequest.sequence} is null or prepR.group like %:#{#prepRunSearchRequest.sequence}%) " +
"and (:#{#prepRunSearchRequest.type} is null or prepR.type = :#{#prepRunSearchRequest.type}) " +
"and (:#{#prepRunSearchRequest.displayArchived} is null or prepR.archived = :#{#prepRunSearchRequest.displayArchived}) " +
"and (:#{#prepRunSearchRequest.dateFrom} is null or prepR.userTimestamp.creatDate >= :#{#prepRunSearchRequest.dateFrom}) " +
"and (:#{#prepRunSearchRequest.dateTo} is null or prepR.userTimestamp.creatDate <= :#{#prepRunSearchRequest.dateTo}) " +
"and (:#{#prepRunSearchRequest.assayWaiting} is null or exists (select 1 from prepR.wells prepW inner join prepW.assayWaiting a where a.code like %:#{#prepRunSearchRequest.assayWaiting}%)) " +
"and (:#{#prepRunSearchRequest.assayRunning} is null or exists (select 1 from prepR.wells prepW inner join prepW.assayRunning a where a.code like %:#{#prepRunSearchRequest.assayRunning}%)) " +
"and (:#{#prepRunSearchRequest.assayDone} is null or exists (select 1 from prepR.wells prepW inner join prepW.assayDone a where a.code like %:#{#prepRunSearchRequest.assayDone}%)) " +
"and (:#{#prepRunSearchRequest.respUserCode} is null or exists (select 1 from Userpid u where u.id = prepR.refRespUserpid and u.nickName like %:#{#prepRunSearchRequest.respUserCode}%)) " +
"and (:#{#prepRunSearchRequest.pcrRunName} is null or exists (select 1 from prepR.wells prepW inner join prepW.pcrWells pcrW inner join pcrW.refPcrRun pcrR where pcrR.name like %:#{#prepRunSearchRequest.pcrRunName}%))")
Page<PrepRun> findBy(PrepRunSearchRequest prepRunSearchRequest, Pageable pageable);

My request with Pageable defined like this :
Page request [number: 0, size 15, sort: userTimestamp.creatDate: DESC]

Error :
org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'prepW.userTimestamp.creatDate' [select distinct prepR from com.mobiolink.backend.core.repository.entity.caccount.prep.PrepRun prepR where (:__$synthetic$__1 is null or prepR.periodFormatted like :__$synthetic$__2) and (:__$synthetic$__3 is null or prepR.name like :__$synthetic$__4) and (:__$synthetic$__5 is null or prepR.status = :__$synthetic$__6) and (:__$synthetic$__7 is null or exists (select 1 from prepR.refPlateSize ps where ps.code like :__$synthetic$__8)) and (:__$synthetic$__9 is null or prepR.comment like :__$synthetic$__10) and (:__$synthetic$__11 is null or prepR.department like :__$synthetic$__12) and (:__$synthetic$__13 is null or prepR.period = :__$synthetic$__14) and (:__$synthetic$__15 is null or prepR.group like :__$synthetic$__16) and (:__$synthetic$__17 is null or prepR.group like :__$synthetic$__18) and (:__$synthetic$__19 is null or prepR.type = :__$synthetic$__20) and (:__$synthetic$__21 is null or prepR.archived = :__$synthetic$__22) and (:__$synthetic$__23 is null or prepR.userTimestamp.creatDate >= :__$synthetic$__24) and (:__$synthetic$__25 is null or prepR.userTimestamp.creatDate <= :__$synthetic$__26) and (:__$synthetic$__27 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayWaiting a where a.code like :__$synthetic$__28)) and (:__$synthetic$__29 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayRunning a where a.code like :__$synthetic$__30)) and (:__$synthetic$__31 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayDone a where a.code like :__$synthetic$__32)) and (:__$synthetic$__33 is null or exists (select 1 from com.mobiolink.backend.core.repository.entity.global.Userpid u where u.id = prepR.refRespUserpid and u.nickName like :__$synthetic$__34)) and (:__$synthetic$__35 is null or exists (select 1 from prepR.wells prepW inner join prepW.pcrWells pcrW inner join pcrW.refPcrRun pcrR where pcrR.name like :__$synthetic$__36)) order by prepW.userTimestamp.creatDate desc]

The "prepW" is only defined in nested query.
The expected order by : order by prepR.userTimestamp.creatDate desc

In 2.6.3 (with a specific wrong field to get the same error) :
nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: jgirojgr of: com.mobiolink.backend.core.repository.entity.caccount.prep.PrepRun [select distinct prepR from com.mobiolink.backend.core.repository.entity.caccount.prep.PrepRun prepR where (:__$synthetic$__1 is null or prepR.periodFormatted like :__$synthetic$__2) and (:__$synthetic$__3 is null or prepR.name like :__$synthetic$__4) and (:__$synthetic$__5 is null or prepR.status = :__$synthetic$__6) and (:__$synthetic$__7 is null or exists (select 1 from prepR.refPlateSize ps where ps.code like :__$synthetic$__8)) and (:__$synthetic$__9 is null or prepR.comment like :__$synthetic$__10) and (:__$synthetic$__11 is null or prepR.department like :__$synthetic$__12) and (:__$synthetic$__13 is null or prepR.period = :__$synthetic$__14) and (:__$synthetic$__15 is null or prepR.group like :__$synthetic$__16) and (:__$synthetic$__17 is null or prepR.group like :__$synthetic$__18) and (:__$synthetic$__19 is null or prepR.type = :__$synthetic$__20) and (:__$synthetic$__21 is null or prepR.archived = :__$synthetic$__22) and (:__$synthetic$__23 is null or prepR.userTimestamp.creatDate >= :__$synthetic$__24) and (:__$synthetic$__25 is null or prepR.userTimestamp.creatDate <= :__$synthetic$__26) and (:__$synthetic$__27 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayWaiting a where a.code like :__$synthetic$__28)) and (:__$synthetic$__29 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayRunning a where a.code like :__$synthetic$__30)) and (:__$synthetic$__31 is null or exists (select 1 from prepR.wells prepW inner join prepW.assayDone a where a.code like :__$synthetic$__32)) and (:__$synthetic$__33 is null or exists (select 1 from com.mobiolink.backend.core.repository.entity.global.Userpid u where u.id = prepR.refRespUserpid and u.nickName like :__$synthetic$__34)) and (:__$synthetic$__35 is null or exists (select 1 from prepR.wells prepW inner join prepW.pcrWells pcrW inner join pcrW.refPcrRun pcrR where pcrR.name like :__$synthetic$__36)) order by prepR.jgirojgr desc]

In the order, this is "prepR" and not "prepW" like in 2.6.4

[edudar]

This is a regression in #2260.

A simpler example would be,

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e WHERE CAST(:modifiedTo AS date) IS NULL"
); -> "e"

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e WHERE CAST(:modifiedFrom AS date) IS NULL"
); -> "date"

Does not work with nested queries either:

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e WHERE (SELECT c.id FROM DbCast c WHERE c.name = '') = e.cast"
); -> "c"

[onacit]

I'm seeing same problem while upgrading Spring Boot 2.6.7.

    @Query(
            value = "SELECT p FROM P AS p"
                    + "     INNER JOIN p.R AS r"
                    + "     INNER JOIN r.M AS m"
                    + " WHERE m.mId.some"
                    + "       = (SELECT MAX(m2.mId.some)"
                    + "          FROM M AS m2"
                    + "          WHERE m2.mId.other = m.mId.other"
                    + "                AND m2.another = 'Y'"
                    + "         )"
    )
    Page<P> findAll...(Pageable pageable);

The sort in the pageable does not work for the p but refers m2....

[slPerryRhodan]

This is related to 2260

[gregturn]

Duplicates #2260.