Aarti Panchal created FINERACT-2975:
---------------------------------------
Summary: DatabaseSpecificSQLGenerator.castChar truncates values to
one character on PostgreSQL, breaking interest rate chart incentive code values
Key: FINERACT-2975
URL: https://issues.apache.org/jira/browse/FINERACT-2975
Project: Apache Fineract
Issue Type: Bug
Reporter: Aarti Panchal
DatabaseSpecificSQLGenerator.castChar returns "x::CHAR" for PostgreSQL. In
PostgreSQL, CHAR without a length means CHAR(1), so the cast silently keeps
only the first character. MySQL's CAST(x AS CHAR) has no such limit, which is
why it works there.
Checked on PostgreSQL 18.3:
SELECT 123::CHAR; -- returns '1'
castChar is only used in one join, repeated in four places:
left join m_code_value code on <castChar("code.id")> = iri.attribute_value
in InterestRateChartReadServiceImpl (two queries),
InterestRateChartSlabsReadServiceImpl and
DepositAccountInterestRateChartReadPlatformServiceImpl. attribute_value is
VARCHAR(50) and holds the code value id for GENDER, CLIENT_TYPE and
CLIENT_CLASSIFICATION incentives.
On PostgreSQL code.id 12 becomes '1', so:
- an incentive pointing at a code value with id 10 or more never matches, and
attributeValueDesc is null;
- an incentive pointing at a single-digit id (say '1') matches every code value
whose id starts with 1 (1, 10-19, 100-199, ...), and the incentive is returned
once per match, so the same incentive shows up several times.
Steps to reproduce (PostgreSQL):
1. Create a code value under the Gender code and note its id (any id of 10 or
more, which is the normal case on a real system).
2. Create a fixed deposit product whose interest rate chart slab has an
incentive with attributeName GENDER (2), attributeValue = that code value id.
3. GET /v1/interestratecharts?productId=<product id> (or GET
/v1/fixeddepositproducts/<id>?associations=all).
4. The incentive comes back with attributeValueDesc missing. On MySQL / MariaDB
it has the code value name.
Expected: same as MySQL, attributeValueDesc is the code value name and each
incentive appears once.
Impact: display data in interest rate chart, chart slab, deposit product and
deposit account reads. Interest calculation uses the JPA entities, not this
query, so amounts are not affected.
Fix: cast to VARCHAR on PostgreSQL ("x::VARCHAR"), the same as groupConcat in
the same class already does. MySQL is unchanged.
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)
