Lazy coalesce

[jsmccrumb]

I was hoping to do something like:

coalesce(
  head([(n1)-[r:FIRST_REL]->(n2) | r]),
  head([(n1)-[r:SECOND_REL]->(n2) | r]),
  head([(n1)-[r:THIRD_REL]->(n2) | r])
) AS r

to get the first of similar relationships that might exist between two nodes. This works, but from the PROFILE of the query it looks like all the head()s are running. I was wondering if anyone could confirm or deny that coalesce would lazily evaluate the options? Would rather not check second rel if first is found, etc.

[mojo2go · Accepted solution]

Your intended result is what I am getting. I created your 'n1' and 'n2' nodes connected only by the FIRST_REL relationship.

MERGE (n1:Coalesce {name:'n1'})-[r1:FIRST_REL {name:'first_rel'}]->(n2:Coalesce {name:'n2'})
//MERGE (n1)-[r2:SECOND_REL {name:'second_rel'}]->(n2)
//MERGE (n1)-[r3:SECOND_REL {name:'third_rel'}]->(n2)

I ran your coalesce query (verbatim) on it.

MATCH (n1:Coalesce {name:'n1'}), (n2:Coalesce {name:'n2'})
RETURN coalesce(
  head([(n1 {name:'n1'})-[r:FIRST_REL]->(n2 {name:'n2'}) | r]),
  head([(n1 {name:'n1'})-[r:SECOND_REL]->(n2 {name:'n2'}) | r]),
  head([(n1 {name:'n1'})-[r:THIRD_REL]->(n2 {name:'n2'}) | r])
) AS r

It returned only FIRST_REL, of course.

Then I added in the SECOND_REL and THIRD_REL

MERGE (n1:Coalesce {name:'n1'})-[r1:FIRST_REL {name:'first_rel'}]->(n2:Coalesce {name:'n2'})
MERGE (n1)-[r2:SECOND_REL {name:'second_rel'}]->(n2)
MERGE (n1)-[r3:SECOND_REL {name:'third_rel'}]->(n2)

...ran your coalesce query (verbatim) again. Same as above. And it returned only FIRST_REL again.

The two PROFILE outputs were identical:

My conclusion is that someone at Neo4j heard you 10 months ago and fixed the problem because it seems to be behaving lazily now, executing only the first successful item.

[mojo2go]

However, I didn't understand why you placed the pipe operator and the 'r' in each of the 3 coalesce items, so I rewrote it without. And I got a completely different result. All items were eagerly executed everytime, and the results of all of all items present were returned. So coalesce it seems was ignored in this case? Maybe this is something that helps but I can't explain it further.

Here's my modified query.

PROFILE
MATCH (n1:Coalesce {name:'n1'})-[r]->(n2:Coalesce {name:'n2'})
RETURN coalesce(
  head((n1 {name:'n1'})-[r:FIRST_REL]->(n2 {name:'n2'})),
  head((n1 {name:'n1'})-[r:SECOND_REL]->(n2 {name:'n2'})),
  head((n1 {name:'n1'})-[r:THIRD_REL]->(n2 {name:'n2'}))
) AS r

...and for the results, when there was only 1 relationship present between the two nodes, that path was returned. When there were 3 relationships present, all 3 were returned, not just the first one.

[mdfrenchman]

@mojo2go try removing the rel r from the path.

mojo2go:

-[r]->

[jsmccrumb]

Agreed, it is definitely resolved now! I only wish I had saved a snipping of the profiles that lead me to believe it was evaluating them all before... o well!

Ran a query just now where it had 2 different head's in a coalesce (a simple one and one with an extended path), simple one had 9 db hits, then I reversed the direction on the simple one (so it wouldn't match) and my projection step was up to 35 db hits. Definitely lazily evaluated.

[mdfrenchman]

This is a good reminder for us as to why performance tuning is ever changing. A previous rule, might not be the case anymore.