[DEFECT] GraphQL Pagination and total results count mismatch and offset error

[Laura-dotCMS]

Problem Statement

The pagination record count displayed in graphQL is inconsistent with the actual number of queryable records, leading to Internal Server Errors (500) when attempting to paginate using an offset that, while theoretically valid based on the reported total count, exceeds the actual number of non-archived, unlocked, and otherwise-filtered records.

The "Total Records" count appears to be incorrectly calculating the total by including records that are being explicitly or implicitly excluded from the default query (e.g., archived, locked, or outdated articles), which results in a mismatch between the reported total and the number of records available for pagination. Furthermore, attempting to use an offset and limit combination that exactly equals the reported, yet inflated, "Total Records" count, or goes slightly beyond the actual available records, throws a 500 error instead of simply returning fewer or zero results.

https://drive.google.com/file/d/1mU4h4FfXdQgvdF0AVz366AOAxVVPxVD5/view?usp=sharing

Steps to Reproduce

1. Initial Query with Inconsistent Count:
   Set limit=25.
   Set languageID to a specific language (e.g., "English CA").
   Expected Result: The "Total Records" count should reflect only the actively queryable articles.
   Actual Result: "Total Records" reports 50. The actual search returns 49 records. (The extra record is later confirmed to be an archived article.)

2. Pagination Error (Internal Server Error 500):
   Using the reported total of 50 and a limit of 25:
   Perform a second query with limit=25 and offset=25 (attempting to view the second page).
   Expected Result: Returns the remaining 25 articles, or fewer if the actual count is lower than the reported 50.
   Actual Result: Returns an Internal Server Error (500).

Observing Discrepancy with No Language Filter:
Remove the languageID filter.
Actual Result: "Total Records" reports 108. The actual search returns 96. (The difference of 12 is later confirmed to be the number of archived records.)
Again, attempting to paginate with an offset based on the reported 108 will likely lead to an error before reaching the 108th record.

Identifying Exclusion Parameters:
Add live=true parameter: The "Total Records" count correctly drops from 50 (with languageID) to 49.
Add locked=false parameter: The count drops further to 48 (indicating one locked article was included in the 49).
Despite the count being 48, attempts to pull a full limit of 48, or even 47, result in an error, suggesting the actual available count is even lower (observed to be around 44).
Further filtering with article_added_date=false and next_review_date filters continues to refine the count, but the underlying issue of the server error when the offset/limit exceeds the actual available records persists.

Acceptance Criteria

Expected Behavior
Accurate Record Count: The "Total Records" count should only reflect the articles available in the current query result set, respecting all active filters (e.g., live=true, locked=false, etc.). If a user wants to see archived records, they should have to explicitly request them.

Graceful Pagination Handling: When the sum of offset + limit exceeds the actual number of available records, the API should return the remaining records (which would be fewer than the limit) or an empty list, but should not throw an Internal Server Error (500). For example, if there are 30 total records and the query is limit=20, offset=20, the server should return the remaining 10 records, not a 500 error.

External Links

https://helpdesk.dotcms.com/a/tickets/33484

dotCMS Version

dotcms-core 25.09.25-01 (September 25, 2025 9:22 PM)

Severity

High - Major functionality broken

[gortiz-dotcms]

1- Regarding the first issue (Initial Query with Inconsistent Count):
The actual behavior matches what the Search portlet does. When we open that portlet and go to Search > Show query, we can see the Lucene query sent to the API is: +contentType:{contentType} +(conhost:{hostId} conhost:SYSTEM_HOST) +languageId:{langId} +deleted:false +working:true +variant:default
The query explicitly includes deleted:false, which is why the count shown in the portlet only reflects non-deleted articles. So the GraphQL query pulling all articles when that parameter isn’t included is actually expected behavior — same idea applies to locked, working, and other flags.
2 - About the 500 error:
I wasn’t able to reproduce it. I followed the exact scenarios from the video, including tests directly on the client environment, and everything worked as expected. It’s possible the error was caused by an index in a bad state. Since a reindex was performed between the time the issue was reported and when I tested it, if that was the root cause, the 500 error should now be resolved.

Closing the issue, as the behavior is expected and I wasn’t able to reproduce the problem.