When executing the following Cypher queries, incorrect results are returned.

[abc123000111]

I discovered that when I executed the following queries, only one record (1, null, null) is returned.

CREATE (n0 :L1{k6:248034764, k10:false});
CREATE (n1 :L2{k15:248034764, k16:'NWYfUF'});
CREATE (n2 :L0{k2:false, k4:true});
CREATE (n3 :L1{k7:false, k10:false});
CREATE (n4 :L2{k16:'CMx'});
CREATE (n5 :L2{k14:248034764});
CREATE (n6 :L1);
CREATE (n7 :L4);
CREATE (n8 :L4{k24:790915693});
CREATE (n9 :L1{k7:false, k9:false, k10:false});
CREATE (n10 :L1{k10:true});
CREATE (n11 :L1);
CREATE (n12 :L1{k6:1787778361});
CREATE (n13 :L4);
CREATE (n14 :L2{k13:'UI', k15:790915693});
CREATE (n15 :L4{k24:1787778361, k26:1136957655});
CREATE (n16 :L0{k2:true});
CREATE (n17 :L3);
MATCH (n0 :L1{k6:248034764, k10:false}), (n12 :L1{k6:1787778361}) MERGE (n0)<-[r0 :T3{k54:'TAA'}]-(n12);
MATCH (n1 :L2{k15:248034764, k16:'NWYfUF'}), (n9 :L1{k7:false, k9:false, k10:false}) MERGE (n1)<-[r1 :T1{k40:790915693}]-(n9);
MATCH (n2 :L0{k2:false, k4:true}), (n11 :L1) MERGE (n2)<-[r2 :T3{k54:'NkomQghhS'}]-(n11);
MATCH (n5 :L2{k14:248034764}), (n16 :L0{k2:true}) MERGE (n5)-[r3 :T2{k49:'RA5gg8Fl'}]->(n16);
MATCH (n9 :L1{k7:false, k9:false, k10:false}), (n5 :L2{k14:248034764}) MERGE (n9)<-[r4 :T0{k32:1787778361}]-(n5);
MATCH (n0) OPTIONAL MATCH (n1 :L2)-[r0 *]-(n2 :L3) OPTIONAL MATCH (n3 :L1),(n1) RETURN DISTINCT 1 AS a0, (n3.k10) AS a1, (n2.k19) AS a2;

But it should return the three records as:
(1, false, null)
(1, null, null)
(1, true, null)

Neo4j Version: 4.3.10, 4.2.14 Community
Operating System: Windows 11
API: Cypher / Java API

Steps to reproduce

1. Start Neo4j
2. Execute the mentioned queries
3. The wrong results are returned.

Expected behavior

Three records:
(1, false, null)
(1, null, null)
(1, true, null)

Actual behavior

One record:
(1, null, null)

[HannesSandberg]

Thanks for this report! We will look into the issue and come back to you.

[inmost-light]

Hello @abc123000111,

I believe this is actually working as intended:

• There are no patterns matching OPTIONAL MATCH (n1 :L2)-[r0 *]-(n2 :L3), therefore n1 is always set to NULL.
• For OPTIONAL MATCH (n3 :L1),(n1) to match anything, all pattern parts must match something. Since n1 is NULL, it can't ever match anything, therefore n3 is also always NULL.

To get your expected result, you need to break the second optional match into OPTIONAL MATCH (n3 :L1) OPTIONAL MATCH (n1).

[abc123000111]

Hello @abc123000111,

I believe this is actually working as intended:

• There are no patterns matching OPTIONAL MATCH (n1 :L2)-[r0 *]-(n2 :L3), therefore n1 is always set to NULL.
• For OPTIONAL MATCH (n3 :L1),(n1) to match anything, all pattern parts must match something. Since n1 is NULL, it can't ever match anything, therefore n3 is also always NULL.

To get your expected result, you need to break the second optional match into OPTIONAL MATCH (n3 :L1) OPTIONAL MATCH (n1).

Thank you! I found that the results returned by Neo4j and Memgraph are inconsistent, so I commit this issue on Memgraph Github (memgraph/memgraph#363).