First MERGE does not see the second MERGE's changes

[rafsun42]

Describe the bug
In a chained MERGE query with two MERGE clauses, the first MERGE does not see the updates of the second one.

What data setup do we need to do?

SELECT create_graph('breakmerge');
SELECT * FROM cypher('breakmerge', $$
    CREATE (:A), (:C)
$$) as (a agtype);

What is the command that caused the error?

SELECT * FROM cypher('breakmerge', $$
    MATCH (x)
    MERGE (x)-[:r]->(:t)
    MERGE (:C)-[:r]->(:t)
$$) as (a agtype);

Outputs:

                                a                                 
------------------------------------------------------------------
 {"id": 844424930131969, "label": "A", "properties": {}}::vertex
 {"id": 1125899906842625, "label": "C", "properties": {}}::vertex
 {"id": 1125899906842626, "label": "C", "properties": {}}::vertex
 {"id": 1125899906842627, "label": "C", "properties": {}}::vertex
(4 rows)

Expected behavior
There should be only one instance of :A and :C (verified in Neo4j).

                                a                                 
------------------------------------------------------------------
 {"id": 844424930131969, "label": "A", "properties": {}}::vertex
 {"id": 1125899906842625, "label": "C", "properties": {}}::vertex

Environment (please complete the following information):
Master branch; PG 15.4

Additional context
Maybe related to how command IDs are used in MERGE's executor.

My assumption: when the MATCH node sends :A to the first MERGE node it creates (:A)-[:r]->(:t) and the second MERGE node creates (:C)-[:r]->(:t). In the next iteration, MATCH sends :C to the first MERGE, but it does not see that the second MERGE has already created (:C)-[:r]->(:t) in the previous iteration.

[jrgemignani]

@rafsun42 Interesting. It is still an issue in the latest master.

[github-actions]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[jrgemignani]

@rafsun42 If you think about it as a transaction - each iteration of MERGE having its own commandId, but working off of the MATCH part at the beginning, then how could the first see the last? I tend to question Neo4j's result here, just a bit, as it sounds like they group the 2 MERGEs as 1 transaction. Or, am I missing something?

[rafsun42]

@jrgemignani It might be possible. I will take a look at their query plan to see how the data flows.

[gregfelice]

I've submitted a fix for this in PR #2344.

Root cause: When chained MERGEs appear (e.g. MATCH (x) MERGE (x)-[:r]->(:t) MERGE (:C)-[:r]->(:t)), the execution plan nests them as MERGE2 → NestLoop → MERGE1. The non-terminal MERGE1 returned rows one at a time, so MERGE2's lateral join (Hash Join) materialized its hash table on the first row — before MERGE1 had finished all its iterations. Entities created by MERGE1 in subsequent iterations were invisible to the already-materialized hash table, causing MERGE2 to create duplicates.

Fix: Make non-terminal MERGE eager — it processes ALL input rows and buffers the projected results before returning any to the parent. This ensures all entity creations are committed before any parent plan node scans the tables.

Verified with the exact reproduction case from this issue: returns 2 rows (A, C) with correct graph state (1 A, 1 C, 2 t nodes, 2 edges). All 31 regression tests pass.