`MERGE` creates incomplete vertices after the first one

[mingfang]

when creating multiple vertices like this

SELECT * FROM cypher('playground', $$
  UNWIND [{first: 'jon', last: 'snow'}, {first: 'ned', last: 'stark'}] AS map
  MERGE (v:PERSON {first: map.first})
  SET v=map
  RETURN v
$$) AS (v agtype)

returns 2 vertices, but all the vertices after the first one will be incomplete. e.g.

SELECT * FROM cypher('playground', $$
  MATCH (v:PERSON)                                                           
  RETURN v
$$) AS (v agtype)

returns

{"id": 3940649673949198, "label": "PERSON", "properties": {"last": "snow", "first": "jon"}}::vertex
{"id": 3940649673949199, "label": "PERSON", "properties": {"first": "ned"}}::vertex

Note the first vertex is correct but the second vertex is missing the "last" field. Very strange.

[anfa-majid]

The issue you're encountering with MERGE creating incomplete vertices stems from the way the MERGE and SET commands are being applied. In your query, MERGE is used with a condition that solely relies on the first property to identify unique vertices. However, this approach can lead to unexpected results when the first property alone does not uniquely identify a vertex, as MERGE will match existing vertices based on the first property and might not correctly associate the last property with subsequent vertices.

To ensure each vertex is accurately created or matched with its complete set of properties (first and last), you need to adjust the MERGE condition to include all properties that together uniquely define a vertex. The combination of first and last names uniquely identifies a PERSON vertex, therefore both should be included in the MERGE condition to prevent the creation of incomplete vertices.

[mingfang]

@anfa-majid
In my specific test case above, first does uniquely identify the vertex.
I think the problem is much deeper(and more serious) than what you described.

[Akaza20-03]

I think we can fix this issue by explicitly assign each property indivisually in merge operation

//previous line
SET v=map

//updated line
SET v.first=map.first , v.last=map.last

In this updated query, after the MERGE operation, we set each property first and last separately from the map variable, which should ensure that all vertices have both "first" and "last" fields correctly set.

[mingfang]

@Akaza20-03
No that does not work either.
Please try it for yourself.

[jrgemignani]

@mingfang It looks like maybe my PR #1718 might have fixed this one too?! This is in the master branch, btw.

psql-16.1-5432-pgsql=# SELECT create_graph('playground');
NOTICE:  graph "playground" has been created
 create_graph
--------------

(1 row)

psql-16.1-5432-pgsql=# SELECT * FROM cypher('playground', $$
psql-16.1-5432-pgsql$#   UNWIND [{first: 'jon', last: 'snow'}, {first: 'ned', last: 'stark'}] AS map
psql-16.1-5432-pgsql$#   MERGE (v:PERSON {first: map.first})
psql-16.1-5432-pgsql$#   SET v=map
psql-16.1-5432-pgsql$#   RETURN v
psql-16.1-5432-pgsql$# $$) AS (v agtype);
                                                  v
-----------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "PERSON", "properties": {"last": "snow", "first": "jon"}}::vertex
 {"id": 844424930131970, "label": "PERSON", "properties": {"last": "stark", "first": "ned"}}::vertex
(2 rows)

psql-16.1-5432-pgsql=#

[jrgemignani]

@mingfang Oops, nvm, sigh,...

psql-16.1-5432-pgsql=# SELECT * FROM cypher('playground', $$
psql-16.1-5432-pgsql$#   MATCH (v:PERSON)
psql-16.1-5432-pgsql$#   RETURN v
psql-16.1-5432-pgsql$# $$) AS (v agtype);
                                                 v
----------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "PERSON", "properties": {"last": "snow", "first": "jon"}}::vertex
 {"id": 844424930131970, "label": "PERSON", "properties": {"first": "ned"}}::vertex
(2 rows)

psql-16.1-5432-pgsql=#

[jrgemignani]

@mingfang I will look into this one. It looks like it is creating the correct return tuple, just not inserting the correct tuple. I sometimes think that I should just rewrite merge altogether.

[jrgemignani]

@mingfang Question - Just curious, why are you doing it this particular way? If you use merge as follows, it works -

psql-16.1-5432-pgsql=# SELECT * FROM cypher('playground', $$
  UNWIND [{first: 'jon', last: 'snow'}, {first: 'ned', last: 'stark'}] AS map
  MERGE (v:PERSON {first: map.first, last: map.last}) RETURN v $$) AS (v agtype);
                                                  v
-----------------------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "PERSON", "properties": {"last": "snow", "first": "jon"}}::vertex
 {"id": 844424930131972, "label": "PERSON", "properties": {"last": "stark", "first": "ned"}}::vertex
(2 rows)

psql-16.1-5432-pgsql=# SELECT * FROM cypher('playground', $$
  MATCH (v:PERSON)
  RETURN v
$$) AS (v agtype);
                                                  v
-----------------------------------------------------------------------------------------------------
 {"id": 844424930131971, "label": "PERSON", "properties": {"last": "snow", "first": "jon"}}::vertex
 {"id": 844424930131972, "label": "PERSON", "properties": {"last": "stark", "first": "ned"}}::vertex
(2 rows)

psql-16.1-5432-pgsql=#

I'm thinking this might be an issue with SET, and not MERGE.

[mingfang]

My real use case is to create a graph from a SQL query and I don't know ahead of time what fields are returned.
This is my function to run SQL and return Agtype

-- Apache AGE load_sql
CREATE OR REPLACE FUNCTION public.load_sql(query agtype) RETURNS SETOF agtype AS $$
BEGIN
    RETURN QUERY EXECUTE FORMAT($sql$
        WITH query AS (%s) 
        SELECT agtype_build_list(query) 
        FROM query
    $sql$, query::text);
END
$$ LANGUAGE plpgsql VOLATILE;

Then in cypher is would call that function and use MERGE to create graph.

SELECT * FROM cypher('movies', $$
    UNWIND public.load_sql('
        SELECT * FROM movies
    ') AS row
    MERGE (v:MOVIE {title: row.title})
    SET v=row
    RETURN count(v)
$$) AS (v agtype);

[jrgemignani]

@mingfang I traced the code and found it skipping the insert for the latter tuple. This looks to be a transaction/command id issue.

[diangamichael]

The issue you're encountering is because of the way you're merging and setting properties in your Cypher query. When you use MERGE, it will only create a new node if it doesn't already exist with the specified properties. However, in your case, you're using SET after MERGE, which can overwrite existing properties on the node.

To fix this issue and ensure that properties are correctly set for all nodes, you should use ON CREATE SET in addition to SET. This will only set properties if the node is newly created during the MERGE operation.

Here's the corrected query:

SELECT * FROM cypher('playground', $$
UNWIND [{first: 'jon', last: 'snow'}, {first: 'ned', last: 'stark'}] AS map
MERGE (v:PERSON {first: map.first})
ON CREATE SET v=map
SET v.last = map.last
RETURN v
$$) AS (v agtype);
With this modification, each node will have its properties correctly set, even if they already exist in the database.

[jrgemignani]

@diangamichael Could you, in addition to your response, include an example using Apache AGE showing this solves the issue?

[jrgemignani]

@mingfang Yeah, it appears to be an issue with transaction/command ids. It likely has to do with the handoff between MERGE and SET as both are updates.