Aarti Panchal created FINERACT-2976:
---------------------------------------
Summary: Stretchy reports broken on PostgreSQL: currency filter,
CAST AS CHAR truncation, Happy Birthday, TxnRunningBalances
Key: FINERACT-2976
URL: https://issues.apache.org/jira/browse/FINERACT-2976
Project: Apache Fineract
Issue Type: Bug
Reporter: Aarti Panchal
Several of the stored report queries a PostgreSQL tenant runs (the copies in
0003_postgresql_specific_initial_data.xml, plus one parameter definition shared
by both databases) fail or return wrong values. Checked on develop (b94b2ddf1f)
against a real tenant database on PostgreSQL 18.3, running each report's stored
SQL the way ReadReportingServiceImpl prepares it (placeholders bound as typed
parameters, CURRENT_DATE replaced with the business date).
1. Currency filter: 22 reports fail on PostgreSQL ("operator does not exist:
character varying = bigint")
The currencyIdSelectAll parameter is registered with parameter_FormatType =
'number', but its values are currency codes and every report uses it as a
string: l.currency_code = '${currencyId}' OR '-1' = '${currencyId}'. Since the
runreports prepared-statement change (FINERACT-2624), a 'number' parameter is
bound as a Long. On PostgreSQL the default "-1" (All) is then compared with a
varchar column and the query fails. On every database a real code such as
R_currencyId=USD is rejected as not numeric.
Affected (all use_report=true, in the web app Reports menu): Portfolio at Risk,
Portfolio at Risk by Branch, Active Loans - Summary, Active Loans - Details,
Active Loans in last installment, Active Loans in last installment Summary,
Active Loans Passed Final Maturity, Active Loans Passed Final Maturity Summary,
Active Loans by Disbursal Period, Aging Summary (Arrears in Weeks), Aging
Summary (Arrears in Months), Client Loans Listing, Funds Disbursed Between
Dates Summary, Funds Disbursed Between Dates Summary by Office, Loans Awaiting
Disbursal, Loans Awaiting Disbursal Summary, Loans Awaiting Disbursal Summary
by Month, Loans Pending Approval, Obligation Met Loans Details, Obligation Met
Loans Summary, Rescheduled Loans, Written-Off Loans.
GET
/fineract-provider/api/v1/runreports/Portfolio%20at%20Risk?R_officeId=1&R_loanOfficerId=-1&R_currencyId=-1&R_fundId=-1&R_loanProductId=-1&R_loanPurposeId=-1&R_parType=1
-> ERROR: operator does not exist: character varying = bigint
2. CAST(... AS CHAR) keeps one character on PostgreSQL (6 reports)
In PostgreSQL CHAR without a length is CHAR(1), and an explicit cast to it
silently truncates (SELECT CAST(round(12.345, 2) AS CHAR) returns 1). The
PostgreSQL copies build the "Portfolio at Risk %" column with cast(round(...,
2) AS CHAR), so 5.88 % shows as "5". Same cast in Portfolio at Risk, Portfolio
at Risk by Branch, Active Loans - Summary, Active Loans in last installment
Summary, Active Loans Passed Final Maturity Summary (4 casts each, one per PAR
type) and TxnRunningBalances ("Transaction Type", so every type shows as one
letter).
Live: one active loan with 1234.50 principal overdue out of 21000.00
outstanding. With the currency filter bound as a string so the query runs,
Portfolio at Risk returns "5" for every R_parType (1 to 4). Expected 5.88
(parType 1) and 5.25 (parType 2 to 4).
3. Happy Birthday (SMS) fails on PostgreSQL, and its logic differs from MySQL
- CEIL(EXTRACT(DAY FROM (CURRENT_DATE - c.date_of_birth))/365): date minus date
is an integer in PostgreSQL, so the query fails: "function
pg_catalog.extract(unknown, integer) does not exist". This happens both through
/runreports and in the SMS campaign job.
- DATE_TRUNC('day', dob) = DATE_TRUNC('day', NOW()) compares the whole date
including the year, so even after the error is fixed it only matches clients
born today. MySQL compares DAY() and MONTH().
- The loan officer filter is hard-coded: (COALESCE(c.staff_id, -10) = -1 OR
'-1' = -1) where MySQL has ${loanOfficerId}.
- The column aliases are unquoted (c.mobile_no AS mobileNo), so PostgreSQL
returns them lower case (mobileno), while
SmsCampaignWritePlatformServiceJpaImpl reads entry.get("mobileNo").
4. TxnRunningBalances fails on PostgreSQL (use_report=false, but runnable via
/runreports)
- DATE ${startDate}: the placeholder becomes ?, and DATE $1 is a syntax error
in PostgreSQL (a typed literal needs a string constant). Error: syntax error at
or near "$1".
- Loan officer hard-coded to 9: (COALESCE(l.loan_officer_id, -10) = 9 or '-1' =
9) where MySQL has ${loanOfficerId}.
- The opening balance row sums interest_portion_derived of disbursements as
"Outstanding Principal"; MySQL (and the transaction rows of the same PostgreSQL
query) use amount for disbursements and minus principal for the rest.
- as char on "Transaction Type" (item 2).
Expected: the reports run on PostgreSQL and return the same values as on MySQL
/ MariaDB.
Not covered by any test: no integration test runs any of these reports, which
is how they survived.
Fix (one PR): a tenant changelog part with
- one PostgreSQL-only changeset replacing AS CHAR) with AS VARCHAR) in those 6
reports (nothing else in the SQL changes),
- one changeset (all databases) setting currencyIdSelectAll's
parameter_FormatType to 'string',
- PostgreSQL-only rewrites of Happy Birthday and TxnRunningBalances,
- an integration test that runs every enabled table report, checks the
"Portfolio at Risk %" format and runs Happy Birthday. CI runs it on PostgreSQL,
MySQL and MariaDB.
Left out, for a follow-up:
- The other 24 PostgreSQL SMS / Email report copies also use unquoted camelCase
aliases (AS mobileNo), which PostgreSQL folds to lower case, so campaigns built
on them likely get no mobile number. Same pattern in all of them, so better as
its own change (not checked end to end through a campaign).
- The five hidden staff dashboard reports (FieldAgentStats, CoordinatorStats,
BranchManagerStats, ProgramDirectorStats, ProgramStats) do integer division on
PostgreSQL, but they also join m_loan_paid_in_advance, which
0014_remove_unused_jobs.xml dropped, so they cannot run on any database.
Probably candidates for removal.
- TxnRunningBalances on PostgreSQL still does not compute running totals the
way the MySQL copy does with session variables (each row shows its own
transaction). That needs window functions; worth doing only if anyone uses this
hidden report.
Found while auditing hand-written SQL for MySQL-only constructs after
FINERACT-1821 and FINERACT-2973.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
