[jira] [Created] (FINERACT-2973) Ad-hoc loan search (POST /search/advance) fails on PostgreSQL because of a single-quoted column alias

["Aarti Panchal (Jira)" <ji...@apache.org>]

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)