Cypher query with chaining does not propagate

Question

In the following cypher query, whenever the $dlt parameter is false, the query never continues beyond the DETACH DELETE statement:

MATCH (person:Person {id: $id})
SET person.matched = (CASE person.secret WHEN $secret THEN 1 ELSE 0 END)
WITH person WHERE person.matched = 1 AND $dlt = true
MATCH (person)<-[:UsedBy]-(dev:Device)
DETACH DELETE dev
WITH person WHERE person.matched = 1
MERGE (person)<-[r:UsedBy {assignedDate: dateTime()}]-(device:Device {id: 'efgh', firebaseToken: 'jjjj8888'})
WITH person, person.matched as matched
REMOVE person.matched
RETURN
CASE matched
WHEN 0 
THEN null
ELSE person END AS Person

The idea is that all Device nodes (and connecting edges) needs to be removed only when the $dlt is true. However, regardless of $dlt (and this is what is not happening) - the subsequent parts must continue (adding a new Device node with a connecting edge, deleting the temporary matched property from person and returning based on matched value).

Btw, I'm running this query from a Jupyter Notebook against an AWS Neptune DB, with the %%oc magic command on top. As this is just for testing, I am not really using parameters (e.g. $dlt) in the Jupyter Notebook, but rather hard-coding some values.

What am I missing?

Answer 1

[UPDATED]

openCypher/Neptune answer

Since openCypher is a limited version of Cypher, the simplest thing would be to split this particular query in two: do the optional deletion in one query and then the optional merge in another. If the 2 queries should be done together atomically, you can use a Mutation Bolt transaction query, and that transaction code can return the appropriate value after it performs both individual queries.

Or you can explore clever ways to use OPTIONAL MATCH, FOREACH, and so forth. This question and its answer may be instructive.

Cypher answer

You can use CALL subqueries to do conditional processing that does not abort the rest of the query.

For example, the following version of your query might work for you. It uses "unit subqueries". A unit subquery does not return anything and does not affect the rows being processed by the enclosing query.

Note that I also simplified your query by not bothering to set and then remove the temporary matched property on each Person, which is wasteful of time and resources. Also, there was no reason to treat booleans as integers.

MATCH (person:Person {id: $id})
WITH person, (person.secret = $secret) AS matched
CALL {
  WITH person, matched
  WITH person, matched
  WHERE matched AND $dlt
  MATCH (person)<-[:UsedBy]-(dev:Device)
  DETACH DELETE dev
}
CALL {
  WITH person, matched
  WITH person, matched
  WHERE matched
  MERGE (person)<-[r:UsedBy {assignedDate: dateTime()}]-(device:Device {id: 'efgh', firebaseToken: 'jjjj8888'})
}
RETURN (CASE WHEN matched THEN null ELSE person END) AS Person

If/Else Processing

As a bonus, the following is an simple example of how to use post-union processing to do "if/else" processing that does not abort the rest of the enclosing query. In this example, the subquery is not a "unit subquery" and returns a type variable that is visible to the enclosing query.

UNWIND [1,2,3,4,5] AS x
CALL {
    WITH x
    WITH x
    WHERE x % 2 = 0
    RETURN 'even' AS type
  UNION
    WITH x
    WITH x
    WHERE x % 2 <> 0
    RETURN 'odd' AS type
}
WITH x, type, x^2 AS squared //arbitrary post-subquery processing
RETURN x, type, squared
ORDER BY x

Answer 2

Based on the discussions and some thinking about how OPTIONAL MATCH can help along with some query re-ordering, here is a version that should work on Neptune. In this case, if there is an OPTIONAL MATCH result and $dlt is TRUE, the dev will get deleted. If there is no match (including if $dlt is FALSE) then dev should be null and therefore nothing will get deleted. The rest of the query after the DELETE line should continue to the end either way.

MATCH (person:Person {id: $id})
SET person.matched = (CASE person.secret WHEN $secret THEN 1 ELSE 0 END)
WITH person WHERE person.matched = 1
OPTIONAL MATCH (person)<-[:UsedBy]-(dev:Device)
WHERE $dlt = true
DETACH DELETE dev
WITH person WHERE person.matched = 1
MERGE (person)<-[r:UsedBy {assignedDate: dateTime()}]-(device:Device {id: 'efgh', firebaseToken: 'jjjj8888'})
WITH person, person.matched as matched
REMOVE person.matched
RETURN
CASE matched
WHEN 0 
THEN null
ELSE person END AS Person