Dynamic sorting presence detection inside Pageable before NativeQuery execution [DATAJPA-1590]

Roman Golovan opened DATAJPA-1590 and commented

Example 59 under https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries.

A Pageable object can be constructed having a Sort argument:

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

...

Pageable pageable = Pagerequest.of(page, size, sort);
userRepository.findByLastname("name", pageable);

 In this case the initial native query will be enriched with what Sort defines, which contradicts with what the documentation says:

Spring Data JPA does not currently support dynamic sorting for native queries, because it would have to manipulate the actual query declared, which it cannot do reliably for native SQL.

Moreover there leads to another issue, which is caused by the absence of logical to physical conversion happening when NativeQuery is enriched with Sort, so the following example fails:

@Entity
public class User {
 ...
 @Column(name = "user_name")
 private String userName;
 ...
}

public interface UserRepository extends JpaRepository<User, Long> { 

 @Query(value = "SELECT * FROM USERS", countQuery = "SELECT count(*) FROM   USERS", nativeQuery = true)
 Page<User> findPageableBy(Pageable pageable);
}
...
Pageable pageable = Pagerequest.of(page, size, Sort.by(Sort.Order.desc("userName")));
userRepository.findPageableBy(pageable);

The resulting query is somewhat like:

Resulting query is somewhat like:
 select id,
 user_name,
 ... from users order by userName desc limit ?
-----
userName cannot be mapped to user_name

Affects: 2.1.10 (Lovelace SR10)