Deep UPDATE loses track of relationships

[BobdenOs]

Description of erroneous behaviour

When doing a deep UPDATE when using an ambiguous WHERE clause it creates children without parents.

Detailed steps to reproduce

await INSERT([{ ID: 1 }, { ID: 2 }]).into(Genres)
const changes = await UPDATE(Genres)
 .where(`ID > `, { val: 0 })
 .with({
  children: [{ ID: 3 }, { ID: 4 }, { ID: 5 }]
})
expect(changes | 0).to.be.eq(2)

const finalState = await CQL`SELECT *,children{*} from ${Genres}`
const result = [
  { "ID": 1, "children": [] }, // Does not have children
  { "ID": 2, "children": [] }, // Does not have children
  // All children are created, but don't have any parent
  { "ID": 3, "children": [] },
  { "ID": 4, "children": [] },
  { "ID": 5, "children": [] },
]

Details about your project

package	version
@sap/cds	7.5.x
@cap-js/db-service	all

[johannes-vogel]

I think that this is acceptable. If there's no clear value to be set, this must be set by the application.
We could discuss whether this should cause an error instead.

[BobdenOs]

As deep updates like this can only be done to compositions it does not make sense to allow for updates that have / could result in multiple parents.

// allowed as it guarantees a single parent
UDPATE(Genres, {ID: 1}).with({children:[...]})
// Could be allowed as the where clause strictly checks all keys
UPDATE(Genres).where(`ID = `, 1).with({children:[...]})
// Could be rejected as there is the chance it will match multiple parents
UPDATE(Genres).with({children:[...]})
UPDATE(Genres).where(`ID != `, 0).with({children:[...]})
... etc

[danjoa]

These work?:

UPDATE(Genres) .where({ID:1}) .with({ children: [{ ID: 3 }, { ID: 4 }, { ID: 5 }] })
UPDATE(Genres,1) .with({ children: [{ ID: 3 }, { ID: 4 }, { ID: 5 }] })

[patricebender]

is this still relevant? We reworked some of the deep logic in the past weeks @David-Kunz ?