How to find the line number when your cypher is trying to MERGE a null value

[pdrangeid]

Neo.DatabaseError.Statement.ExecutionFailed: Expected to find a node at ref slot 9 but found instead: null

This error leaves me wanting more info! 😉

My only method is to take my entire cypher statement, and start hacking MERGE statements out, until I find the one that is offending.

Why can't we have the offending line of CYPHER returned in the error message details when this error is raised?

[stefan_armbrust]

a trick to map a null value: MERGE (:Person{name:coalesce(row.id, "n/a")})

This will create a n/a node for the "unknown" person. Of course it depends on your domain if you want to have a dummy for a unknown person.

If you just want to skip the MERGE if the id is null you can use apoc:

CALL apoc.do.when(row.id is null, "MERGE (p:Person{id:row.id}) ....") yield value

[pdrangeid]

Stefan,

Thanks! I've been making good use of the coalesce, and the "ignore me in case when" (which is how I ended up fixing the failure)... Didn't know about the apoc.do.when, that looks a little cleaner. (I should spend more time reading on apoc, there are so many gems in there!)

My challenge is I've got a bit more complex bit of cypher that is reading a list of MODIFIED rows from an outside database, and then doing UPDATES to nodes for asset management (removing relationships, updating properties, and re-adding relationships back in with nodes that have alias relationships etc etc, so I'm sure this will happen again in the future... I'm just curious why it can't tell me which line it's choking on so I can find the offending part...

here's the (now working) entire ugly beast:

// NOW we should look at any tickets MODIFIED (BUT NOT CREATED) after the last Asset ingestion
MATCH (lna:Crmasset),(cdl:Crmdataload {name:'CommitCRM'})
WITH cdl,max(lna.createdon) as lastnewasset,timestamp() AS howsoonisnow
WITH cdl,apoc.date.format(coalesce(lastnewasset,0),'ms','yyyy-MM-dd HH:mm:ss.sss') AS lastnewasset,timestamp() AS howsoonisnow,apoc.date.format(coalesce(cdl.assetrefresh,0),'ms','yyyy-MM-dd HH:mm:ss.sss', 'CST') AS lastassetrefresh
CALL apoc.load.driver("com.extendedsystems.jdbc.advantage.ADSDriver")
call apoc.load.jdbc('jdbc:extendedsystems:advantage://commitcrm:6262;catalog=//commitcrm/CommitCRM/Db','select RECID,ACCOUNTRECID,CONTACTRECID,
PARENTASSETRECID,STATUS,NAME,SERIALNO,ASSETCODE,LOCATION,DESCRIPTION,MANUFACTURER,MODEL,USER3,CREATEDATE,UPDATEDATE,USER1 from Assets where RECID is not null AND CREATEDATE < \''+lastnewasset+'\' AND UPDATEDATE > \''+lastassetrefresh+'\'') YIELD row
MATCH (a:Crmasset {recid:row.RECID})
// FIND matches to row values to change the existing (:Crmasset) properties and relationships to
OPTIONAL MATCH (rs:Recordstatus {state:row.STATUS})
OPTIONAL MATCH (a1:Company {acctrecid:row.CARDID})
OPTIONAL MATCH (a:Company {acctrecid:coalesce(a1.acctrecid,'None Provided')})
OPTIONAL MATCH (c1:Contact {acctrecid:row.CONTACTRECID})
OPTIONAL MATCH (c:Contact {acctrecid:coalesce(c1.acctrecid,'None Provided')})
OPTIONAL MATCH (e1:Crmemployee {acctrecid:row.WORKERID})
OPTIONAL MATCH (e:Crmemployee {acctrecid:coalesce(e1.acctrecid,'None Provided')})
OPTIONAL MATCH (pa1:Crmasset {recid:row.PARENTASSETRECID})
OPTIONAL MATCH (pa:Crmasset {recid:coalesce(pa1.recid,'None Provided')})
OPTIONAL MATCH (np:Manufacturer {name:'None Provided'})
OPTIONAL MATCH (m1:Manufacturer {name:row.MANUFACTURER})
OPTIONAL MATCH (m:Manufacturer {name:coalesce(m1.name,'None Provided')})
OPTIONAL MATCH (m2:Manufacturer)--(:Mfralias {name:row.MANUFACTURER})
OPTIONAL MATCH (acnp:Assetcategory {name:'None Provided'})
OPTIONAL MATCH (c1:Assetcategory {name:trim(row.USER1)})
OPTIONAL MATCH (c:Assetcategory {name:coalesce(c1.name,'None Provided')})
OPTIONAL MATCH (c2:Assetcategory)--(:Crmcategory {name:trim(row.USER1)})
OPTIONAL MATCH (md1:Model {name:row.MODEL})
OPTIONAL MATCH (md:MODEL {name:coalesce(md1.name,'None Provided')})
// FIND relationships for the existing (:Crmasset) node
OPTIONAL MATCH (a)-[asr:ASSET_STATE]-(oas:Recordstatus)
OPTIONAL MATCH (a)-[aor:ASSET_OF]-(oao:Company)
OPTIONAL MATCH (a)-[ubr:USED_BY]-(oub:Contact)
OPTIONAL MATCH (a)-[ibr:INSTALLED_BY]-(oib:Crmemployee)
OPTIONAL MATCH (a)-[cor:CHILD_OF]-(oco:Crmasset)
OPTIONAL MATCH (a)-[bor:BLUENET_OWNED]-(obo:Crmhaas)
OPTIONAL MATCH (a)-[mbr:MANUFACTURED_BY]-(omb:Manufacturer)
OPTIONAL MATCH (omb)--(omba:Mfralias)
OPTIONAL MATCH (a)-[icr:IN_CATEGORY]->(oic:Assetcategory)
OPTIONAL MATCH (oic)--(oica:Crmcategory)
OPTIONAL MATCH (a)-[aimr:ASSET_IS_MODEL]->(oaim:Model)
OPTIONAL MATCH (ih:Crmhaas)
// REMOVE all relationships that are no longer correct
FOREACH (ignoreMe in CASE WHEN row.STATUS <> oas.state THEN [1] ELSE [] END | DELETE asr)
FOREACH (ignoreMe in CASE WHEN row.ACCOUNTRECID <> oao.acctrecid THEN [1] ELSE [] END | DELETE aor)
FOREACH (ignoreMe in CASE WHEN row.CONTACTRECID <> oub.acctrecid THEN [1] ELSE [] END | DELETE ubr)
FOREACH (ignoreMe in CASE WHEN not(exists(oib.acctrecid)) or (row.INSTALLEDBY <> oib.acctrecid)  THEN [1] ELSE [] END | DELETE ibr)
FOREACH (ignoreMe in CASE WHEN not(exists(oco.recid)) or (row.PARENTASSETRECID <> oco.recid)  THEN [1] ELSE [] END | DELETE cor)
FOREACH (ignoreMe in CASE WHEN (trim(toLower(row.USER5)) <> 'haas')  THEN [1] ELSE [] END | DELETE bor)
FOREACH (ignoreMe in CASE WHEN not(exists(omb.name)) or (row.MANUFACTURER <> omb.name and trim(row.MANUFACTURER) <> omba.name)  THEN [1] ELSE [] END | DELETE mbr)
FOREACH (ignoreMe in CASE WHEN not(exists(oic.name)) or (row.USER1 <> oic.name and trim(row.USER1) <> oica.name)  THEN [1] ELSE [] END | DELETE icr)
FOREACH (ignoreMe in CASE WHEN not(exists(oaim.name)) or (row.MODEL <> oaim.name)  THEN [1] ELSE [] END | DELETE aimr)
// REMOVE all node properties that are no longer correct (then set to the values based on the row)
FOREACH (ignoreMe in CASE WHEN a.name <> row.NAME THEN [1] ELSE [] END | SET a.name=row.NAME)
FOREACH (ignoreMe in CASE WHEN a.category <> row.USER1 THEN [1] ELSE [] END | SET a.category=row.USER1)
FOREACH (ignoreMe in CASE WHEN a.manufacturer <> row.MANUFACTURER THEN [1] ELSE [] END | SET a.manufacturer=row.MANUFACTURER)
FOREACH (ignoreMe in CASE WHEN a.assettag <> row.ASSETCODE THEN [1] ELSE [] END | SET a.assettag=row.ASSETCODE)
FOREACH (ignoreMe in CASE WHEN a.location <> row.LOCATION THEN [1] ELSE [] END | SET a.location=row.LOCATION)
FOREACH (ignoreMe in CASE WHEN a.description <> row.DESCRIPTION THEN [1] ELSE [] END | SET a.description=row.DESCRIPTION)
FOREACH (ignoreMe in CASE WHEN a.serial <> row.SERIALNO THEN [1] ELSE [] END | SET a.serial=row.SERIALNO)
FOREACH (ignoreMe in CASE WHEN a.mac <> toLower(replace(replace(replace(row.USER3,' ',''),':',''),'-','')) THEN [1] ELSE [] END | SET a.mac=toLower(replace(replace(replace(row.USER3,' ',''),':',''),'-','')))
FOREACH (ignoreMe in CASE WHEN a.createdon <> row.CREATEDATE THEN [1] ELSE [] END | SET a.createdon=row.CREATEDATE)
FOREACH (ignoreMe in CASE WHEN a.updatedon <> row.UPDATEDATE THEN [1] ELSE [] END | SET a.updatedon=row.UPDATEDATE)
// RECREATE proper RELATIONSHIPS if they are missing
FOREACH (ignoreMe in CASE WHEN not(exists(m.name)) and exists(m2.name) THEN [1] ELSE [] END | MERGE (a)-[:MANUFACTURED_BY]->(m2))
FOREACH (ignoreMe in CASE WHEN not(exists(m.name)) and not(exists (m2.name)) and row.MANUFACTURER is not null and trim(row.MANUFACTURER) <> "" THEN [1] ELSE [] END | MERGE (m:Manufacturer {name:row.MANUFACTURER}))
FOREACH (ignoreMe in CASE WHEN exists(m.name) THEN [1] ELSE [] END | MERGE (a)-[:MANUFACTURED_BY]->(m))
FOREACH (ignoreMe in CASE WHEN not(exists(m.name)) THEN [1] ELSE [] END | MERGE (a)-[:MANUFACTURED_BY]->(np))
FOREACH (ignoreMe in CASE WHEN not(exists(c.name)) and exists(c2.name) THEN [1] ELSE [] END | MERGE (a)-[:IN_CATEGORY]->(c2))
FOREACH (ignoreMe in CASE WHEN not(exists(c.name)) and not(exists(c2.name)) and row.USER1 is not null and trim(row.USER1) <> "" THEN [1] ELSE [] END | MERGE (c:Assetcategory {name:row.USER1}))
FOREACH (ignoreMe in CASE WHEN exists(c.name) THEN [1] ELSE [] END | MERGE (a)-[:IN_CATEGORY]->(c))
FOREACH (ignoreMe in CASE WHEN not(exists(c.name)) THEN [1] ELSE [] END | MERGE (a)-[:IN_CATEGORY]->(acnp))
FOREACH (ignoreMe in CASE WHEN not(exists(md.name)) THEN [1] ELSE [] END | MERGE (a)-[:ASSET_IS_MODEL]->(md))
FOREACH (ignoreMe in CASE WHEN row.MODEL is not null and trim(row.MODEL) <> "" THEN [1] ELSE [] END | MERGE (md:Model {name:row.MODEL}))
FOREACH (ignoreMe in CASE WHEN exists(md.name) THEN [1] ELSE [] END | MERGE (a)-[:ASSET_IS_MODEL]->(md))
FOREACH (ignoreMe in CASE WHEN not(exists(md.name)) THEN [1] ELSE [] END | MERGE (a)-[:ASSET_IS_MODEL]->(mnp))
MERGE (a)-[:ASSET_STATE]-(rs)
MERGE (a)-[:ASSET_OF]->(a)
MERGE (a)-[:USED_BY]-(c)
MERGE (a)-[:INSTALLED_BY]-(ib)
MERGE (a)-[:ASSET_IS_MODEL]->(md)
FOREACH (ignoreMe in CASE WHEN exists(row.PARENTASSETRECID) THEN [1] ELSE [] END | MERGE (pa)<-[:CHILD_OF]-(a))
FOREACH (ignoreMe in CASE WHEN trim(toLower(row.USER5))='haas' THEN [1] ELSE [] END | MERGE (a)-[:BLUENET_OWNED]->(ih))
// SET the timestamp for when this refresh procedure last ran
FOREACH (ignoreMe in CASE WHEN cdl.assetrefresh<>howsoonisnow THEN [1] ELSE [] END | SET cdl.assetrefresh=howsoonisnow)
RETURN count(a) AS `//Updated (:Crmasset) when data or relationships have changed...`;

[stefan_armbrust]

I consider this a shortcoming in Cypher. When an error happens while building the query plan you normally get detailed information where the error happened. With runtime errors it seems to be different. Would you mind raising an issue with https://github.com/neo4j/neo4j ?

[pdrangeid]

I have done so. Thanks!