Unexpected Behavior Chaining Any() and None() in Neo4j

Question

What I'm trying to get is nodes having certain property values for any property name (key) and not having some other values for any property.

So in short, pseudo-google query be like:

+Tom +val1 +val2 (...) -Cruise -valX -valY (...)

and Cypher query be like:

MATCH (n) WHERE (
  ANY ( p in KEYS(n) WHERE n[p] CONTAINS 'Tom' ) AND
  NONE ( p in KEYS(n) WHERE n[p] CONTAINS 'Cruise')
)
RETURN n

But the test result with movie database (:play movie graph) was just an empty list, while there are other actors named 'Tom' in the database, such as Tom Hanks. (match (n) where (any( p in KEYS(n) WHERE n[p] contains 'Tom')) return n gives [Tom Tykwer, Tom Hanks, Tom Cruise, Tom Skerritt])

So I experimented with 'om' instead of 'Tom', and this time, the result is a incomplete list of 'om's:

match (n) where (
 any( p in KEYS(n) WHERE n[p] contains 'om') and 
 none( p in Keys(n) WHERE n[p] contains 'Cruise')
 )
 return n

gives

[Romantic (genre), Naomie Harris, James Thompson, Jessica Thompson]
(No Tom's -- why?)

Also tried NOT ANY() in place of NONE() and had same results.

Where does this inconsistency come from?

Answer 1

@stdob-- offers an accurate explanation of the issue.

But there are simpler workarounds. For instance, you can use the COALESCE function() to force a NULL value to be treated as FALSE:

MATCH (n)
WHERE
  ANY ( p in KEYS(n) WHERE n[p] CONTAINS 'Tom' ) AND
  NONE( p in KEYS(n) WHERE COALESCE(n[p] CONTAINS 'Cruise', FALSE))
RETURN n

Answer 2

The problem is that nodes have properties with a type other than string. And for them, NONE-verification gives null, which gives an error for where entirely. For example, this query return nothing:

WITH {k1: 1, k2: '2'} AS test 
WHERE NONE(key IN keys(test) WHERE test[key] CONTAINS '1')
RETURN test

So in this case you need to check the type of the property. Since there is no native type-checking function, you can use the function from the APOC library:

MATCH (n) WHERE (
  ANY(p in KEYS(n) WHERE apoc.meta.cypher.type(n[p]) = 'STRING' AND n[p] CONTAINS 'Tom') AND
  NONE(p in KEYS(n) WHERE apoc.meta.cypher.type(n[p]) = 'STRING' AND n[p] CONTAINS 'Cruise')
)
RETURN n