Different results yielded in cypher coalesce

[priyambada_n168]

Hi,

I am not able to understand why the below query returns different results.

MATCH (service:Service)
WHERE coalesce(service.name = 'Service1Name', service.aliasValue = 'Service2Alias')
RETURN service;

Initially I have data as below
CREATE (s:Service {name: 'Service1Name'})
RETURN s;

CREATE (s:Service {aliasValue: 'Service2Alias'})
RETURN s;

The query returns both the services.

But, when name and aliasValue both the fileds are added to both the service nodes, only one service node is returned that qualifies name = 'Service1Name'.
{"name":"Service1Name","aliasValue":"Service1Alias"}
{"aliasValue":"Service2Name","name":"Service2Alias"}

As per my understanding all the Service nodes should be returned qualifying either of the conditions.

Thank you.

[Joel]

because no node that meets the criteria

service.aliasValue = 'Service2Alias'

(you assigned that value to the name property...)

{
"aliasValue":"Service2Name",
"name":"Service2Alias"
}

[andrew_bowman]

Keep in mind that coalesce() will return the first non-null value evaluated.

Also, comparisons against null values result in null. This is why when only one property was present, both nodes were returned:

On evaluation of your first node, service.name = 'Service1Name' evaluates to true, which is not a null value, so it is used, and passes your filter.

On the evaluation of your second node, service.name = 'Service1Name' evaluates to null, because service.name is null, and the comparison against a null value results in null. This falls through to the second expression: service.aliasValue = 'Service2Alias', and since service.aliasValue exists, and equals the provided value, it evaluates to true, and passes your filter.

When both nodes have both values, behavior changes because, since all properties exist, you will not get any null values, and thus no comparisons against null, and so none of the boolean expressions will evaluate to null, thus the first element of coalesce(), service.name = 'Service1Name' will always be used.

If you want the equivalent of a boolean OR expression, then do that directly:

MATCH (service:Service)
WHERE service.name = 'Service1Name' OR service.aliasValue = 'Service2Alias'
...

And if you can't be certain that the properties exist, then you can use coalesce() in each to handle nulls:

MATCH (service:Service)
WHERE coalesce(service.name = 'Service1Name', false) OR coalesce(service.aliasValue = 'Service2Alias', false)
...

You could also use coalesce() just around each property access, providing a default (maybe the empty string '') when no such name or alias value exists on the node