Spring Boot: Slice/Pageable not returning proper chunk based on page

Question

In my PSQL DB, I have two objects stored and I would like to retrieve each item on their separate page/slice. I am trying to achieve this by passing in the following Page objects:

PageRequest.of(0,1) for the first item and PageRequest.of(1, 1) for the second item.

However, when I create the Pageable object via PageRequest.of(1, 1), this always results in only the first item being returned every time, but I have confirmed that both items indeed does exist by calling repo.findAll().

What am I doing incorrectly?

My service layer call looks like the following:

 @Transactional
  public Slice<Foo> findAllInactive(Pageable pageable) {
    return repo.findAllInactive(new Date(), pageable));
  }

And my repo is:

@Repository
public interface FooRepository extends JpaRepository<Foo, String> {
  
      value =
          "SELECT * FROM fooschema.foo i WHERE i.valid_until < :currentDate OR i.valid_until IS NULL --#pageable\n",
      nativeQuery = true,
      countQuery = "SELECT count(*) FROM fooschema.foo i")
  Slice<Foo> findAllInactive(@Param("currentDate") Date currentDate, Pageable pageable);
}

If it makes any difference, here is the test call

  @Autowired private MockMvc mvc;

  @Test
  void testStuff() throws Exception {
    // two elements added....

    ResultActions resultActions =
            mvc.perform(
                    get("/foo")
                            .param("page", "1")
                            .param("size", "1"))// should return the second element, but returns the first
                    .andExpect(status().isOk())
                    .andExpect(content().contentType("application/json")); 
  }

and the controller

@RestController
@RequestMapping("/foo")
public class FooController {

  @GetMapping
  @ApiImplicitParams({
    @ApiImplicitParam(
        name = "page",
        dataType = "int",
        paramType = "query",
        value = "Page you want to retrieve",
        defaultValue = "0"),
    @ApiImplicitParam(
        name = "size",
        dataType = "int",
        paramType = "query",
        value = "Number of foo per page.",
        defaultValue = "10"))
  public Slice<Foo> getFoo(Pageable pageable) {
        return service.findAllInactive(pageable);
    }
}

Answer 1

Anshul's comment got me on the right track, and in the end, it appears that creating a derived query, as noted here: https://www.baeldung.com/spring-data-derived-queries, works.

In the end, the following got it working for me:

Slice<Foo> findByValidUntilIsNullOrValidUntilBefore(Date currentDate, Pageable pageable); // or can return a List<Foo>