search

spring-projects / spring-data-jpa

Simplifies the development of creating a JPA-based data access layer.
https://spring.io/projects/spring-data-jpa/
Apache License 2.0
3.01k stars 1.42k forks source link

Pageable - incorrect query for complex join for nativeQuery=true #2395

Open tomaszba opened 2 years ago

tomaszba commented 2 years ago

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_neutralcolumn I mean column which has no impact on sorting.

DiegoKrupitza commented 2 years ago

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.