Channels / #fineract / 2026 / 10 / 21

#fineract 2026-10-21

Wed 06:06Aarti Panchal
Hi @Adam Saghy, hi @Arnold Galovics,

After the two PostgreSQL fixes I opened (FINERACT-1821 cashier TIME(): https://github.com/apache/fineract/pull/6660 and FINERACT-2973 search alias: https://github.com/apache/fineract/pull/6701), I went looking for more of the same. I scanned all the hand-written SQL in src/main and replayed the Liquibase changelog to get the report SQL a PostgreSQL tenant actually runs.

The Java side is in good shape, almost everything goes through DatabaseSpecificSQLGenerator. The problems are mostly in the PostgreSQL copies of the stretchy reports from 0003_postgresql_specific_initial_data.xml, plus one in the helper itself:

  1. CAST(... AS CHAR) means char(1) on PostgreSQL, so "Portfolio at Risk %" shows only the first digit (12.34 becomes 1). It is in 5 core reports (Portfolio at Risk, PAR by Branch, Active Loans Summary and two maturity summaries), no error, just a wrong number.
  2. castChar() in DatabaseSpecificSQLGenerator returns x::CHAR for PostgreSQL, same truncation. It breaks the code value join for interest rate chart incentives (4 call sites).
  3. The Happy Birthday SMS report fails on PostgreSQL (EXTRACT on an integer), and its port also compares the full date with the year and drops the loanOfficerId filter.
  4. Smaller ones: TxnRunningBalances (DATE ? syntax error, loan officer hard-coded to 9), integer division in the staff dashboard reports, and m_tenant_oidc_config.enabled is SMALLINT on PostgreSQL while the repository binds a boolean.
I confirmed the casts, the EXTRACT error and the SMALLINT column on a local PostgreSQL 18 Fineract. None of these have a ticket yet. Two questions before I file anything:

  • Would you like one umbrella ticket with a PR per area (reports, the castChar helper, OIDC), or separate tickets? For the report fixes, do you prefer full report_sql rewrites in a postgresql-context changeset like FINERACT-2436, or one regexp_replace changeset for the AS CHAR cases?
  • None of these is covered by a test that runs a report. Would you accept a smoke integration test that runs every use_report=true Table report on the PG/MySQL/MariaDB shards and checks it returns headers? It would have caught most of these, and I think it is a better guard than trying to lint SQL strings.
I would start with the PAR reports and castChar unless you think something else matters more.
Wed 08:23Adam Saghy
thank you for the thorough debugging and review!

Go ahead with the fix, i dont mind if the report fixes are done in one umbrella PR.

Regarding postgres specific syntax or generic: can you please share some PRO vs CON? so we can make a better judgment call here
Wed 08:42Aarti Panchal
Thanks Adam! Here's how I see the options for the report fixes. The cast to char is there because the CASE mixes numbers with the text 'invalid PAR Type', so every branch has to be a string. MySQL's CAST(x AS CHAR) has no length; on PostgreSQL it's char(1).

  1. PostgreSQL-only, targeted: one changeset with context="postgresql" that does UPDATE stretchy_report SET report_sql = REPLACE(report_sql, 'AS CHAR)', 'AS VARCHAR)') WHERE report_name IN (the 5 reports + TxnRunningBalances)
  • small diff, easy to review, zero risk for MySQL/MariaDB
  • only touches the cast, so a tenant who customised one of these reports keeps their other edits
  • relies on the exact text; if a tenant changed that part it silently doesn't apply
  • keeps the two copies (0002 vs 0003) apart, which is how these bugs got in
  1. PostgreSQL-only, full rewrite (like FINERACT-2436): replace the whole report_sql in a postgresql-context changeset
  • explicit, the final SQL is right there in the changeset
  • same pattern the project already accepted
  • long XML diffs, harder to review
  • overwrites any tenant customisation of these reports
  • still two copies to keep in sync
  1. Generic, one SQL for all DBs
  • one source of truth, all three CI shards test the same SQL
  • not a small change: the MySQL copies use backtick identifiers ("x.`Principal Overdue`"), PostgreSQL needs double quotes, so the inner aliases would need renaming to plain names first
  • MySQL doesn't accept CAST(... AS VARCHAR), and CAST(... AS CHAR(20)) pads with spaces on PostgreSQL, so we'd need something like CONCAT(round(...), '') or returning NULL instead of 'invalid PAR Type', which changes output on MySQL
  • touches reports that work today on most installs (MySQL), so more regression risk
My suggestion: 1 for the AS CHAR cases, 2 only where the logic itself has to change (Happy Birthday, TxnRunningBalances), all in the one umbrella PR, plus the report smoke test so it runs on all three shards. If you'd rather start converging on generic SQL, I'd do that as a separate follow-up per report.

For castChar I'll file the ticket and open the PR today.
Wed 09:45Aarti Panchal
Quick update before I file: while testing the report fixes on a real PostgreSQL tenant I found a bigger one. The currencyIdSelectAll parameter is registered with parameter_FormatType 'number', but its values are currency codes (the reports do l.currency_code = '${currencyId}'). Since FINERACT-2624 a number parameter is bound as a Long, so with the default "-1" (All) 22 of the 34 enabled table reports fail on PostgreSQL with "operator does not exist: character varying = bigint", including Portfolio at Risk, all Active Loans and Aging reports. On every database a real code like R_currencyId=USD is rejected as not numeric. The fix is one row: set its format to 'string'.

I've put it in the same umbrella PR as its own changeset (all databases), next to the PostgreSQL-only ones, so it's easy to split out if you'd rather have it as a separate ticket. OK to keep it together?
Wed 09:47Adam Saghy
Sound like a good plan
👍 1
Wed 17:06Dharani
@Dharani has joined the channel