Associations with sql functions incorrect results

[pavlzk]

Description of erroneous behaviour

Getting incorrect result on expanded associations defined with sql functions in on clause

Database schema defined as follows:

namespace db;

entity Posts {
  key ID: Integer;
  name: String;
  similar: Association to many Posts on name = similar.name;
  iSimilar: Association to many Posts on UPPER(name) = UPPER(iSimilar.name);
}

Service definition:

using {db} from '../db/schema';

service PostsService {
  entity Posts as projection on db.Posts
}

CSV data:

ID,name
1,some
2,some
3,Some
4,some1
5,some2
6,Some4

Odata request: /odata/v4/posts/Posts(1)?$expand=similar,iSimilar

Actual result:

{
  "@odata.context": "$metadata#Posts/$entity",
  "ID": 1,
  "name": "some",
  "similar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    }
  ],
  "iSimilar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    },
    {
      "ID": 3,
      "name": "Some"
    },
    {
      "ID": 4,
      "name": "some1"
    },
    {
      "ID": 5,
      "name": "some2"
    },
    {
      "ID": 6,
      "name": "Some4"
    }
  ]
}

Expected result:

{
  "@odata.context": "$metadata#Posts/$entity",
  "ID": 1,
  "name": "some",
  "similar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    }
  ],
  "iSimilar": [
    {
      "ID": 1,
      "name": "some"
    },
    {
      "ID": 2,
      "name": "some"
    },
    {
      "ID": 3,
      "name": "Some"
    }
  ]
}

Details about your project

@cap-js/asyncapi: 1.0.2
@cap-js/cds-types: 0.6.5
@cap-js/openapi: 1.0.4
@cap-js/sqlite: 1.7.3
@sap/cds: 8.1.1
@sap/cds-compiler: 5.1.2
@sap/cds-dk (global): 8.0.3
@sap/cds-fiori: 1.2.7
@sap/cds-foss: 5.0.1
@sap/cds-mtxs: 2.0.3
@sap/eslint-plugin-cds: 3.0.4
Node.js: v20.10.0