Sorting by function result not possible if function contains parameter and Sort.by is used [DATAJPA-1750]

[spring-projects-issues]

simonparadies opened DATAJPA-1750 and commented

The FUNCTION_PATTERN regex in QueryUtils does not match functions containing positional or named parameters. Therefore, in these cases, the order by clause is constructed using the entities' qualifier, which leads to an incorrect expression

---

Affects: 2.4 M1 (2020.0.0)

Referenced from: pull request #425

[spring-projects-issues]

Jens Schauder commented

Could you provide a specific example where it fails?

[spring-projects-issues]

simonparadies commented

See the tests in the linked PR

[spring-projects-issues]

Jens Schauder commented

Strange I didn't see that. Thanks

[luitas]

On 2.5.12 spring boot it works bu in 2.5.13 not working
In my case if i have

pageable = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), Sort.by(Sort.Direction.DESC, "inserted"));
return dataRecordRepository.findAllByFilter(pageable,1);

and in repository

@Query("select r From DataRecord r " +
            "  " +
            "where " +
                    " ( " +
            "       r.adusrId = :userId " +
            "       or EXISTS( select 1 FROM DataRecordDvsRight dr WHERE dr.adusrId = :userId AND dr.dataRecord = r ) " +
            ")")
    Page<DataRecord> findAllByFilter(Pageable pageable, @Param("userId") Long userId);

I get an error:
Invalid path: 'dr.inserted' [select r From com.algoritmusistemos.media.app.entity.DataRecord r where ( r.adusrId = :userId or EXISTS( select 1 FROM com.algoritmusistemos.media.app.entity.DataRecordDvsRight dr WHERE dr.adusrId = :userId AND dr.dataRecord = r ) ) order by dr.inserted desc]

[schauder]

Seems to be related to, possibly a duplicate of #2496

[gregturn]

Backported to 2.7.x and 2.6.x.