Create Complete RelationshipProperties with CALL { MERGE (x)-[r]->(y) } IN TRANSACTIONS not working.

[Omer]

Goodmorning,

I want to shorten and merge the Paths into one relation with properties from exististing relations.

When I Merge One User to object I get the relationship NEW_OK with All the TRUE Properties.

But I get an error Not enough Memory when I want to merge the rest. So I am using transactions.

So  With

MATCH (u:User)-[r1]->(a:A)<--(o:Object)<--(ro:Role)<-[r2]-(u)
CALL { WITH u, r1, o, r2
MERGE (u)-[r:NEW]->(o) 
SET r.a = (r1 = r2)
, r.b = (r1 = r2)
 etc... 
} IN TRANSACTIONS
;

Then I dont get all Properties set but NEW_NOK.

[glilienfield]

Can you post the entire cypher and exact error? 

[Omer]

Goodevening, the server is at work and is being migrated to a new one.

The error is in the context of '..could not finish not enough memory' I have to reproduce

And the psudo code above is the one I use.

The behavior is the one without IN TRANSACTIONS  all properties are created.

But with IN TRANSACTION the relation is not filled with all properties. (+/- 4 relations million small server)...

Note:

There is only One relationtype DIRECT the OK /NOK are for explanning the problem.

Yours Kindly  

[glilienfield]

What are you trying to accomplish with this block of code?

SET r.a = (r1 = r2)
, r.b = (r1 = r2)

Looks like you are setting r.a and r.b to the Boolean value (r1=r2), which I would imagine will always be false, since they are different relationships. How do you want to set the properties of ‘r’? 

[Omer]

Thank you for the reply.

Excuse the psudo code is not correct. I forget to write the properies.

The right example = .... SET r.a = (r1.a = r2.a),    r.b = (r1.b = r2.b), etc...

MATCH (u:User)-[r1]->(a:A)<--(o:Object)<--(ro:Role)<-[r2]-(u)
CALL { WITH u, r1, o, r2
MERGE (u)-[r:NEW]->(o) 
SET r.a = (r1.a = r2.a)
, r.b = (r1.b = r2.b)
, r.c = (r1.c = r2.c)
, r.d = (r1.d = r2.d) 
} IN TRANSACTIONS
;

So when both are true then de property is set and there is only a,b,c,d if there set in the existing :AND relation the value is true (there are no properties with false) But the are :AND relations where a,  b, d exists but c not.  

So when doing one User with the example code above,  then the NEW relation is set nicely with all properties (a,b, d)  = true (in the drawing NEW_OK).*

But when I want to do It for all user and Relations there seems not be enough memory on the computer. So then I think I use transactions with the intention to wright the data and freeup memory. But then the same user the NEW relation only contains a:true and c.true but not d:true (in the drawing NEW_NOK).*.

It seems like IN TRANSACTIONS does not do the same MERGE on the properties.

I think the MERGE only take care if the Path exists in Database. But MERGE does not set other properties when the Path allready exists in the Database when using transactions. In my usecase there are two 'Role' paths with there own properties,

*The Drawing relations NEW_OK and NEW_NOK are the same NEW so only One relation is set with properties true.

Yours Kindly

[glilienfield · Accepted solution]

I have done a little investigation through some simulations. This is what I observed.  Your match query will return two rows, one for each value 'r2', since there are two Role nodes related to the user. The first instance of 'r2' will create the relationship 'r' because it does not exists and then set the values of 'r.x'. The second instance of 'r2' will match 'r', then set the values of 'r.x'.  The value of the expression 'r1.x = r2.x' will equal null then one or both values equal null. This ends up removing the value of r.x if it exists when the second instance of 'r2' is evaluated. As such, the final result of r.a, r.b, r.c. and r.d will the values calculated between 'r1' and the second of instance of 'r2' that gets processed. This means the order in which the 'r2' relationships get processed determines the outcome. This is not desirable, since the result is indeterminate. In my simulations, I got 'r.d=true' as the result when I forced the 'r2' relationship with 'r2.d=true' last, and I got 'r.a=true, r.c=true' as the result when I forced the reverse order of processing the 'r2' relationships. 

I am not sure how you got the result 'r.a=true, r.c=true, and r.d=true' from your query, as you indicate in your 'NEW_OK' relationship. If you want that result, you have to stop the second relationship results from overwriting the first results for the null results in the second pass. The following query seems to do that. With this query, I got the result 'r.a=true, r.c=true, and r.d=true', regardless of order.  Note, the 'MATCH' statements are different from yours, as I didn't create the relationship to the 'Object' node for the testing.  Replace the three 'MATCH' statement with yours.

MATCH (o:Object)
MATCH (u:User)-[r1]->(a:A)
MATCH (u)-[r2]->(:Role)
with u, r1, r2, o
CALL { 
    WITH u, r1, o, r2
    MERGE (u)-[r:NEW]->(o) 
    ON CREATE SET 
    r.a = (r1.a = r2.a), 
    r.b = (r1.b = r2.b), 
    r.c = (r1.c = r2.c), 
    r.d = (r1.d = r2.d) 
    ON MATCH SET 
    r.a = coalesce(r1.a = r2.a, r.a), 
    r.b = coalesce(r1.b = r2.b, r.b), 
    r.c = coalesce(r1.c = r2.c, r.c), 
    r.d = coalesce(r1.d = r2.d, r.d) 
} 

Does this help?

[Omer]

Thank you for the reply.

Today I installed Neo4j on my new server (with more RAM).

I also tried the ON MATCH and ON CREATE in combination with IN TRANSACTION but that did not work. 

I don't fully understand how MERGE works with relationship properties because the desired result is when I'am not using IN TRANSACTION with one user. .  

The coalesce adition is a nice one. I will try that.

Yours kindly

[glilienfield]

I don't know what is happening in your environment.  I just ran the query with and without the 'in transactions' phrase and I got the same result:  a, c, and d are true.  Here is my test data script:

create(u:User{id:0})
create(a:A{id:0})
create(u)-[r:AND]->(a)
set r.a =true, r.b=true, r.c=true, r.d=true;

match(u:User{id:0})
create(role:Role{id:0})
create(u)-[r:AND]->(role)
set r.a =true, r.c=true;

match(u:User{id:0})
create(role:Role{id:1})
create(u)-[r:AND]->(role)
set r.d =true;

create(o:Object{id:0});

Maybe try using a Docker image to determine if you get the same result for both cases. 

[Omer]

I have a new server with more HEAP memory and done some new tests (8 variants).

1) With Create IN TRANSACTIONS added a key for r.role (CREATE so more relations)

2) With Create without IN TRANSACTIONS added a key for r.role (CREATE so more relations)

3) With MERGE with IN TRANSACTIONS

4) With MERGE without IN TRANSACTIONS

5) With MERGE and ON CREATE ON MATCH and with IN TRANSACTIONS

6) With MERGE and ON CREATE ON MATCH and without IN TRANSACTIONS

7) With MERGE and ON CREATE ON MATCH and without IN TRANSACTIONS and Coalesce()

😎 With MERGE and ON CREATE ON MATCH and with IN TRANSACTIONS and Coalesce()

7 AND 8 with Coalesce() gives the right results!!

Thank you.

Yours kindly 

Omer