Aarti Panchal created FINERACT-2973:
---------------------------------------
Summary: 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
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)
