[
https://issues.apache.org/jira/browse/FINERACT-2713?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Adam Saghy updated FINERACT-2713:
---------------------------------
Fix Version/s: 1.16.0
> 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
> Assignee: Farooq Ayoade
> Priority: Major
> Fix For: 1.16.0
>
>
> ---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)
