SpannerRepository methods having @Query annotations with ORDER BY clauses don't sort properly for entities with @Interleaved fields

**Describe the bug**

`SpannerRepository` methods having `@Query` annotations with `ORDER BY` clauses don't sort properly for entities with `@Interleaved` fields.

Using:
  - Spring Boot: 3.3.7
  - Spring Framework: 6.1.16
  - Spring Data Commons: 3.3.7
  - Spring Cloud GCP for Spanner: 5.11.2

**Sample**

Entity classes for the same interleaved tables. One of classes includes an interleaved field and the other does not.

```java
//
// Entity class without interleaved fields
//
@Table(name = "bn_item")
@Getter
@SuperBuilder
@NoArgsConstructor
public class BnItemEn {
  @PrimaryKey
  @Column(name = "item_id")
  private String id;

  @PrimaryKey(2)
  @Column(name = "another_id")
  private String anotherId;

  @Setter
  @Column(name = "description")
  private String description;

  @Column(name = "create_date", nullable = false)
  private Timestamp createDate;
}

//
// Entity class with interleaved fields
//
@Table(name = "bn_item")
@Getter
@SuperBuilder
@NoArgsConstructor
public class BnItemWithInterleavingEn {
  @PrimaryKey
  @Column(name = "item_id")
  private String id;

  @PrimaryKey(2)
  @Column(name = "another_id")
  private String anotherId;

  @Setter
  @Column(name = "description")
  private String description;

  @Column(name = "create_date", nullable = false)
  private Timestamp createDate;

  // @Query annotation ordering does not work when there are @Interleaved annotated fields
  @Interleaved private List<BnItemChildEn> children;
}

//
// Interleaved child entity class
//
@Table(name = "bn_item_child")
@Getter
@SuperBuilder
@NoArgsConstructor
public class BnItemChildEn {
  @PrimaryKey
  @Column(name = "item_id")
  private String id;

  @PrimaryKey(2)
  @Column(name = "another_id")
  private String anotherId;

  @PrimaryKey(3)
  @Column(name = "child_id")
  private String childId;

  @Setter
  @Column(name = "description")
  private String description;

  @Column(name = "create_date", nullable = false)
  private Timestamp createDate;
}
```

Repository classes -- identical SQL in the query annotations.

```java
//
// Repository class for entity without interleaved fields.
//
@Repository
public interface BnItemRepository extends SpannerRepository<BnItemEn, String> {

  String SORT_PROPERTY = "create_date";

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT 1")
  BnItemEn mostRecentRecordWithSortingAndLimitInQueryAnnotation();

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT 5")
  List<BnItemEn> mostRecentFiveRecordsWithSortingAndLimitInQueryAnnotation();

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT @limit")
  List<BnItemEn> mostRecentVariableNumberOfRecordsWithSortingAndLimitInQueryAnnotation(int limit);

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT @limit OFFSET @offset")
  List<BnItemEn> mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
      long offset, int limit);

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC")
  List<BnItemEn> mostRecentRecordsWithSortingAndNoLimitInQueryAnnotation();
}

//
// Repository class for entity with interleaved fields.
//
@Repository
public interface BnItemWithInterleavingRepository
    extends SpannerRepository<BnItemWithInterleavingEn, String> {

  String SORT_PROPERTY = "create_date";

  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT 1")
  BnItemWithInterleavingEn mostRecentRecordWithSortingAndLimitInQueryAnnotation();

  // does not sort properly
  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT 5")
  List<BnItemWithInterleavingEn>
      mostRecentFiveRecordsWithSortingAndLimitInQueryAnnotation();

  // does not sort properly
  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT @limit")
  List<BnItemWithInterleavingEn>
      mostRecentVariableNumberOfRecordsWithSortingAndLimitInQueryAnnotation(int limit);

  // does not sort properly
  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC LIMIT @limit OFFSET @offset")
  List<BnItemWithInterleavingEn>
      mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
          long offset, int limit);

  // does not sort properly
  @Query("SELECT * FROM bn_item ORDER BY " + SORT_PROPERTY + " DESC")
  List<BnItemWithInterleavingEn>
      mostRecentRecordsWithSortingAndNoLimitInQueryAnnotation();
}
```

Unit test that passes for repository for entity without interleaved fields.

```java
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("test")
public class BnItemRepositoryTest {

  // use repeated tests to verify that sorting is consistent and that we didn't just get a
  // lucky ordering, which would be a false positive
  private static final int REPETITIONS = 5;
  private static final int TOTAL_ITEMS = 20;
  private static final AtomicLong UNIQUE_TIMESTAMP_COUNTER = new AtomicLong(0);

  @Autowired private BnItemRepository repository;

  @BeforeEach
  @AfterEach
  public void init() {
    repository.deleteAll();
  }

  static BnItemEnBuilder<?, ?> itemBuilder() {
    var timestamp = generateUniqueTimestamp();
    return BnItemEn.builder()
        .createDate(timestamp)
        .updateDate(timestamp)
        .anotherId(CoreUUID.generateCleanUUIDv4())
        .description(CoreUUID.generateCleanUUIDv4());
  }

  static Timestamp generateUniqueTimestamp() {
    var baseTime = Instant.now();
    var offset = UNIQUE_TIMESTAMP_COUNTER.getAndIncrement();
    var uniqueTime = baseTime.plusMillis(offset);
    return Timestamp.ofTimeSecondsAndNanos(uniqueTime.getEpochSecond(), uniqueTime.getNano());
  }

  @Nested
  class QueryAnnotationSorting {
    private BnItemEn mostRecentItem;
    private List<BnItemEn> items;

    @BeforeEach
    void setUp() {
      items =
          IntStream.range(0, TOTAL_ITEMS)
              .mapToObj(i -> (BnItemEn) itemBuilder().build())
              .map(en -> repository.save(en))
              .sorted(Comparator.comparing(BnItemEn::getCreateDate).reversed())
              .toList();
      mostRecentItem = items.getFirst();
    }

    void assertMostRecentFound(BnItemEn found) {
      assertThat(found).isEqualTo(mostRecentItem);
    }

    void assertMostRecentFound(List<BnItemEn> found, int limit) {
      assertThat(found).hasSize(limit).containsExactlyElementsOf(items.subList(0, limit));
    }

    @Nested
    class MostRecentRecord {

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentRecordWithSortingAndLimitInQueryAnnotation();
        assertMostRecentFound(found);
      }
    }

    @Nested
    class MostRecentFiveRecords {
      private static final int LIMIT = 5;

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentFiveRecordsWithSortingAndLimitInQueryAnnotation();
        assertMostRecentFound(found, LIMIT);
      }
    }

    @Nested
    class MostRecentVariableNumberOfRecords {
      private static final int LIMIT = 7;

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_variable_in_query_annotation() {
        var found =
            repository.mostRecentVariableNumberOfRecordsWithSortingAndLimitInQueryAnnotation(LIMIT);
        assertMostRecentFound(found, LIMIT);
      }
    }

    @Nested
    class MostRecentRecordsWithNoLimit {
      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentRecordsWithSortingAndNoLimitInQueryAnnotation();
        assertMostRecentFound(found, TOTAL_ITEMS);
      }
    }

    @Nested
    class LimitAndOffset {

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_and_offset_variables_in_query_annotation() {
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0, 5);
        assertMostRecentFound(found, 5);
      }

      @RepeatedTest(REPETITIONS)
      void
          sorting_and_limit_and_offset_variables_in_query_annotation_when_offset_is_zero_and_limit_is_one() {
        // SQL: LIMIT 1 OFFSET 0 - skip 0 items, return next 1 (item 0)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0, 1);
        assertThat(found).hasSize(1).containsExactlyElementsOf(items.subList(0, 1));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_aligns_with_page_boundaries() {
        // SQL: LIMIT 5 OFFSET 0 - skip 0 items, return next 5
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(0, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_does_not_align_with_page_boundaries() {
        // SQL: LIMIT 5 OFFSET 12 - skip 12 items, return next 5 (items 12-16)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    12L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(12, 17));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_for_second_page_aligned() {
        // SQL: LIMIT 5 OFFSET 5 - skip 5 items, return next 5 (items 5-9)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    5L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(5, 10));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_for_second_page_not_aligned() {
        // SQL: LIMIT 5 OFFSET 7 - skip 7 items, return next 5 (items 7-11)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    7L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(7, 12));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_near_end() {
        // SQL: LIMIT 3 OFFSET 18 - skip 18 items, return next 3 (items 18-19 = only 2 available)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    18L, 3);
        assertThat(found).hasSize(2).containsExactlyElementsOf(items.subList(18, TOTAL_ITEMS));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_with_limit_of_one() {
        // SQL: LIMIT 1 OFFSET 4 - skip 4 items, return next 1 (item 4)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    4L, 1);
        assertThat(found).hasSize(1).containsExactlyElementsOf(items.subList(4, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_with_large_limit() {
        // SQL: LIMIT 20 OFFSET 2 - skip 2 items, return next 20 (items 2-21 = only 18 available)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    2L, 20);
        assertThat(found).hasSize(18).containsExactlyElementsOf(items.subList(2, TOTAL_ITEMS));
      }

      @RepeatedTest(REPETITIONS)
      void sequential_pages_contain_all_test_items_in_order() {
        // Verify that sequential LIMIT/OFFSET calls produce consistent results
        var combined = new ArrayList<BnItemEn>();
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5)); // items 0-4
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    5L, 5)); // items 5-9
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    10L, 5)); // items 10-14
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    15L, 5)); // items 15-19
        assertThat(combined).hasSize(TOTAL_ITEMS).containsExactlyElementsOf(items);
      }

      @RepeatedTest(REPETITIONS)
      void overlapping_queries_produce_consistent_results() {
        // Verify that overlapping ranges produce expected results
        var items0to4 =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5);
        var items2to6 =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    2L, 5);

        // Items 2-4 should appear in both results
        assertThat(items0to4.subList(2, 5))
            .containsExactlyElementsOf(items2to6.subList(0, 3))
            .containsExactlyElementsOf(items.subList(2, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_empty_list_when_offset_exactly_at_data_boundary() {
        // SQL: LIMIT 5 OFFSET 20 - skip 20 items, but only 20 items exist
        // This tests when offset equals total number of items
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    TOTAL_ITEMS, 5);
        assertThat(found).isEmpty();
      }

      @RepeatedTest(REPETITIONS)
      void returns_empty_list_when_offset_beyond_data_non_aligned() {
        // SQL: LIMIT 3 OFFSET 21 - skip 21 items, but only 20 items exist
        // offset=21, limit=3 → skipInFirstPage=0, fetchSize=3, actualPage=7
        // This tests the edge case where we're beyond the data with a non-aligned offset
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    TOTAL_ITEMS + 1, 3);
        assertThat(found).isEmpty();
      }
    }
  }
}
```

Unit test that fails for repository for entity with interleaved fields (except for the methods with `limit 1` in the sql and the tests that return no results).

```java
@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@ActiveProfiles("test")
public class BnItemWithInterleavingRepositoryTest {

  // use repeated tests to verify that sorting is consistent and that we didn't just get a
  // lucky ordering, which would be a false positive
  private static final int REPETITIONS = 5;
  private static final int TOTAL_ITEMS = 20;
  private static final AtomicLong UNIQUE_TIMESTAMP_COUNTER = new AtomicLong(0);

  @Autowired private BnItemWithInterleavingRepository repository;

  @BeforeEach
  @AfterEach
  public void init() {
    repository.deleteAll();
  }

  static BnItemWithInterleavingEnBuilder<?, ?> itemBuilder() {
    var timestamp = generateUniqueTimestamp();
    return BnItemWithInterleavingEn.builder()
        .createDate(timestamp)
        .updateDate(timestamp)
        .anotherId(CoreUUID.generateCleanUUIDv4())
        .description(CoreUUID.generateCleanUUIDv4());
  }

  static Timestamp generateUniqueTimestamp() {
    var baseTime = Instant.now();
    var offset = UNIQUE_TIMESTAMP_COUNTER.getAndIncrement();
    var uniqueTime = baseTime.plusMillis(offset);
    return Timestamp.ofTimeSecondsAndNanos(uniqueTime.getEpochSecond(), uniqueTime.getNano());
  }

  @Nested
  class QueryAnnotationSorting {
    private BnItemWithInterleavingEn mostRecentItem;
    private List<BnItemWithInterleavingEn> items;

    @BeforeEach
    void setUp() {
      items =
          IntStream.range(0, TOTAL_ITEMS)
              .mapToObj(i -> (BnItemWithInterleavingEn) itemBuilder().build())
              .map(en -> repository.save(en))
              .sorted(Comparator.comparing(BnItemWithInterleavingEn::getCreateDate).reversed())
              .toList();
      mostRecentItem = items.getFirst();
    }

    void assertMostRecentFound(BnItemWithInterleavingEn found) {
      assertThat(found).isEqualTo(mostRecentItem);
    }

    void assertMostRecentFound(List<BnItemWithInterleavingEn> found, int limit) {
      assertThat(found).hasSize(limit).containsExactlyElementsOf(items.subList(0, limit));
    }

    @Nested
    class MostRecentRecord {

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentRecordWithSortingAndLimitInQueryAnnotation();
        assertMostRecentFound(found);
      }
    }

    @Nested
    class MostRecentFiveRecords {
      private static final int LIMIT = 5;

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentFiveRecordsWithSortingAndLimitInQueryAnnotation();
        assertMostRecentFound(found, LIMIT);
      }
    }

    @Nested
    class MostRecentVariableNumberOfRecords {
      private static final int LIMIT = 7;

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_variable_in_query_annotation() {
        var found =
            repository.mostRecentVariableNumberOfRecordsWithSortingAndLimitInQueryAnnotation(LIMIT);
        assertMostRecentFound(found, LIMIT);
      }
    }

    @Nested
    class MostRecentRecordsWithNoLimit {
      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_in_query_annotation() {
        var found = repository.mostRecentRecordsWithSortingAndNoLimitInQueryAnnotation();
        assertMostRecentFound(found, TOTAL_ITEMS);
      }
    }

    @Nested
    class LimitAndOffset {

      @RepeatedTest(REPETITIONS)
      void sorting_and_limit_and_offset_variables_in_query_annotation() {
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0, 5);
        assertMostRecentFound(found, 5);
      }

      @RepeatedTest(REPETITIONS)
      void
          sorting_and_limit_and_offset_variables_in_query_annotation_when_offset_is_zero_and_limit_is_one() {
        // SQL: LIMIT 1 OFFSET 0 - skip 0 items, return next 1 (item 0)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0, 1);
        assertThat(found).hasSize(1).containsExactlyElementsOf(items.subList(0, 1));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_aligns_with_page_boundaries() {
        // SQL: LIMIT 5 OFFSET 0 - skip 0 items, return next 5
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(0, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_does_not_align_with_page_boundaries() {
        // SQL: LIMIT 5 OFFSET 12 - skip 12 items, return next 5 (items 12-16)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    12L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(12, 17));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_for_second_page_aligned() {
        // SQL: LIMIT 5 OFFSET 5 - skip 5 items, return next 5 (items 5-9)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    5L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(5, 10));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_for_second_page_not_aligned() {
        // SQL: LIMIT 5 OFFSET 7 - skip 7 items, return next 5 (items 7-11)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    7L, 5);
        assertThat(found).hasSize(5).containsExactlyElementsOf(items.subList(7, 12));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_when_offset_near_end() {
        // SQL: LIMIT 3 OFFSET 18 - skip 18 items, return next 3 (items 18-19 = only 2 available)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    18L, 3);
        assertThat(found).hasSize(2).containsExactlyElementsOf(items.subList(18, TOTAL_ITEMS));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_with_limit_of_one() {
        // SQL: LIMIT 1 OFFSET 4 - skip 4 items, return next 1 (item 4)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    4L, 1);
        assertThat(found).hasSize(1).containsExactlyElementsOf(items.subList(4, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_correct_results_with_large_limit() {
        // SQL: LIMIT 20 OFFSET 2 - skip 2 items, return next 20 (items 2-21 = only 18 available)
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    2L, 20);
        assertThat(found).hasSize(18).containsExactlyElementsOf(items.subList(2, TOTAL_ITEMS));
      }

      @RepeatedTest(REPETITIONS)
      void sequential_pages_contain_all_test_items_in_order() {
        // Verify that sequential LIMIT/OFFSET calls produce consistent results
        var combined = new ArrayList<BnItemWithInterleavingEn>();
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5)); // items 0-4
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    5L, 5)); // items 5-9
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    10L, 5)); // items 10-14
        combined.addAll(
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    15L, 5)); // items 15-19
        assertThat(combined).hasSize(TOTAL_ITEMS).containsExactlyElementsOf(items);
      }

      @RepeatedTest(REPETITIONS)
      void overlapping_queries_produce_consistent_results() {
        // Verify that overlapping ranges produce expected results
        var items0to4 =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    0L, 5);
        var items2to6 =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    2L, 5);

        // Items 2-4 should appear in both results
        assertThat(items0to4.subList(2, 5))
            .containsExactlyElementsOf(items2to6.subList(0, 3))
            .containsExactlyElementsOf(items.subList(2, 5));
      }

      @RepeatedTest(REPETITIONS)
      void returns_empty_list_when_offset_exactly_at_data_boundary() {
        // SQL: LIMIT 5 OFFSET 20 - skip 20 items, but only 20 items exist
        // This tests when offset equals total number of items
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    TOTAL_ITEMS, 5);
        assertThat(found).isEmpty();
      }

      @RepeatedTest(REPETITIONS)
      void returns_empty_list_when_offset_beyond_data_non_aligned() {
        // SQL: LIMIT 3 OFFSET 21 - skip 21 items, but only 20 items exist
        // offset=21, limit=3 → skipInFirstPage=0, fetchSize=3, actualPage=7
        // This tests the edge case where we're beyond the data with a non-aligned offset
        var found =
            repository
                .mostRecentVariableNumberOfRecordsWithSortingAndLimitAndOffsetInQueryAnnotation(
                    TOTAL_ITEMS + 1, 3);
        assertThat(found).isEmpty();
      }
    }
  }
}
```

**Workarounds**

Our workaround is to use DAOs with Statements instead of repository methods for the queries. Those do not seem to exhibit the same sorting problems.

Another workaround, though far less desirable, is to remove the `ORDER BY ...` clauses from the SQL in the repository methods and change the method signature to take a Pageable or Sort argument and configure the arguments as needed. Handling `ORDER BY ...  LIMIT n OFFSET m` in this manner required the development of a pretty complex helper that replicates SQL LIMIT/OFFSET semantics using paging semantics.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

SpannerRepository methods having @Query annotations with ORDER BY clauses don't sort properly for entities with @Interleaved fields #4244

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SpannerRepository methods having @Query annotations with ORDER BY clauses don't sort properly for entities with @Interleaved fields #4244

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions