Farooq Ayoade created FINERACT-2713:
---------------------------------------
Summary: Cascaded report-parameter lookups fail on PostgreSQL with
"operator does not exist: bigint = character varying" since FINERACT-2624
Key: FINERACT-2713
URL: https://issues.apache.org/jira/browse/FINERACT-2713
Project: Apache Fineract
Issue Type: Bug
Components: Reports
Reporter: Farooq Ayoade
---Since FINERACT-2624 replaced string interpolation of report parameters with
JDBC bind variables, a parameter that appears in a report's SQL but is not one
of that report's own declared parameters — i.e. a {}cascaded{} lookup parameter
— is
bound as a {{String}}. On PostgreSQL, comparing a {{bigint}} column to a
bound {{character varying}} throws {{operator does not exist: bigint =
character varying}}, so the lookup fails.
The stock {{loanOfficerIdSelectAll}} option lookup is the clearest case: its
SQL filters {{... and o.id = ${officeId}}}, where {{officeId}} is supplied by
the parent parameter, not by {{loanOfficerIdSelectAll}} itself. The Loan
Officer dropdown of
every report that cascades off office is therefore empty on PostgreSQL.
h3. Steps to reproduce
Run Fineract on PostgreSQL (MySQL/MariaDB masks the bug — it coerces the
types).
Ensure at least one staff member with {{is_loan_officer = true}} exists under
the head office.
Call the cascaded option lookup exactly as the UI does when populating the
Loan Officer dropdown:
{noformat}
GET
/fineract-provider/api/v1/runreports/loanOfficerIdSelectAll?parameterType=true&genericResultSet=true&R_officeId=1
{noformat}
h3. Actual
HTTP 403; server log:
{noformat}
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar
[select x.* from ((select lo.id, lo.display_name AS name ... where
lo.is_loan_officer = true and o.id = ?) ...) x]
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist:
bigint = character varying
{noformat}
Every report using this lookup shows an empty Loan Officer dropdown.
h3. Expected
The lookup returns the loan officers under office 1 (as it does on
MySQL/MariaDB, and as it did before FINERACT-2624).
h3. Root cause
{{ReadReportingServiceImpl.buildPreparedQuery}} binds each {{${param}}} via
{{castParamValue(value, paramFormatTypes.get(param))}}. {{paramFormatTypes}} is
loaded for the report being run ({{loanOfficerIdSelectAll}}), whose own
parameters do not
include {{officeId}}; the cascaded {{officeId}} placeholder therefore
resolves to a {{null}} format type and {{castParamValue}} returns the raw
{{String}}. {{o.id = ?}} then compares {{bigint}} to a {{varchar}} bind —
accepted by MySQL, rejected
by PostgreSQL.
h3. Suggested fix
When the format type is unknown, infer a numeric bind for a plain integer
value so strict engines compare correctly; leave currency codes, identifiers
with leading zeros and free text as strings:
{code:java}
if ((formatType == null || formatType.isBlank()) &&
UNTYPED_INTEGER.matcher(value).matches()) {
return Long.parseLong(value); // UNTYPED_INTEGER = -?(0|[1-9]\d*)
}
return value;
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
