[Postgres] `Differ._diffUpdate()` throws error `column "foo.id" must appear in the GROUP BY clause`

[hakimio]

Description of erroneous behaviour

After annotating entity with @changelog from change-tracking plugin and trying to update it, SAP Differ._diffUpdate() throws the following error:

[cds] - error: column "customers.id" must appear in the GROUP BY clause or be used in an aggregate function
    at pg\lib\client.js:545:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.all (@cap-js\postgres\lib\PostgresService.js:175:26)
    at async PostgresService.onSELECT (@cap-js\db-service\lib\SQLService.js:136:16)
    at async next (@sap\cds\lib\srv\srv-dispatch.js:64:17)
    at async PostgresService.handle (@sap\cds\lib\srv\srv-dispatch.js:62:10)
    at async _selectDeepUpdateData (@sap\cds\libx\_runtime\common\composition\data.js:281:14)
    at async Differ._addPartialPersistentState (@sap\cds\libx\_runtime\common\utils\differ.js:59:28)
    at async Differ._diffUpdate (@sap\cds\libx\_runtime\common\utils\differ.js:64:17)
    at async PostgresService.track_changes (@cap-js\change-tracking\lib\change-log.js:447:14) {
  
  length: 176,
  severity: 'ERROR',
  code: '42803',
  detail: undefined,
  hint: undefined,
  position: '457',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_agg.c',
  line: '1444',
  routine: 'check_ungrouped_columns_walker',
  query: `SELECT to_jsonb(root.*) as _json_ FROM (SELECT root.ID as "ID",to_char(root.createdAt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as "createdAt",root.createdBy as "createdBy",to_char(root.modifiedAt, 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as "modifiedAt",root.modifiedBy as "modifiedBy",r
oot.economicId as "economicId",root.company_ID as "company_ID",root.seller_ID as "seller_ID",root.csm_ID as "csm_ID",root.totalActiveContracts as "totalActiveContracts" FROM (SELECT Customers.ID,Customers.createdAt,Customers.createdBy,Customers.modifiedAt,Customers.modifiedBy,Custome
rs.economicId,Customers.company_ID,Customers.seller_ID,Customers.csm_ID,count(activeContracts.ID) as totalActiveContracts FROM gc_crm_Customers as Customers left JOIN gc_crm_Contracts as activeContracts ON (activeContracts.customer_ID = Customers.ID) and activeContracts.status in ($1,$2) WHERE Customers.ID = $3) as root) as root\n` +
    ' ^'
}

[error] - 500 > {
  code: '42803',
  message: 'column "customers.id" must appear in the GROUP BY clause or be used in an aggregate function'
}

Detailed steps to reproduce

Schema entity definition

entity Customers : cuid, managed {
    company              : Composition of Companies @mandatory;
    contracts            : Composition of many Contracts
                               on contracts.customer = $self;
    activeContracts      : Association to many Contracts
                               on  activeContracts.customer =  $self
                               and activeContracts.status   in (
                                   'Active', 'NotStarted'
                               );
}

entity Companies : cuid, managed, oldCrmId {
    name        : String not null @mandatory;
    website     : String not null @mandatory;
    cvr         : String(50);
}

Service entity definition

entity Customers                    as
        projection on crm.Customers {
            *,
            sum(
                activeContracts.recurringValue
            ) as totalValue : Decimal(15, 2),
            (
                case
                    when
                        sum(
                            activeContracts.recurringValue
                        ) <= 5000
                    then
                        'D'
                    when
                        sum(
                            activeContracts.recurringValue
                        ) <= 10000
                    then
                        'C'
                    when
                        sum(
                            activeContracts.recurringValue
                        ) <= 15000
                    then
                        'B'
                    else
                        'A'
                end
            ) as rank       : CustomerRank
        }
        group by
            ID;

1. Annotate the entity with @changelog
2. Try to update the entity with PATCH request (PATCH Customers(2af7db0d-f7be-4c9a-ab27-0e2ad9a672aa)):

{
    "ID": "2af7db0d-f7be-4c9a-ab27-0e2ad9a672aa",
    "company": {
        "ID": "46161e86-5345-4021-b3a7-b2d8bdb8779a",
        "cvr": "326513212"
    }
}

Details about your project

Package	Version
OData version	v4
@cap-js/asyncapi	1.0.3
@cap-js/cds-types	0.9.0
@cap-js/change-trackin	1.0.7
@cap-js/db-service	1.18.0
@cap-js/openapi	1.2.1
@cap-js/postgres	1.12.0
@sap/cds	8.8.2
@sap/cds-compiler	5.8.2
@sap/cds-dk	8.8.1
@sap/cds-fiori	1.4.0
@sap/cds-foss	5.0.1
@sap/cds-mtxs	2.6.1
@sap/eslint-plugin-cds	3.2.0
Node.js	v22.13.1

[hakimio]

Duplicate of cap-js/change-tracking#153