ConstraintValidation Failure with MERGE within UNWIND statement

[leelandclay]

When I first created my schema, I created a property called interests to store a comma separated string that the users would type in. The plan was to create several hundred default interests and then allow users to add additional entries as needed. The property was a quick way for me to start capturing user input without having all of the nodes there to choose from.
Now I've created the Interest nodes and I'm writing a query to merge the entries from the comma separated string into actual nodes. The Interest node has two properties (label and value). The value property has a unique constraint. In order to make things easy, I decided to just remove all spaces and lower case the users entry to use as the value.
When I run the command on test nodes without any overlapping entries, everything works fine. However, if I create duplicate entries within two Person nodes, I get a constraint error, even though I'm using MERGE for the creation command.

MATCH (e:Person)
WHERE e.interests IS NOT NULL
WITH COLLECT({userId: e.userId, interests: split(e.interests, ',') }) as entries
UNWIND entries as entry
	MATCH (p:Person {userId: entry.userId })
        UNWIND entry.interests as interest
    	    MERGE (i:Interest {value: toLower(trim(replace(interest, " ", ""))), label: trim(interest) })
            MERGE (p)-[:HAS_INTEREST]->(i)

For testing, I created a couple of Person nodes with an interests property set to "Interest 1, Interest 2 , interest 3" (yes, the extra spaces are intended in case the user entered extra spaces).

Does MERGE work differently within an UNWIND?

[leelandclay]

I'm not sure if this is the "proper" fix...but I was playing around with different ways to get this to work and discovered if I just use the value property for the initial MERGE and then add an ON CREATE SET command afterwards, it seems to work. I modified the command to use this for the MERGE instead:

MERGE (i:Interest {value: toLower(trim(replace(interest, " ", ""))) })
ON CREATE SET i.label = trim(interest)

[mark_needham]

It sounds like it might be a bug. Would you be able to create a small sample dataset which reproduces the issue so that we can share it with the Cypher engineering team?

I did try to do that myself, but I haven't been able to get it to fail yet. Perhaps I'm not doing exactly the same thing as you are?

CREATE (:Person {userId: 1, interests: "Football,Tennis,Neo4j"})
CREATE (:Person {userId: 2, interests: "Football,Rugby,Neo4j"})

And then:

MATCH (e:Person)
WHERE e.interests IS NOT NULL
WITH COLLECT({userId: e.userId, interests: split(e.interests, ',') }) as entries
UNWIND entries as entry
	MATCH (p:Person {userId: entry.userId })
        UNWIND entry.interests as interest
    	    MERGE (i:Interest {value: toLower(trim(replace(interest, " ", ""))), label: trim(interest) })
            MERGE (p)-[:HAS_INTEREST]->(i)

[leelandclay]

Hi Mark,

I changed to a new database to create the sample dataset and everything works fine. I went back to my other database and I was still getting the error.

I looked deeper into it, and I found the problem. When I was creating the test properties, I was testing for a lot of different scenario's and apparently created interests of "Interest1" and "Interest 1" (with a space). The query did exactly what it was supposed to do and remove the spaces...which tried to create two entries... { value: 'interest1', label: 'Interest1' } and { value: 'interest1', label: 'Interest 1' }. Since I created a unique constraint, it failed (as it should have).

The problem is completely due to my data and does not appear to be a bug. Now that I know that I created the problem, I think the "ON CREATE SET" addition is the proper way to go.

Thanks for looking into this and getting me to realize that I should have dropped out into a test database to verify weirdness 🙂