Returning null on other relationships only when MERGE relationship inside UNWIND

[venkat125]

I'm using @Query on Repository for updating motherTongue and known languages similar model in IT + motherTongue in model with added toDelete transient field on Knows model class

Trying to update both relationships in single query as below:

@Query("MATCH (f:Person {id: $from})\n" +
		"OPTIONAL MATCH (mt:Language {name: $mot})\n" +
		"OPTIONAL MATCH (f)-[fmtl:MOTHER_TONGUE_IS]->(mt)\n" +
		"UNWIND $relations As obj WITH obj, f, mt, fmtl\n" +
			"CALL apoc.do.when(obj.__properties__.toDelete,\n" +
				"'OPTIONAL MATCH (f)-[fkl:KNOWS]->(l:Language {name: o.__properties__.__target__.__id__}) DELETE fkl RETURN f',\n" +
				"'OPTIONAL MATCH (l:Language {name: o.__properties__.__target__.__id__}) \n" +
					"MERGE (f)-[fklCr:KNOWS]->(l) RETURN f,fklCr,l',\n" +
			"{f:f, o:obj}) YIELD value\n" +
		"WITH f, mt, fmtl, collect(value.fklCr) AS fklCr, collect(value.l) AS l\n" +
			"CALL apoc.do.when(fmtl IS NOT NULL,\n" +
				"'RETURN f, fmtl, mt',\n" +
				"'OPTIONAL MATCH (f)-[fmtlTd:MOTHER_TONGUE_IS]->(:Language) DELETE fmtlTd MERGE (f)-[fmtlCr:MOTHER_TONGUE_IS]->(mt) RETURN f,fmtlCr,mt',\n" +
			"{f:f, fmtl:fmtl, mt:mt}) YIELD value\n" +
	   "RETURN f, fklCr, l, mt, fmtl, collect(value.fmtlCr)")
Person updateRel(@Param("from") String from, @Param("relations") List<Knows> relations, @Param("mot") String motherTongue);

when toDelete: true the result is

{"id":"xyxy","name":"Helge","knowsMt":{"id":14,"language":{"name":"English"}},"knownLanguages":[]}

whereas, when toDelete: false, it does creates know language as "German" but the other relationships are null. In this case: knowMt (Mother Tongue) is null (not retrieved from DB)

{"id":"xyxy","name":"Helge","knowsMt":null,"knownLanguages":[{"id":17,"description":null,"toDelete":null,"language":{"name":"German"}}]}

But could see mother tongue ('xyxy')-[:MOTHER_TONGUE_IS]->('English') relationship created in DB.
Note:

1. This is not only for the same TargetNode even when there are any other relationship to different Labelled Target, those are returning null only.
2. Its working fine with multiple relationships with different targets, when used without UNWIND in query. Issue is faced only when mixing UNWIND for iterate the list along with other relationship updates.

Just tagging meistermeier as you would be more familiar.
Please suggest me if the above query is having any defect. Thanks Team

[meistermeier]

TBH It took me some time to understand the query.

But could see mother tongue ('xyxy')-[:MOTHER_TONGUE_IS]->('English') relationship created in DB.

This means that at least the fmtl was null when the query was issued, right?
I am wondering if also the mt_ = English_ was not existing at this moment. This would be the only explanation I can think of right now.
The result

{"id":"xyxy","name":"Helge","knowsMt":null,"knownLanguages":[{"id":17,"description":null,"toDelete":null,"language":{"name":"German"}}]}

looks like there was only one language in the relations field (German), right?
As a consequence, the UNWIND does just one loop, so it should be the very same outcome as without the unwind(?)

Do you have test data to call this method? Or even better a complete model of the affected domain incl. the MOTHER_TONGUE_IS relationship plus the data that should already exist in the database at the time the query gets issued (of course only related persons and languages).
I wonder what the outcome of the query in cypher-shell or the browser would look like. Maybe this can give us a hint.

Side note: You have used the variable o in the query above but it is not set anywhere. The APOC lib will swallow exceptions in (not only) this case.

[venkat125]

Sorry if the query is not clear for reading. Let me try to explain a bit.
JFYI, Assume Person's mother tongue is English and that person knows German as below graph depicts

Being said that, the query in the OP is update query where it would return input itself, if the input and data in DB are same or else it would update and return the updated data
So, with the below input params there would be no data change in DB and would return the same in person object

from : <person_id>
relations : [{"id":null,"description":"Some description","toDelete":false,"language":{"name":"German"}}]
mot (motherTongue) : "English"

then the output should be similar to below right? by 'RETURN f, fmtl, mt' true part of the statement in the query.

{"id":"e542e5a5","name":"Helge","knowsMt":{"id":14,"language":{"name":"English"}},"knownLanguages":[{"id":12,"description":null,"toDelete":null,"language":{"name":"German"}}]}

but the knowsMt is being returning null like this

{"id":"e542e5a5","name":"Helge","knowsMt":null,"knownLanguages":[{"id":12,"description":null,"toDelete":null,"language":{"name":"German"}}]}

note: toDelete is null bcoz, that field is transient and used for functionality purpose only.
Please let me know if this is still not clear.

On the side note part, the variable o is used to assign the language name in the query
{name: o.__properties__.__target__.__id__} and was referenced in params o:obj on lineNo: 9 in the query.
Also i'm not getting what u mean by

The APOC lib will swallow exceptions in (not only) this case.
If possible please give an idea on this as well. Thanks @meistermeier

Note: adding motherTongue relationship to the following testcase is the model

@Relationship("MOTHER_TONGUE_IS")
private KnowsMt knowsMt; // in person entity

@RelationshipProperties @Data
public class KnowsMt {
	@Id @GeneratedValue private Long id;
	@TargetNode private final Language language;
}

[meistermeier]

The problem is rooted in the combination of returned lists and plain node and relationship.

RETURN f, fklCr, l, mt, fmtl, collect(value.fmtlCr)

SDN checks for list(relationship) and/or list(node) first to pick them out of the result. If such entries exists, it skips the part of single node/relationship due to performance reasons.

The quick fix for your problem would be to change the last return statement to:

RETURN f, fklCr, l, collect(mt), collect(fmtl), collect(value.fmtlCr)

I am currently investigating if a mixture of plain and list of nodes would have too much impact on performance and if we could allow this again in the future.

[meistermeier]

Thanks again for reporting this. I removed the exclusive adding (either plain or list) of result entities in the mapping preparation part. Should work with the initial query out-of-the-box with the next (6.3.1) release.