IN clause value order matters when deleting nodes

[paul_clark]

I'm trying to delete all note nodes that have a relationship to a particular set of services:

match (n:note)<--(s:service) where s.serviceid in ["a", "b"] detach delete n

My database contains a single note with a relationship to service "b" node. There is no service "a" node in my database.

I'd expect this query to delete the note related to service "b". However it does not.

If i change the order of the IN clause values, then it works:

match (n:note)<--(s:service) where s.serviceid in ["b", "a"] detach delete n

Is this expected behaviour? If so, how do i change the statement to behave as i'd expect?

This behaviour only seems to apply to deletes, if i return the note nodes then it works as expected.

I'm using Neo4j 4.3.1 docker, web interface

[glilienfield]

I could not reproduce this behavior using 4.4.5 nor 4.1.3 enterprise in Desktop.

[paul_clark · Accepted solution]

Thanks for trying, I've created a minimal script to reproduce the issue. In creating the script, i've found two extra requirements:

• a unique constraint on serviceid
• version 4.3.1 (i can't reproduce on 4.4.6)

Looking at the change history i can see it was fixed in 4.3.6:
"Fixes a bug in node index seek on a unique property in write queries. For example MATCH (n:Node) WHERE n.unique IN ['does not exist', 'exists'] DELETE DETACH n would fail to delete nodes if there is a unique constraint on unique and at least one predicate does not match any nodes."

CREATE CONSTRAINT service_serviceid ON (s:service) ASSERT s.serviceid IS UNIQUE;

CREATE (n:note {note: 'Note 1'});
CREATE (n:service {serviceid: 'b'});

MATCH (n:note), (s:service)
CREATE (s)-[r:servicenote]->(n);

MATCH (n:note)<--(s:service) 
WHERE s.serviceid in ['a', 'b'] 
DETACH DELETE n;