Bug: Combining unwind with merge returns wrong number of tuples

[andreasdamm-shure]

Ladybug version

0.15.1

What operating system are you using?

MacOS Tahoe 26.3.1

What happened?

When running a merge clause from an unwind statement repeatedly merging the same node, only one tuple is returned instead of one tuple per unwound list member.

Are there known steps to reproduce?

Using the Kuzu CLI run the following queries

create node table A(id string default gen_random_uuid(), stuff int8, primary key (id));

unwind [1, 1, 1] as i
      merge (a:A { stuff: i })
      return a.id;

The output should look like

┌──────────────────────────────────────┐
│ a.id                                 │
│ STRING                               │
├──────────────────────────────────────┤
│ 412dcddf-0f53-4c02-aea0-645f14037e0f │
│ 412dcddf-0f53-4c02-aea0-645f14037e0f │
│ 412dcddf-0f53-4c02-aea0-645f14037e0f │
└──────────────────────────────────────┘

The output should return the same id three times, instead the output is

┌──────────────────────────────────────┐
│ a.id                                 │
│ STRING                               │
├──────────────────────────────────────┤
│ 412dcddf-0f53-4c02-aea0-645f14037e0f │
└──────────────────────────────────────┘

[aheev]

UNWIND is working perfectly fine(see below). I think something's wrong with UNWIND + RETURN when there're duplicates involved in the result

lbug> unwind [1, 2, 3] as i WITH i
            merge (a:A { stuff: i })
            return a.id;
┌──────────────────────────────────────┐
│ a.id                                 │
│ STRING                               │
├──────────────────────────────────────┤
│ 528922fd-3126-471a-97c5-7a592c73b085 │
│ 967bf99e-ae92-4e9f-9198-73f70af1c04b │
│ d18b9e22-cc62-4206-8c5b-bf71bef7791e │
└──────────────────────────────────────┘

[aheev]

We might need to tweak merge.cpp, pattern_creation_info_table.cpp, insert_executor.cpp

[adsharma]

More context in #171

[adsharma]

I thought about it a bit. There is no spec that says we should return 3 rows for this case. Returning a single row is defensible based on duckdb's behavior:

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
     id INTEGER PRIMARY KEY DEFAULT nextval('users_id_seq'),
     name VARCHAR
);
WITH src AS (SELECT unnest([1, 1, 1]) AS id, 'John' AS name)
INSERT INTO users SELECT * FROM src ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
RETURNING id;

┌───────┐
│  id   │
│ int32 │
├───────┤
│     1 │
└───────┘

Also, we need to think about:

unwind [1, 2, 2, 1, 1] as i
      merge (a:A { stuff: i })
      return a.id

We correctly return 2 rows instead of 5. Suggest handling this in the application.

[andreasdamm-shure]

In the documentation (https://docs.ladybugdb.com/cypher/data-manipulation-clauses/merge/) it suggests that MERGE should behave like MATCH when the record exists already:

MERGE can be interpreted as If MATCH then RETURN ELSE CREATE .

When making sure the entry has been created already, and then replacing MERGE with MATCH, as many records as are in the unwound list are returned (i.e. even if they are duplicates).

Removing duplicates would make MERGE used with UNWIND fairly unpredictable, the unwound item might be used by another clause after the MERGE to create nodes and rels based of each item being unwound which wouldn't work if some items are suppressed.

[andreasdamm-shure]

Tested similar query with Neo4j and it returns the expected output of three tuples. Would be interesting to see what https://www.iso.org/standard/76120.html has to say about it.

[adsharma]

The problem with the neo4j comparison is that it was traditionally schema-less and had no notion of such constraints. It's been added in recent versions. But there is no user base that depends on it and I don't know how much thought has been put into it.

GQL would be a valid comparison point. But it's spec is not public and not clear if we can test against any implementation with a significant user base.

For now, DuckDB is the best reference I have for such queries. The behavior is compatible with DuckDB.

Thinking about it from the app perspective, I empathize with what you're saying. If unwind has N elements, expecting N rows sounds natural. The only question is where should it be implemented? Inside the database or in a layer above the database.

Based on my past experience working on the kernel, the principle is: if something can be implemented in the application to keep the kernel simple, it should be. This smells like one of those things to me.