Failure to invoke apoc.temporal.format() when OPTIONAL MATCH returns no nodes

[igorclark]

Hi folks,

When I try to use apoc.temporal.format inside an OPTIONAL MATCH clause which returns no nodes, I get a Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke function 'apoc.temporal.format': Caused by: java.lang.NullPointerException. Running the same query with MATCH instead of OPTIONAL MATCH works as expected; similarly, running the same query with a pattern that returns nodes works fine whether with using an OPTIONAL match or not.

I've come across it in a much more complex query, so I thought it might be something to do with that, but I've reduced it to the example below, which is the most simple case I can show with it happening, and it's still there.

Is this a bug or am I doing something wrong? Currently running Neo4J 3.4.7, APOC 3.4.0.7.

Thanks!

neo4j> create (n:TestLabel{from:date("20190101"),to:date("20190201")});
0 rows available after 0 ms, consumed after another 0 ms
Added 1 nodes, Set 2 properties, Added 1 labels
neo4j> match (n:TestLabel) return apoc.temporal.format(n.from, "yyyyMMdd");
+------------------------------------------+
| apoc.temporal.format(n.from, "yyyyMMdd") |
+------------------------------------------+
| "20190101"                               |
+------------------------------------------+

1 row available after 1 ms, consumed after another 0 ms
neo4j> optional match (n:TestLabel) return apoc.temporal.format(n.from, "yyyyMMdd");
+------------------------------------------+
| apoc.temporal.format(n.from, "yyyyMMdd") |
+------------------------------------------+
| "20190101"                               |
+------------------------------------------+

1 row available after 0 ms, consumed after another 0 ms
neo4j> match (n:SomeOtherLabel) return apoc.temporal.format(n.from, "yyyyMMdd");
+------------------------------------------+
| apoc.temporal.format(n.from, "yyyyMMdd") |
+------------------------------------------+
+------------------------------------------+

0 rows available after 0 ms, consumed after another 0 ms
neo4j> optional match (n:SomeOtherLabel) return apoc.temporal.format(n.from, "yyyyMMdd");
Failed to invoke function `apoc.temporal.format`: Caused by: java.lang.NullPointerException

[ameyasoft]

It looks like there is no node with label "SomeOtherLabel"

Running the query, MATCH (n:SomeOtherLabel) RETURN n yields '(no changes, no records)'

Running the query, OPTIONAL MATCH (n:SomeOtherLabel) RETURN n yields 'null' and hence your RETURN apoc.temporal.format(n.from, "yyyyMMdd") yields the error you are getting.

[andrew_bowman]

OPTIONAL MATCH will always return rows (provided that there are input rows to execute it upon). It may not return nodes (emitting null instead)...but OPTIONAL MATCH is not a filtering clause, it can never reduce the number of rows.

Which means that this function doesn't handle null inputs. Perhaps it should. We'll have to chat about that among ourselves and see if changing that makes sense.

In the meantime, when you do have to use this from the results of an OPTIONAL MATCH when a variable or property can be null, you may want to use a CASE around the property, and when it's not null use the function, and when it is, supply a reasonable default.

[igorclark]

Thank you both for your answers. @ameyasoft, I think that's the error I was wondering about, should this function just crash like this when presented with a NULL? Which is what @andrew.bowman was wondering in his response. Personally I think it would be great if it handled NULLs.

Andrew, thanks, the CASE trick is one I use often, and in fact was already doing in this query - the apoc function call was inside something like this:

MATCH (o{prop:$prop}) // returns an 'o' node

OPTIONAL MATCH (o)-[:REL]->(n) // may or may not return any 'n' nodes

WITH
    CASE WHEN n IS NOT NULL THEN COLLECT( DISTINCT {
        from: apoc.temporal.format(n.from, "yyyyMMdd"),
        to: apoc.temporal.format(n.to, "yyyyMMdd")
    } ) ELSE [] END AS result

so I thought it would always be handled, but even having it inside that clause made the whole query fail as above. I'll go and look at whether there are other places I could put the check too, or other ways to work it.

In the meantime, I have TOINT( REPLACE( TOSTRING( n.from ), '-', '' ) ) which does the same, it's just a bit less elegant

Cheers,
Igor