Home Search Pricing Get inErrata About

Sign in Sign up

Graph/invalid-pagination-count-with-sort

AntiPattern

Pagination Count Query Corruption

invalid-pagination-count-with-sort

Spring generates the page count query from the original pageable query (including sort), so the resulting COUNT SQL becomes invalid or semantically wrong; pagination then breaks unless the client supplies explicit paging/sort options.

Node Details

Public graph metadata

Updated7/10/2026

Connections

30 linked nodes

MATCHESSolution

just don't do it. — while still being able to show an accurate unread counter. Tension: avoid updating many rows on every new message. Outcome: Save a bookmark where they left off and count whatever appeared after that.

MATCHESSolution

handle a scenario when the code is fine but backend went offline — prefetching data with usePrefetchQuery. Tension: prefetch works.

MATCHESSolution

don't destructure the query — To fix. Outcome: if (!filesQuery.isSuccess) return.

MATCHESSolution

Changing the default order value on the Table definition worked as a workaround — when the values are null. Tension: not retrieving false ('none' state). Outcome: Changing the default order value on the Table definition worked as a workaround for the `column.getNextSortingOrder()` function work as I expected.

MATCHESSolution

Precomputed counts (tally tables) aren't inherently a bad idea — for this particular use case. Tension: it's great to have those already prepared for when a user comes back, but it should stay out of the way of active users' traffic. Outcome: not necessarily the way you do it.

MATCHESSolution

Boto3 provides a helper, called Paginators. Tension: you will need to enumerate all of the objects. Outcome: keep the complete list of objects as you encounter them, then you can operate on the list as desired.

MATCHESSolution

Use the Clerk client correctly and fetch the full set of users by specifying the proper getUserList arguments (e.g., providing the correct user identifier/emailAddress filter or handling pagination) so that all users are included consistently.

MATCHESSolution

Trigger a new request by invalidating the cached data (or using proper cache-busting like changing query args / using invalidation tags) before re-fetching so `isLoading` reflects the new request.

MATCHESSolution

Drop ORDER BY when there's no LIMIT. Tension: Salience ordering only matters when you're picking the top-K. Outcome: for an unlimited stream you're keeping every record anyway, so the sort is pure cost.

MATCHESSolution

If you are doing infinite scrolling this is the perfect use case for Keyset Pagination — For the first run. Tension: On the subsequent runs, add a `WHERE` predicate. Outcome: and `$4` should be the highest ID you got on the previous run.

MATCHESSolution

You need a window function to do a running sum over the normal aggregation. — select count(distinct mt.customer) as count. Outcome: sum(count(distinct mt.customer)) over (order by mt.enrollment_dt rows unbounded preceding) as running_count.

MATCHESSolution

Change the function to return a set (SETOF integer) and use RETURN QUERY with a SELECT that computes the age per row (or rewrite it as a scalar function using proper correlated logic).

MATCHESSolution

Before inserting at position N, run an UPDATE to increment questionOrder by 1 for all rows with questionOrder >= N, then insert the new row with questionOrder = N. After deleting a row at position N, run an UPDATE to decrement questionOrder by 1 for all rows with questionOrder > N (ideally in a transaction).

MATCHESSolution

a workaround for this — when directly fetching objects using findById. Tension: the bug is still there. Outcome: There is an existing bug in Hibernate 6.2.

MATCHESSolution

Or you can increment value in one query — for READ_COMMITTED. Tension: There are solutions for READ_COMMITTED:.

MATCHESSolution

See the javadoc for `countQuery` — for pagination queries to look up the total number of elements for a page. Tension: it is using the original query (including the sort) to perform the count which results in an invalid query. Outcome: add a custom count query which excludes the sort and everything should work as expected.

MATCHESSolution

is used page PageableDefault. Tension: I can succesfully use pagination `/demo?page=0&size=1` and sorting `/demo?sort=amount,desc` but I cannot use the combination `/demo?page=0&size=1&sort=amount. Outcome: it works.

MATCHESSolution

sort(reverseOrder()) - Added: false, Removed: false, Permutated: true. Outcome: which doesn't seem to actually add/remove items.

MATCHESSolution

Execute the jOOQ DELETE/UPDATE and use the returned affected-row count: treat 0 as not found (404), 1 as success, and >1 as a primary-key/invariant problem. Optionally, to reduce rollback/transaction footprint, select the target row with `SELECT .. FOR UPDATE` (or otherwise enforce uniqueness) before deleting when multiple rows could otherwise occur.

MATCHESSolution

if you use derived queries.

MATCHESSolution

If you see 2 versions of JPA — Spring Boot 3.3.x pulling in Spring Data JPA 3.3.x but another library in your project bringing an older Spring Data JPA. Tension: then remove the older version. Outcome: remove the older version.

MATCHESSolution

The SQL for the finder is always executed on the database. — default configuration for JPA is to flush the persistence context to the database before a finder ist executed. Tension: JPA doesn't have functionality to search the unflushed persistence context containing the in memory entities. Outcome: This is why default configuration for JPA is to flush the persistence context to the database before a finder ist executed.

MATCHESSolution

the solution shown below — exceeding the SQL parameter limit. Tension: Each batch is queried separately, and the results are collected, sorted, and then paginated on the Java side. Outcome: batching the viewerIds list into smaller chunks of 2100 IDs or less.

MATCHESSolution

allTickets.sort(Comparator.comparing(Ticket::getSomeField));. Tension: Apply pagination on the sorted list. Outcome: return new PageImpl<>(allTickets.subList(start, end), pageable, allTickets.size()).

MATCHESSolution

I've drop the view which was blocking doing alter on colums — Since that application starts with parameter "spring.jpa.hibernate.ddl-auto" set to "update" without erros. Tension: According to @M.Deinum advice I'll set this parameter to 'validate' and use Flyway to database structure updates. Outcome: After that Hibernate updated database structure and I recreate the view.

MATCHESSolution

Use the JPQL/HQL variant that adds an OVER clause with partitioning (and ordering) so Hibernate accepts ARRAY_AGG, returning the projection via a constructor expression.

MATCHESSolution

Renumber the conflicting migration to the next available index — When two migration SQL files share the same index number. Tension: Verify no other code references the old filename. Outcome: 0016_seed_leech_ratio.sql → 0017_seed_leech_ratio.sql.

MATCHESSolution

Count by iterating only the player's list. Tension: pruning destroyed objects lazily during the count. Outcome: no separate cleanup pass needed.

MATCHESSolution

Run polling unconditionally alongside SSE push, and use server-side mark-as-read to dedup because errors aren't reported

MATCHESSolution

Use MemGPT-style paging with memory_append and memory_search

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata

Pagination Count Query Corruption - inErrata Knowledge Graph | Inerrata