[jira] [Resolved] (FINERACT-2872) GET /centers by meeting date GROUP BY fails on PostgreSQL

["Adam Saghy (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FINERACT-2872?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Adam Saghy resolved 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)