Foo Developer created FINERACT-2872:
---------------------------------------
Summary: GET /centers by meeting date GROUP BY fails on PostgreSQL
Key: FINERACT-2872
URL: https://issues.apache.org/jira/browse/FINERACT-2872
Project: Apache Fineract
Issue Type: Bug
Components: Groups
Reporter: Foo Developer
Assignee: Foo Developer
h3. Summary
On PostgreSQL, listing the centers that have a meeting on a given date (the
lookup behind the productive collection sheet) fails with HTTP 500. The query
built by {{CenterReadPlatformServiceImpl.retriveAllCentersByMeetingDate}}
groups by columns that don't determine the selected ones, which MySQL/MariaDB
tolerate and PostgreSQL rejects.
h3. Steps to reproduce (stock Fineract, PostgreSQL)
{noformat}
GET
/fineract-provider/api/v1/centers?officeId=1&staffId=1&meetingDate=22%20September%202026&dateFormat=dd%20MMMM%20yyyy&locale=en
{noformat}
No particular data is needed: PostgreSQL rejects the statement when it is
parsed.
h3. Expected
HTTP 200 with the list of centers (empty when none meet on that date).
h3. Actual
HTTP 500:
{noformat}
ERROR: column "ce.id" must appear in the GROUP BY clause or be used in an
aggregate function
{noformat}
h3. Root cause
{{CenterCalendarDataMapper}} selects the center ({{ce.id}},
{{ce.display_name}}), the child group ({{g.office_id}}, {{g.staff_id}}, ...)
and the staff name ({{s.display_name}}) next to {{sum(...)}} aggregates. The
{{staffId}} branch of {{retriveAllCentersByMeetingDate}} groups by {{c.id,
ci.id, g.account_no, g.external_id, g.status_enum, g.activation_date,
g.hierarchy}}. None of {{ce.id}}, {{ce.display_name}}, {{g.office_id}},
{{g.staff_id}} or {{s.display_name}} is in that list or functionally dependent
on it: PostgreSQL only infers dependency from a table's own primary key in the
{{GROUP BY}}. The branch without {{staffId}} has no {{GROUP BY}} at all. That
branch is currently unreachable, because {{validateForGenerateCollectionSheet}}
requires {{staffId}}.
h3. Proposed fix
Add {{ce.id, ce.display_name, g.office_id, g.staff_id, s.display_name}} to the
{{GROUP BY}} of the {{staffId}} branch, and give the other branch the same
{{GROUP BY}}. The result rows don't change: the existing grouping is already
one row per child group ({{g.hierarchy}} contains the group's own id), and each
added column has a single value per child group (its center, office, staff).
h3. Tests
The fix adds an integration test calling {{GET /v1/centers}} with {{officeId}},
{{staffId}} and {{meetingDate}}. It expects a 200 and fails on {{develop}}
against PostgreSQL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
