[
https://issues.apache.org/jira/browse/FINERACT-2872?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aman Mittal closed FINERACT-2872.
---------------------------------
Resolution: Fixed
> 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
> Priority: Major
> Labels: beginner, beginner-friendly
> Fix For: 1.16.0
>
>
> 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)
