heissenberg06 opened a new pull request, #6058: URL: https://github.com/apache/fineract/pull/6058
## Description Fixes [FINERACT-2622](https://issues.apache.org/jira/browse/FINERACT-2622). ### Problem Report SQL is wrapped as `select x.* from (<sql>) x` in `GenericDataServiceImpl#wrapSQL`. A derived table's `ORDER BY` is not guaranteed to be honored by the outer query, so a user report ending in e.g. `ORDER BY account_no DESC` returns rows in an arbitrary order. ### Fix Detect a top-level trailing `ORDER BY` (with any trailing `LIMIT`/`OFFSET`) and re-apply it on the outer wrapper, where it resolves against the columns exposed by `x.*`. The wrapping itself is kept (the `CachedRowSetImpl` column-label workaround). The scan ignores `ORDER BY` that appears inside parentheses (subqueries / window functions), string literals, and SQL comments. As a safeguard against regressions, lifting is skipped when the trailing clause contains a dot-qualified reference (`alias.column`) that would not resolve in the outer scope, leaving today's behavior for that case. Scope check: `wrapSQL` has three call sites in `ReadReportingServiceImpl` — `buildPreparedQuery` (user report SQL, the target of this fix) and `getSql` / `getReportType` (fixed internal queries with no `ORDER BY`, so their behavior is unchanged). ### Tests Unit tests on `wrapSQL` cover: ORDER BY preserved, no-ORDER-BY unchanged, subquery ORDER BY not lifted, window-function ORDER BY not lifted, dot-qualified skipped, string-literal ORDER BY ignored, trailing LIMIT carried. ### Note on pagination When pagination is enabled (`IS_PAGINATION_ALLOWED=true`), the wrapped SQL is passed to `paginationHelper.fetchPage`. If a user report SQL ends with a top-level `ORDER BY ... LIMIT`, the carried `LIMIT` could interact with pagination's own limiting. Flagging for reviewer confirmation on whether `PaginationHelper` appends or re-wraps; happy to skip `LIMIT`-carrying on that path if preferred. Also happy to add a DB-backed integration test if desired. --- -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
