Neo4j, Cypher, Order of filtering matters, why?

Question

I'm wondering why these two queries are giving me different results.

START keanu=node(1) 
MATCH keanu -[r:ACTED_IN]-> (movieToDelete)
OPTIONAL MATCH movieToDelete -[relationshipsToDelete]- ()
WHERE "Neo" IN r.roles
RETURN movieToDelete, relationshipsToDelete;

And

START keanu=node(1) 
MATCH keanu -[r:ACTED_IN]-> (movieToDelete)
WHERE "Neo" IN r.roles
OPTIONAL MATCH movieToDelete -[relationshipsToDelete]- ()
RETURN movieToDelete, relationshipsToDelete;

In the first query, I filter for Neo after the optional match and in the second, I filter for Neo before the optional match. The first query actually returns a row where the r-variable doesn't have a Neo in the roles. However, there is a r-variable, and it does contain a property called roles.

František Hartman František Hartman · Accepted Answer · 2014-07-01T14:33:51

WHERE is always part of previous (OPTIONAL) MATCH clause.

from docs:

In the case of multiple (OPTIONAL) MATCH clauses, the predicate in WHERE is always a part of the patterns in the directly preceding MATCH. Both results and performance may be impacted if the WHERE is put inside the wrong MATCH clause.

http://docs.neo4j.org/chunked/stable/query-where.html

EDIT: If you try this on the sample movie data in neo4j browser then change the result to table view it might be clearer what the cypher query actually returns:

The first query will take keanu, all movies he played in, optionally match other relationships and filter these optionaly matched relationships (it won't filter keanu's movies)

The second query will take keanu, match all his movies with role Neo, and on this filtered set of movies it will do the optional match

So the first query result contains the same as second, plus movies where keanu acts in, but is not in role Neo

Neo4j, Cypher, Order of filtering matters, why?

1 Answers