[
https://issues.apache.org/jira/browse/FINERACT-2976?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18129579#comment-18129579
]
Aarti Panchal commented on FINERACT-2976:
-----------------------------------------
The new smoke test also found three reports that fail on MySQL 8 (they run on
MariaDB and PostgreSQL). MySQL 8 enables ONLY_FULL_GROUP_BY by default, and
these select columns that are not in their GROUP BY:
* Loans Awaiting Disbursal Summary by Month: ounder.name, cur.display_symbol
and monthname(expected_disbursedon_date) not grouped
* Obligation Met Loans Summary: ounder.name and cur.display_symbol not grouped
* Active Loans in last installment Summary: the per-branch totals select the
currency but group by branch only, so on MariaDB loans in different currencies
were summed into one row with an arbitrary currency label
They are broken on develop too; the currency parameter change is unrelated (the
error is raised when MySQL resolves the query, before any parameter is used). I
added a MySQL-context changeset to the same PR that adds the missing columns to
each GROUP BY (branch + currency for the last one, like the PostgreSQL copy).
The PostgreSQL copies already group correctly.
PR: https://github.com/apache/fineract/pull/6706
> 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
> Priority: Major
>
> 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)
