Restriction of {0,0} returns the same as {1,1}

[Dtenwolde]

What happens?

see title

To Reproduce

create table student(id INT); INSERT INTO student(id) VALUES (10), (20), (30), (40);
create table know(src INT, dst INT); INSERT INTO know(src, dst) VALUES (40, 20), (10,30), (10,10), (20,10), (30,10);

-create property graph g vertex tables (student) edge tables (know source key (src) references student (id) destination key (dst) references student (id)); 

-FROM GRAPH_TABLE (g
    MATCH
    ANY SHORTEST (a:student)-[k:know]->{0,0}(b:student)
    WHERE a.id = 10
    COLUMNS (a.id as a_id, b.id as b_id)
    );

Actual result:

┌───────┬───────┐
│ a_id  │ b_id  │
│ int32 │ int32 │
├───────┼───────┤
│    10 │    10 │
│    10 │    30 │
└───────┴───────┘

Expected result:

┌───────┬───────┐
│ a_id  │ b_id  │
│ int32 │ int32 │
├───────┼───────┤
│    10 │    10 │
└───────┴───────┘

### OS:

macOs 13 - Apple M1 Pro

### DuckDB Version:

dev

### DuckDB Client:

CLI

### Full Name:

Daniel ten Wolde

### Affiliation:

CWI

### Have you tried this on the latest `master` branch?

- [X] I agree

### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

- [X] I agree

[Dtenwolde]

I've started looking into this issue, but I don't know what the PGQ document uses for path semantics. If it is strict, then no edge traversals occur and the expected result would be the empty set. If it is relaxed semantics, then the result could be only the source node. I will ask next week at the TUC meeting.

[Dtenwolde]

Started a branch here: https://github.com/cwida/duckpgq-extension/tree/issue/9-restriction-of-00-returns-the-same-as-11

[Dtenwolde]

The following two queries get equal results, just the SELECT clause is slightly different, but that is a detail. I think moving the {0,1} pattern to a CTE would be a good approach to translating the query. I use a subquery in the following example, but the translation to a CTE is trivial:

select * from graph_table (pg match (a is person)-[k is knows]->{0,1}(b is person)-[k2 is knows]->(c is person)  columns (a.id as a_id, a.name as a_name, b.id as b_id, b.name as b_name, c.id as c_id));

select * from (select a.*, b.* from person a, knows k, person b where a.id = k.person1id and b.id = k.person2id UNION ALL select a.*, b.* from person a, person b where a.rowid = b.rowid) join knows k2 on id_1 = k2.person1id join person c on c.id = k2.person2id;

Tested this with Oracle 23ai and got the same results