Pageable - incorrect query for complex join for nativeQuery=true

[tomaszba]

Hello,

spring-data-jpa, version: 2.3.9
database: mysql

An error occures for method annotated with @Query(nativeQuery=true) with declared pageable parameter, e.g.:

Page<NEntity> searchForEntityWithStatus(@Param("status"), Pageable pageable);

If query contains complex JOIN with ORDER BY and LIMIT, e.g.:

"LEFT JOIN( SELECT status FROM entity_statuses ORDER BY updated_at DESC LIMIT 1) st ON ..."

library generates wrong sorting and pagination fragment (ORDER BY is missing):

, some_column LIMIT ?,? which causes SQL error.

Workaroundthat working for me is to add at the end of query sentence like below:

ORDER BY <any_neutral_column>

any_neutral_column I mean column which has no impact on sorting.

[DiegoKrupitza]

Could you provide a full SQL query (which has the bug)? Because then I can check if this bug is still present after introducing JSqlParser in #2417.