[
https://issues.apache.org/jira/browse/FINERACT-2973?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18129328#comment-18129328
]
Aarti Panchal commented on FINERACT-2973:
-----------------------------------------
PR: https://github.com/apache/fineract/pull/6701
> Ad-hoc loan search (POST /search/advance) fails on PostgreSQL because of a
> single-quoted column alias
> -----------------------------------------------------------------------------------------------------
>
> Key: FINERACT-2973
> URL: https://issues.apache.org/jira/browse/FINERACT-2973
> Project: Apache Fineract
> Issue Type: Bug
> Reporter: Aarti Panchal
> Priority: Major
>
> POST /v1/search/advance (the ad-hoc loan search used by the Fund Mapping page
> in the web apps) returns HTTP 500 when Fineract runs on PostgreSQL.
> The query built in SearchReadServiceImpl.AdHocQuerySearchMapper#schema starts
> with:
> Select a.name as officeName, a.Product as productName, a.cnt as 'count',
> ...
> PostgreSQL does not accept a single-quoted string as a column alias:
> ERROR: syntax error at or near "'count'"
> MySQL and MariaDB accept it, which is probably why it went unnoticed.
> Steps to reproduce:
> 1. Run Fineract develop on PostgreSQL with at least one loan.
> 2. POST /fineract-provider/api/v1/search/advance with body
> \{"entities":["loans"],"loanStatus":["all"],"loanDateOption":"disbursalDate","loanFromDate":"2024-01-01","loanToDate":"2026-12-31","dateFormat":"yyyy-MM-dd","locale":"en"}
> 3. Response is 500 with the syntax error above in the log.
> Expected: the matching loans grouped by office and product, as on MySQL.
> Fix: alias the column without quotes (a.cnt as count). count is not a
> reserved word in PostgreSQL, MySQL or MariaDB, and the row mapper already
> reads the column as "count".
> Found while building the Fund Mapping page for the Mifos X React web app
> (openMF/mifos-x-web-app-react#190)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
