Ambiguous column name with path expression in where condition

[klaus_kopecz]

Hello CAP team,

I'm getting the error "SQLITE_ERROR: ambiguous column name: ID", when following an association witthin a where-condition. Take the following entities as an example:

entity db.Products  {
  key ID       : Integer;
      name     : String(100);
      supplier : Association to db.Suppliers;
}
entity db.Suppliers {
  key ID       : Integer;
      name     : String(100);
      products : Association to many db.Products 
                on products.supplier = $self;
}

Now consider the following three variants of a service:

// Variant 1 --> BAD
service CQLService {
    entity Products as select from db.Products where supplier.name ='Vendor 101';
}
// Variant 2 --> BAD
service CQLService {
    entity Products as projection on db.Products where supplier.name ='Vendor 101';
}
// Variant 3  --> GOOD!
service CQLService {
    entity Products as select * from db.Products where supplier.name ='Vendor 101';
}

The query "/cql/Products" is only working as expected for variant 3 (using the *-operator). The first two variants result in the error "SQLITE_ERROR: ambiguous column name: ID". This happens for SQlite as well as for HANA.

Works as designed? Or is this an error? The documentation is a little vague on the effect of the *-operator ...

Thanks and best regards!

>> cds -v
@sap/cds: 4.2.4
@sap/cds-compiler: 1.43.0
@sap/cds-dk: 3.1.2
@sap/cds-foss: 2.1.0
@sap/cds-reflect: 2.13.1
@sap/cds-runtime: 2.5.2
Node.js: v12.18.2

[079695 · Accepted solution]

Hi Klaus, this will be fixed with the upcoming CAP release. Stay tuned!