Native query with pagination throws internal server error when page size less than record size spring boot

Question

I'm using a native query in spring boot with pagination, the issue is when i run the query with page size less than the no of records i'm getting an internal server error, but it worked fine or page size greater than the no of records. Below is my native query

  @Query(value = "SELECT * FROM (SELECT * from transactions t where t.book_id=?1 OR t.reflect_book_id=?2  AND t.is_active = true) a inner join " +
            "(select  id, display_name from customers) b on a.customer_id = b.id order by display_name",
            nativeQuery = true)
    Page<Transaction> findAllTransactionsByName(String bookId, String reflect_book_id, Pageable pageable);

Below is the error I'am getting

    ... 110 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT *) from transactions t where t.book_id='bid-5778a8af-6bd0-4f3e-87ac' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.18.jar:8.0.18]

I printed the sql query and ran it manually and it worked fine. Below is the sql query printed on log.

Hibernate: SELECT * FROM (SELECT * from transactions t where t.book_id=? OR t.reflect_book_id=?  AND t.is_active = true) a inner join (select  id, display_name from customers) b on a.customer_id = b.id order by display_name limit ?

Hope you got this issue, need your all expertise on this issue. Thank you.

Answer 1

• Can you try by specifying the countQuery

@Query(value = "SELECT * FROM (SELECT * from transactions t where t.book_id=?1 OR t.reflect_book_id=?2  AND t.is_active = true) a inner join " +
            "(select  id, display_name from customers) b on a.customer_id = b.id order by display_name",
    countQuery="Your count query goes here",
            nativeQuery = true)
    Page<Transaction> findAllTransactionsByName(String bookId, String reflect_book_id, Pageable pageable);