Upgrade from 4.0 to 4.1 causing errors when using WITH with COLLECT

[kearnsw]

I have a query where I get the properties of a series of relationships between users on the network and messages for downstream processing. This query worked in 4.0, but now is raising an error in 4.1:

Neo.DatabaseError.Statement.ExecutionFailed in Neo4j Desktop and Java Driver

Query

MATCH (o:Organization {id: "1787106602"})-[:from]-(m:Message {id: "4474437524"}) 
OPTIONAL MATCH (m)-[v:viewed]-(u:User) 
OPTIONAL MATCH (m)-[li:likes]-(u) 
WITH m as msg, COLLECT(li.date) as likes, COLLECT(v.date) as views, o 
OPTIONAL MATCH (uu:User)-[f:follows]-(:Group)-[:to]-(msg) WHERE uu.tenantId <> "1787106602" 
RETURN msg, likes as likeDates, views as viewDates

I've narrowed it down to the WITH clause particularly the COLLECT portion of that clause. Is there a better way to construct this query and/or a reason why this no longer works. I checked the changelog and migration guide but wasn't able to locate an answer.

Thank you!

[Joel]

This appears to be a fix or an introduced bug, I'd argue the later because this behavior makes life more difficult.

The error is thrown due to trying to reference a property on a null relationship

There may be other approaches, but the first thought to get around the issue (just for now?) is to check if the relationship is null and either create a relationship map with null properties, or at the property level just return a null..

HACK FIX1: return with a null property as needed

(CASE li
WHEN li = null THEN {date: null}
ELSE li
END)

HACK FIX2: at the property level, everywhere you reference a property on a relationship which might be null, add a hackfix (cypher version of ternary...)

(CASE li
WHEN li = null THEN null
ELSE li.date
END)

[kearnsw]

Thanks, Joel. How would I integrate the case check with the collect function?

[Joel · Accepted solution]

I gave (I believe) a working example for li.date which is throwing an error, but to fix you need to do that everywhere you reference a property of a potentially null relationship drop the second case statement in and adjust as needed..

Something like this?

MATCH (o:Organization {id: "1787106602"})-[:from]-(m:Message {id: "4474437524"}) 
OPTIONAL MATCH (m)-[v:viewed]-(u:User) 
OPTIONAL MATCH (m)-[li:likes]-(u) 
WITH m as msg, COLLECT(
CASE li
WHEN li = null THEN null
ELSE li.date
END
) as likes, COLLECT(
CASE v
WHEN v = null THEN null
ELSE v.date
END
) as views, o 
OPTIONAL MATCH (uu:User)-[f:follows]-(:Group)-[:to]-(msg) WHERE uu.tenantId <> "1787106602" 
RETURN msg, likes as likeDates, views as viewDates

Note: Remember if you (later) decide to reference properties on any potentially null relationships in the RETURN statement you'd have to do it there too. (or switch to the other hackfix)
Note2: I reiterate that this is really a hackfix, I don't like it, I hope this is a bug that will be fixed, if not we need a better way to handle...

[kearnsw]

I can confirm the above solution works to generate the output in the original query. Alternatively, I removed the date property selector from the query (based on your explanation) and retrieve the relationship in Scala from the InternalRelationship class.

MATCH (o:Organization {id: "1787106602"})-[:from]-(m:Message {id: "4474437524"}) 
OPTIONAL MATCH (m)-[v:viewed]-(u:User) 
OPTIONAL MATCH (m)-[li:likes]-(u) 
WITH m as msg, COLLECT(li) as likes, COLLECT(v) as views, o 
OPTIONAL MATCH (uu:User)-[f:follows]-(:Group)-[:to]-(msg) WHERE uu.tenantId <> "1787106602" 
RETURN msg, likes, views