Do I really need countQuery in a native @Query for pagination?

Question

According to the reference, countQuery is required for native queries pagination (at least there is an example with it). But according to my tests (Spring Boot 2.4.5), @Query pagination works without countQuery for such queries:

public interface ARepository extends JpaRepository<A, Long>{
  @Query(value = "select * from a", nativeQuery = true)
  Page<A> findAllANative(Pageable pageable);

  @Query(value = "select count(a.id) as aCount, category.name as 
        categoryName " +
        "from a " +
        "join category on a.category_id=category.id " +
        "group by category.name",
        nativeQuery = true)
  List<CountView> findACountByCategoryNative(Pageable pageable);
}

As you can see, there is no countQuery and they work.

Does pagination work only for these specific queries or I can omit countQuery in all cases?

Answer 1

Perhaps, it's still getting changed with new versions of Spring Data.

I am also wondering about the cases when I do really need to manually copy-paste my query for countQuery, and when not. But everything I could find on it so far is this comment from that same Spring Docs' page you referenced in the question (emphasis is mine):

Spring Data can rewrite simple queries for pagination and sorting. More complex queries require either JSqlParser to be on the class path or a countQuery declared in your code. See the example below for more details.

Nevertheless, their "example below" doesn't seem to shed light upon what queries are to be considered "complex". It only illustrates the usage of countQuery, instead:

public interface UserRepository extends JpaRepository<User, Long> {

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

I would expect it required in cases with complex queries like having CTEs or subqueries inside the SELECT expression among other columns.

For the while it stays an undocumented behavior, I would suggest we always provide countQuery to avoid unexpected results.