[
https://issues.apache.org/jira/browse/FINERACT-2622?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18092363#comment-18092363
]
Berk Emir commented on FINERACT-2622:
-------------------------------------
Hi, I'd like to work on this. Confirmed the root cause at
GenericDataServiceImpl#wrapSQL: the report SQL is wrapped as `select x.* from
(<sql>) x`, and a derived table's ORDER BY isn't guaranteed to be preserved by
the outer query, so the requested ordering is lost. Of the three wrapSQL call
sites, only buildPreparedQuery in ReadReportingServiceImpl passes user report
SQL (the other two are fixed internal queries with no ORDER BY), so the fix
fits inside wrapSQL.
Since the wrapping itself is still needed (the CachedRowSetImpl column-label
workaround), my proposed approach is: detect a top-level trailing ORDER BY
(ignoring any ORDER BY nested in parentheses, string literals, comments or
window functions), including any trailing LIMIT/OFFSET, and re-apply it on the
outer wrapper. The outer ORDER BY resolves against the inner output columns
exposed by `x.*`. To avoid regressions I'd conservatively skip lifting when the
clause contains a dot-qualified reference (alias.column) that wouldn't resolve
in the outer scope, leaving today's behavior for that case. I'd cover it with a
unit test on wrapSQL (ORDER BY preserved, no-ORDER-BY unchanged,
subquery/window ORDER BY not lifted, LIMIT carried).
Does this approach look right to you, or would you prefer handling the ordering
differently? Could you assign this to me?
> Values not sorted correctly in report output
> --------------------------------------------
>
> Key: FINERACT-2622
> URL: https://issues.apache.org/jira/browse/FINERACT-2622
> Project: Apache Fineract
> Issue Type: Bug
> Reporter: Hysnije
> Priority: Major
> Attachments: image-2026-05-28-14-14-09-703.png, image.png
>
>
> h3. Steps to reproduce
> # Navigate to *System* > *Admin* > *Manage Reports*
> # Create a report for any entity with an SQL script which contains an
> "{*}Order By{*}" statement e.g.
> {code:java}
> SELECT account_no FROM m_loan ORDER BY account_no DESC{code}
> # Navigate to *Reports* and select the newly created report
> # Click "{*}Run Report"{*}
> h3. Actual result
> Loan account numbers are *not* displayed in correct descending numerical order
> !image-2026-05-28-14-14-09-703.png!
> h3. Expected result
> Loan account numbers should always be displayed in {*}correct descending
> order{*}, respecting the "ORDER BY account_no DESC" SQL clause.
> h3. Additional information
> The issue is that the SQL is wrapped into a subselect
> [https://github.com/apache/fineract/blob/develop/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java#L202.|https://github.com/apache/fineract/blob/develop/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/GenericDataServiceImpl.java#L202]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
