WindowIterator does not consider scroll direction

[thjanssen]

I played around with the keyset scrolling feature, and I either don't understand backward scrolling or found an issue.

I expected that this

WindowIterator<ChessPlayer> players = WindowIterator.of(pos -> playerRepo.findFirst2ByLastName("Doe", pos))
                                                                                               .startingAt(ScrollPosition.keyset().backward());

would give me a WindowIterator that puts the resultset in the descending order of the primary key and moves from there in windows of 2 until it reaches the lowest primary key (= last record).

But it only seems to work for the 1st window. The query that fetches the 2nd window is missing the DESC keyword to reverse the order.

2023-06-12T15:27:33.354+02:00 DEBUG 7520 --- [           main] org.hibernate.SQL                        : 
    select
        c1_0.id,
        c1_0.birth_date,
        c1_0.first_name,
        c1_0.last_name,
        c1_0.version 
    from
        chess_player c1_0 
    where
        c1_0.last_name=? 
    order by
        c1_0.id desc fetch first ? rows only
2023-06-12T15:27:33.371+02:00  INFO 7520 --- [           main] c.t.janssen.spring.data.TestPagination   : Doe, John14
2023-06-12T15:27:33.371+02:00  INFO 7520 --- [           main] c.t.janssen.spring.data.TestPagination   : Doe, John15
2023-06-12T15:27:33.381+02:00 DEBUG 7520 --- [           main] org.hibernate.SQL                        : 
    select
        c1_0.id,
        c1_0.birth_date,
        c1_0.first_name,
        c1_0.last_name,
        c1_0.version 
    from
        chess_player c1_0 
    where
        c1_0.last_name=? 
        and (
            c1_0.id>?
        ) 
    order by
        c1_0.id fetch first ? rows only
2023-06-12T15:27:33.383+02:00  INFO 7520 --- [           main] c.t.janssen.spring.data.TestPagination   : Doe, John15

[mp911de]

Related to spring-projects/spring-data-jpa#2999.

The backward direction isn't retained across windows, instead it is being reset to forward. This is by design, yet I agree that it is weird when using that feature. We need to fix this.

[mp911de]

When scrolling backward, we need to use the scroll position of the first item as reference. Right now, we always use the last item causing to behave as in forward-scrolling.