today I ran into the following error while using the draft capabilities of CAP.
Consider the following simplified setup to reproduce the issue
db/person.cds
namespace com.sap.ibso;
using { cuid } from '@sap/cds/common';
entity Person : cuid {
name : String;
addresses : Composition of many Person2Address on addresses.person = $self;
entity Person2Address {
// the below split of properties into *_ID and separate Association field is required to workaround the following error
// { [Error: SQLITE_ERROR: no such column: person] errno: 1, code: 'SQLITE_ERROR', query: 'SELECT * FROM PersonService_Person2Addresses_drafts ALIAS_1 WHERE person = ?' }
key person : Association to Person;
key address : Association to Address;
key person_ID : UUID;
person : Association to Person on person.ID = person_ID;
key address_ID : UUID;
address : Association to Address on address.ID = address_ID;
entity Address : cuid {
street : String;
city : String;
persons : Association to many Person2Address on persons.address = $self;
using { com.sap.ibso as entities } from '../db/person';
annotate PersonService.Persons with @odata.draft.enabled;
service PersonService @(path : '/person') {
entity Persons as projection on entities.Person;
entity Person2Addresses as projection on entities.Person2Address;
entity Addresses as projection on entities.Address;
Then I execute the following sequence of http request, like a Fiori Elements UI would use to create the corresponding entity instance(s). Remark: the client.test(...) section simply takes the value of the ID property from the response and saves it in a environment variable to be used by the subsequent requests with {{person_ID}}
On the last request which should activate the draft instance(s) to "real" instance(s), I get a response with status code 500 Internal Server Error and the following response body
{"error":{"code":"500","message":"SQLITE_ERROR: near \"address_ID\": syntax error"}}
while the "cds run --in-memory"-log shows the following
[cds] - POST /person/Persons
[cds] - PATCH /person/Persons(ID=941d4010-c57c-4399-b68c-ae343d6ec260,IsActiveEntity=false)
[cds] - POST /person/Persons(ID=941d4010-c57c-4399-b68c-ae343d6ec260,IsActiveEntity=false)/addresses
[cds] - POST /person/Persons(ID=941d4010-c57c-4399-b68c-ae343d6ec260,IsActiveEntity=false)/PersonService.draftPrepare
[cds] - POST /person/Persons(ID=941d4010-c57c-4399-b68c-ae343d6ec260,IsActiveEntity=false)/PersonService.draftActivate
[2020-12-08T10:47:28.369Z | ERROR | 1580937]: { [Error: SQLITE_ERROR: near "address_ID": syntax error] errno: 1, code: 'SQLITE_ERROR', query: 'SELECT c.person_ID AS "c_person_ID", c.address_ID AS "c_address_ID", d.DraftUUID AS "d_DraftUUID", true AS "c_IsActiveEntity", filterExpand.ID AS "filterExpand_ID", true AS "filterExpand_IsActiveEntity" FROM PersonService_Person2Addresses c INNER JOIN (SELECT DISTINCT ID FROM (SELECT a.ID AS ID FROM PersonService_Persons a LEFT JOIN DRAFT_DraftAdministrativeData b ON b.DraftUUID IN ( SELECT DraftAdministrativeData_DraftUUID FROM PersonService_Persons_drafts WHERE ID = a.ID ) WHERE a.ID = ?)) filterExpand ON ( filterExpand.ID = c.person_ID ) LEFT JOIN DRAFT_DraftAdministrativeData d ON d.DraftUUID IN ( SELECT DraftAdministrativeData_DraftUUID FROM PersonService_Person2Addresses_drafts WHERE person_ID = c.person_ID address_ID = c.address_ID )' }
If I analyse the query shown in the logs, I can see, that there is a logical "and" missing in the last where-clause:
WHERE person_ID = c.person_ID address_ID = c.address_ID
I guess that this query is generated during some kind of CAP internal processing since I don't have any custom service event handler like this.before(...). Is this actually a bug in the internal sql query generation or is there an issue with my setup?
so basically, you need to set up your Person2Address entity as something like this:
entity Person2Address {
key ID: UUID
person_ID : UUID; // I am using String(36) here - don't remember why
person : Association to Person on person.ID = person_ID;
address_ID : UUID; // I am using String(36) here - don't remember why
address : Association to Address on address.ID = address_ID;
Thank you for the quick answer and the wiki link.
Since I already thought that it's related to the composite key, I found your proposed solution on my own. I consider it rather a workaround instead of a solution, that's why I wanted to ask in the community.
Composite keys on drafts would probably complicate the draft tables and the corresponding draft handling logic. But anyway I wonder why there are no composite keys possible for drafts... are there specific (technical) limitations?
For now I continue with the following solution with the additional unique constraint to avoid duplicate entries.
entity Person2Address @(assert.unique : {
person2address : [ person, address ]
}) : cuid {
person : Association to Person;
address : Association to Address;
}
