cancel
Showing results for 
Search instead for 
Did you mean: 

IN clause value order matters when deleting nodes

paul_clark
Node

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

1 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;

View solution in original post

2 REPLIES 2

glilienfield
Ninja
Ninja

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

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;
Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.