[CYPHER] MATCH + optional match (when >1 match in optional) stops at first match iteration

[camomiy]

Hello

ArcadeDB Version:

ArcadeDB Server v24.11.2 (build 055592c73d27d894c26f3faaf7df22e15c28f03d/1733838531445/main)

OS and JDK Version:

Running on Linux 5.15.0-105-generic - OpenJDK 64-Bit Server VM 17.0.13

Expected behavior

Here is a part of our graph :

match(n) 
Where n:DOCUMENT or n:CHUNK or n.subtype='LOCATION' 
return n

Long story short, with the visible chunks on this screenshot, we should be able using this query to find multiple DOCUMENT nodes.

MATCH (doc:DOCUMENT)
OPTIONAL MATCH (ner:NER {subtype: 'LOCATION'})<--(linkedChunk:CHUNK)-->(doc:DOCUMENT)
RETURN ID(doc), ID(ner)

Simple because in the screenshot, one NER is linked to two CHUNK nodes that are themselves linked to their own DOCUMENT node.

However, running this query only returns this table :

ID(doc)	ID(ner)
#25:0	#82:0
#25:0	#85:0
#25:0	#88:0
#25:0	#91:0

Removing {subtype: 'LOCATION'} filter from the query increases the results length but it still stops at the first found DOCUMENT.

I understand it can be confusing, but as a TL;DR, here is an example that there is something wrong going on :

---

MATCH (doc:DOCUMENT)
OPTIONAL MATCH (ner:NER {subtype: 'LOCATION'})<--(linkedChunk:CHUNK)-->(doc:DOCUMENT)
RETURN ID(doc)

(should match real things, all docs, it's an OPTIONAL match so even if it doesn't find anything, it should at least return as much as data as the MATCH (non optional) query alone)

ID(doc)
#25:0
#25:0
#25:0
#25:0

Yes, lines are duplicated because there are 4 NER nodes matching.

---

MATCH (doc:DOCUMENT)
OPTIONAL MATCH (:RANDOM_DUMMY_NON_EXISTING_TYPE {subtype: 'LOCATION'})<--(linkedChunk:CHUNK)-->(doc:DOCUMENT)
RETURN ID(doc)

ID(doc)
#25:0
#28:0
#31:0

---

TL;DR :

Even if there is no match on the OPTIONAL MATCH query, it should return a null value in the second column and still returns all the DOCUMENT nodes.

Here is the database backup dump :

POLAIRE2-backup-20250123-094141512.zip

[ExtReMLapin]

Feel free to ping me if you need any detail, we created this issue at the office

[robfrank]

I assigned this issue to the next 25.2.1 release.
Right now we are consolidating the work streams for 25.1.1.
The best way to help us in the triage process would be to provide a test in Java through a PR.
That said, if you cant't do that, the sample database and the queries are gold for us.