[ https://issues.apache.org/jira/browse/FINERACT-2725?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Foo Developer updated FINERACT-2725:
------------------------------------
Description:
Description:
## Bug
`PaginationParameters.limitSql()` generates lowercase SQL clauses:
" limit 25 offset 180"
These get appended to the query in services like `AuditReadPlatformServiceImpl`
before being passed to `PaginationHelper.fetchPage`. On PostgreSQL,
`PaginationHelper` calls `DatabaseSpecificSQLGenerator.countQueryResult` to
build a total-count query by wrapping the SQL in SELECT COUNT(*) FROM (...).
`countQueryResult` was supposed to strip the LIMIT/OFFSET before wrapping,
but the regex patterns were case-sensitive:
sql.replaceAll("LIMIT \\d+", "").replaceAll("OFFSET \\d+", "")
Since `limitSql()` emits lowercase, neither pattern matched. The resulting
count query was:
SELECT COUNT(*) FROM (SELECT ... limit 25 offset 180) AS temp
The database applied the LIMIT to the outer COUNT, so the API always returned
a `totalFilteredRecords` value capped at the page size instead of the true
total. This affected any paginated endpoint backed by `PaginationHelper` on
PostgreSQL — most visibly the audit trail endpoint.
## Fix
Make both patterns case-insensitive and use \s+ to handle any whitespace
between the keyword and the number:
sql.replaceAll("(?i)LIMIT\\s+\\d+", "").replaceAll("(?i)OFFSET\\s+\\d+", "")
Two unit tests added to `DatabaseSpecificSQLGeneratorTest` covering lowercase
limit-only and lowercase limit+offset cases.
> Audit trails api pagination
> ---------------------------
>
> Key: FINERACT-2725
> URL: https://issues.apache.org/jira/browse/FINERACT-2725
> Project: Apache Fineract
> Issue Type: Bug
> Components: System
> Reporter: Foo Developer
> Assignee: Foo Developer
> Priority: Major
>
> Description:
>
> ## Bug
>
> `PaginationParameters.limitSql()` generates lowercase SQL clauses:
> " limit 25 offset 180"
>
> These get appended to the query in services like `AuditReadPlatformServiceImpl`
> before being passed to `PaginationHelper.fetchPage`. On PostgreSQL,
> `PaginationHelper` calls `DatabaseSpecificSQLGenerator.countQueryResult` to
> build a total-count query by wrapping the SQL in SELECT COUNT(*) FROM (...).
>
> `countQueryResult` was supposed to strip the LIMIT/OFFSET before wrapping,
> but the regex patterns were case-sensitive:
>
> sql.replaceAll("LIMIT \\d+", "").replaceAll("OFFSET \\d+", "")
>
> Since `limitSql()` emits lowercase, neither pattern matched. The resulting
> count query was:
>
> SELECT COUNT(*) FROM (SELECT ... limit 25 offset 180) AS temp
>
> The database applied the LIMIT to the outer COUNT, so the API always returned
> a `totalFilteredRecords` value capped at the page size instead of the true
> total. This affected any paginated endpoint backed by `PaginationHelper` on
> PostgreSQL — most visibly the audit trail endpoint.
> ## Fix
>
> Make both patterns case-insensitive and use \s+ to handle any whitespace
> between the keyword and the number:
>
> sql.replaceAll("(?i)LIMIT\\s+\\d+", "").replaceAll("(?i)OFFSET\\s+\\d+", "")
>
> Two unit tests added to `DatabaseSpecificSQLGeneratorTest` covering lowercase
> limit-only and lowercase limit+offset cases.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)