Blazebit
Spring Data JPA OR composed Specification keyset pagination not paging correctly
BlazePaginationRepository:
public PagedList<T> findTopN(Specification<T> specification,
Sort sortBy,
int pageSize,
Class<T> focalEntityClass) {
return sortedCriteriaBuilder(specification, sortBy, focalEntityClass)
.page(0, pageSize)
.withKeysetExtraction(true)
.withExtractAllKeysets(true)
.getResultList();
}
public PagedList<T> findNextN(Specification<T> specification,
Sort sortBy,
PagedList<T> previousPage,
Class<T> focalEntityClass) {
return sortedCriteriaBuilder(specification, sortBy, focalEntityClass)
.page(
previousPage.getKeysetPage(),
previousPage.getPage() * previousPage.getMaxResults(),
previousPage.getMaxResults()
)
.withKeysetExtraction(true)
.withExtractAllKeysets(true)
.getResultList();
}
private CriteriaBuilder<T> sortedCriteriaBuilder(Specification<T> specification, Sort sortBy, Class<T> focalEntityClass) {
BlazeCriteriaBuilder cb = BlazeCriteria.get(cbf);
BlazeCriteriaQuery<T> query = cb.createQuery(focalEntityClass);
Root<T> root = query.from(focalEntityClass);
if(null != specification){
query.where(specification.toPredicate(root, query, cb));
}
CriteriaBuilder<T> builder = query.createCriteriaBuilder(entityManager);
sortBy.forEach(order -> {
builder.orderBy(
order.getProperty(),
order.isAscending()
);
});
return builder;
}
@Repository
public interface EntityRepository extends JpaRepository<FocalEntity, FocalEntityID>,
JpaSpecificationExecutor<FocalEntity>,
BlazePaginationRepository<FocalEntity> {
Expected behavior
expected: <2>
Actual behavior
but was: <3>
Steps to reproduce
@BeforeEach
void insert(){
entityRepository.save(getFocalEntity("AAPL"));
entityRepository.save(getFocalEntity("SNAP"));
entityRepository.save(getFocalEntity("TWTR"));
entityRepository.save(getFocalEntity("QQQ"));
entityRepository.save(getFocalEntity("TSLA"));
entityRepository.save(getFocalEntity("AMZN"));
entityRepository.save(getFocalEntity("GOOGL"));
entityRepository.save(getFocalEntity("ADBE"));
entityRepository.save(getFocalEntity("META"));
entityRepository.save(getFocalEntity("TMUS"));
entityRepository.save(getFocalEntity("SPY"));
entityRepository.save(getFocalEntity("AMD"));
entityRepository.save(getFocalEntity("AMEX"));
}
@Test
void testSpecificationWithBlazeOr(){
Sort sort = Sort.by(FocalEntity_.HEADER_START_DATETIME).ascending()
.and(Sort.by(FocalEntity_.BUSINESS_KEY).ascending()
.and(Sort.by(FocalEntity_.PRIME_RECORD_IDENTIFIER)).ascending()
.and(Sort.by(FocalEntity_.VERSION).ascending()));
Specification<FocalEntity> nameBeginsWithAOrT = nameBeginsWith("A").or(nameBeginsWith("T"));
PagedList<FocalEntity> firstPage = entityRepository.findTopN(nameBeginsWithAOrT, sort, 3, FocalEntity.class);
Assertions.assertEquals(3, firstPage.size());
PagedList<FocalEntity> secondPage = entityRepository.findNextN(nameBeginsWithAOrT, sort, firstPage,FocalEntity.class);
Assertions.assertEquals(3, secondPage.size());
Assertions.assertEquals(3, secondPage.getTotalPages());
Assertions.assertEquals(8, secondPage.getTotalSize());
//blaze does not iterate over OR correctly
PagedList<FocalEntity> third = entityRepository.findNextN(nameBeginsWithAOrT, sort, secondPage, FocalEntity.class);
Assertions.assertEquals(2, third.size()); <---- fails
}
Environment
Version: Using Spring Boot 2.7.x, Following https://vladmihalcea.com/keyset-pagination-spring/ JPA-Provider: Hibernate DBMS: H2 Application Server: web(tomcat)
Find TopN:
Hibernate:
select
focalentit0_.prime_record_identifier as col_0_0_,
focalentit0_.version as col_0_1_,
focalentit0_.header_start_datetime as col_1_0_,
focalentit0_.business_key as col_2_0_,
focalentit0_.prime_record_identifier as col_3_0_,
focalentit0_.version as col_4_0_,
(select
count(*)
from
focal_entity focalentit1_
where
focalentit1_.business_key like replace(?,'\\','\\\\')
or focalentit1_.business_key like replace(?,'\\','\\\\')) as col_5_0_,
focalentit0_.prime_record_identifier as prime_re1_0_,
focalentit0_.version as version2_0_,
focalentit0_.ask_price_account_id as ask_pric3_0_,
focalentit0_.ask_price_account_name as ask_pric4_0_,
focalentit0_.ask_price_as_of_date as ask_pric5_0_,
focalentit0_.ask_price_cents as ask_pric6_0_,
focalentit0_.ask_price_currency as ask_pric7_0_,
focalentit0_.ask_price_value as ask_pric8_0_,
focalentit0_.bid_price_account_id as bid_pric9_0_,
focalentit0_.bid_price_account_name as bid_pri10_0_,
focalentit0_.bid_price_as_of_date as bid_pri11_0_,
focalentit0_.bid_price_cents as bid_pri12_0_,
focalentit0_.bid_price_currency as bid_pri13_0_,
focalentit0_.bid_price_value as bid_pri14_0_,
focalentit0_.business_key as busines15_0_,
focalentit0_.cash_instrument_id as cash_in16_0_,
focalentit0_.cash_principal_qty as cash_pr17_0_,
focalentit0_.event_impact_business_id as event_i18_0_,
focalentit0_.event_impact_business_process_type as event_i19_0_,
focalentit0_.event_impact_business_type as event_i20_0_,
focalentit0_.event_impact_sys_event_type as event_i21_0_,
focalentit0_.header_end_datetime as header_22_0_,
focalentit0_.header_start_datetime as header_23_0_,
focalentit0_.processing_is_ticket_released as process24_0_,
focalentit0_.processing_is_trade_blotter_reporting_skipped as process25_0_,
focalentit0_.processing_is_trade_feed_received as process26_0_,
focalentit0_.schema_major_version as schema_27_0_,
focalentit0_.schema_minor_version as schema_28_0_,
focalentit0_.schema_patch_version as schema_29_0_,
focalentit0_.ticket_datetime as ticket_30_0_,
focalentit0_.ticket_num_id as ticket_31_0_,
focalentit0_.ticket_type as ticket_32_0_
from
focal_entity focalentit0_
where
focalentit0_.business_key like replace(?,'\\','\\\\')
or focalentit0_.business_key like replace(?,'\\','\\\\')
order by
focalentit0_.header_start_datetime ASC,
focalentit0_.business_key ASC,
focalentit0_.prime_record_identifier ASC,
focalentit0_.version ASC limit ?
Find Next N:
select
focalentit0_.prime_record_identifier as col_0_0_,
focalentit0_.version as col_0_1_,
focalentit0_.header_start_datetime as col_1_0_,
focalentit0_.business_key as col_2_0_,
focalentit0_.prime_record_identifier as col_3_0_,
focalentit0_.version as col_4_0_,
(select
count(*)
from
focal_entity focalentit1_
where
focalentit1_.business_key like replace(?,'\\','\\\\')
or focalentit1_.business_key like replace(?,'\\','\\\\')) as col_5_0_,
focalentit0_.prime_record_identifier as prime_re1_0_,
focalentit0_.version as version2_0_,
focalentit0_.ask_price_account_id as ask_pric3_0_,
focalentit0_.ask_price_account_name as ask_pric4_0_,
focalentit0_.ask_price_as_of_date as ask_pric5_0_,
focalentit0_.ask_price_cents as ask_pric6_0_,
focalentit0_.ask_price_currency as ask_pric7_0_,
focalentit0_.ask_price_value as ask_pric8_0_,
focalentit0_.bid_price_account_id as bid_pric9_0_,
focalentit0_.bid_price_account_name as bid_pri10_0_,
focalentit0_.bid_price_as_of_date as bid_pri11_0_,
focalentit0_.bid_price_cents as bid_pri12_0_,
focalentit0_.bid_price_currency as bid_pri13_0_,
focalentit0_.bid_price_value as bid_pri14_0_,
focalentit0_.business_key as busines15_0_,
focalentit0_.cash_instrument_id as cash_in16_0_,
focalentit0_.cash_principal_qty as cash_pr17_0_,
focalentit0_.event_impact_business_id as event_i18_0_,
focalentit0_.event_impact_business_process_type as event_i19_0_,
focalentit0_.event_impact_business_type as event_i20_0_,
focalentit0_.event_impact_sys_event_type as event_i21_0_,
focalentit0_.header_end_datetime as header_22_0_,
focalentit0_.header_start_datetime as header_23_0_,
focalentit0_.processing_is_ticket_released as process24_0_,
focalentit0_.processing_is_trade_blotter_reporting_skipped as process25_0_,
focalentit0_.processing_is_trade_feed_received as process26_0_,
focalentit0_.schema_major_version as schema_27_0_,
focalentit0_.schema_minor_version as schema_28_0_,
focalentit0_.schema_patch_version as schema_29_0_,
focalentit0_.ticket_datetime as ticket_30_0_,
focalentit0_.ticket_num_id as ticket_31_0_,
focalentit0_.ticket_type as ticket_32_0_
from
focal_entity focalentit0_
where
(
?, ?, ?, ?
) < (
focalentit0_.header_start_datetime, focalentit0_.business_key, focalentit0_.prime_record_identifier, focalentit0_.version
)
and 0=0
and (
focalentit0_.business_key like replace(?,'\\','\\\\')
)
or focalentit0_.business_key like replace(?,'\\','\\\\')
order by
focalentit0_.header_start_datetime ASC,
focalentit0_.business_key ASC,
focalentit0_.prime_record_identifier ASC,
focalentit0_.version ASC limit ?
Causes this sort of sliding window where just one record from previous page is thrown away while other two are retained (page size was 3) even though total size and total pages values are correct.
I think you might be running into something related to https://github.com/Blazebit/blaze-persistence/issues/1638 which was fixed via https://github.com/Blazebit/blaze-persistence/commit/087aa00323018b7940db05197c3a2cc2cd3634e7.
Can you try the 1.6.9-SNAPSHOT please?
It's there in the snapshots repository: https://oss.sonatype.org/content/repositories/snapshots/
Is it different than building from source? I tried that and got the same issue.
@beikov We aren't able to view the contents of the URL (Directory listing forbidden) and can't easily get snapshot dependencies hence I went building from source route.
It's the same as building from source. Including the snapshots repository is described here: https://maven.apache.org/guides/mini/guide-multiple-repositories.html
Do you think you can share a reproducer with me so I can take a deeper look? You can use the Spring Boot Archetype if that makes it easier for you:
mvn archetype:generate "-DarchetypeGroupId=com.blazebit" "-DarchetypeArtifactId=blaze-persistence-archetype-spring-boot-sample" "-DarchetypeVersion=1.6.8"
@beikov Used the archetype and created the project. Updated Spring Boot and Java versions. https://github.com/adityaparikh91087/specification/blob/main/src/test/java/com/blazebit/springdatajpa/sample/SpecificationTest.java#L67 Here is the failing test line.
Sorry, not yet. The Hibernate 6.2 integration for Quarkus 3 and Spring Boot 3 has priority right now.