Neo4j Cypher sorting and pattern comprehension

Question

I have a following Cypher query:

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) 
WHERE id(parentD) = 89592 
OPTIONAL MATCH (childD)<-[:SET_FOR]->(sortValue89686:Value)-[:SET_ON]->(sortCharacteristic89686:Characteristic) 
WHERE id(sortCharacteristic89686) = 89686 
WITH ru, u, childD , sortValue89686 
ORDER BY sortValue89686.value ASC, childD.name DESC 
SKIP 0 LIMIT 100 
RETURN ru, u, childD AS decision, 
[ (parentD)<-[:DEFINED_BY]-(entity)<-[:COMMENTED_ON]-(comg:CommentGroup)-[:COMMENTED_FOR]->(childD) | {entityId: id(entity),  types: labels(entity), totalComments: toInt(comg.totalComments)} ] AS commentGroups, 
[ (parentD)<-[:DEFINED_BY]-(c1:Criterion)<-[:VOTED_ON]-(vg1:VoteGroup)-[:VOTED_FOR]->(childD) | {criterionId: id(c1),  weight: vg1.avgVotesWeight, totalVotes: toInt(vg1.totalVotes)} ] AS weightedCriteria, 
[ (parentD)<-[:DEFINED_BY]-(ch1:Characteristic)<-[:SET_ON]-(v1:Value)-[:SET_FOR]->(childD) | {characteristicId: id(ch1),  value: v1.value, valueType: ch1.valueType, visualMode: ch1.visualMode} ] AS valuedCharacteristics

Right now the following part in this query:

OPTIONAL MATCH (childD)<-[:SET_FOR]->(sortValue89686:Value)-[:SET_ON]->(sortCharacteristic89686:Characteristic) 
WHERE id(sortCharacteristic89686) = 89686

produces nulls for all pattern comprehension in the return statement. The commentGroups, weightedCriteria and valuedCharacteristics are not null and contain values only for records where sortValue89686.value is not null.

Instead of NULLs at the screenshot above I expect a values(for commentGroups, weightedCriteria and valuedCharacteristics) for rows even when sortValue89686.value is null.

What am I doing wrong and how to fix it ?

You've mentioned sortValue212, but I don't see that anywhere in the query. Can you clarify what this is? — InverseFalcon
@InverseFalcon Sorry, in this particular case this is sortValue89686.value. I have updated the question. — alexanoid

InverseFalcon InverseFalcon · Accepted Answer · 2017-03-12T05:39:47

This looks to be the result of a bug affecting Neo4j 3.1.1 and below (likely this one, or at least related to it).

I can reproduce your results with Neo4j 3.1.1, but not in Neo4j 3.1.2 (no nulls, results look good).

Try upgrading to 3.1.2 (it's not set as their default newest release, so grab it from here) and give it a spin.

Neo4j Cypher sorting and pattern comprehension

1 Answers