Does Match (All) With Variable Path Work?

[alexander-alvarez]

What happens?

Going off of an example from https://pgql-lang.org/spec/2.0/#all-path but with the finbench data

SELECT *
FROM GRAPH_TABLE (finbench
    MATCH (a:Account) -[e:Transfer]-> (a2:Account)
    KEEP ALL WHERE a.accountId = 81064793292669306
    COLUMNS (e)
)
LIMIT 10

Returns 4 records

Adding a variable path

SELECT *
FROM GRAPH_TABLE (finbench
    MATCH (a:Account) -[e:Transfer]->{,3} (a2:Account)
    KEEP ALL WHERE a.accountId = 81064793292669306
    COLUMNS (e)
)
GROUP BY ALL
LIMIT 10

Gives an exception

BinderException: Binder Error: Referenced column "e" not found in FROM clause!
Candidate bindings: "a.email", "a2.email", "temp", "a.createTime", "a2.createTime"

FWIW swapping COLUMNS (e) -> COLUMNS (temp) yields

BinderException: Binder Error: Property temp is never registered!

To Reproduce

ATTACH 'https://blobs.duckdb.org/data/finbench.duckdb' AS finbench;
USE finbench;

INSTALL duckpgq FROM community; LOAD duckpgq;

CREATE PROPERTY GRAPH finbench
VERTEX TABLES (
    Person,
    Account
)
EDGE TABLES (
    AccountTransferAccount
        SOURCE KEY (fromId) REFERENCES Account (accountId)
        DESTINATION KEY (toId) REFERENCES Account (accountId)
        LABEL Transfer,
    PersonOwnAccount
        SOURCE KEY (personId) REFERENCES Person (personId)
        DESTINATION KEY (accountId) REFERENCES Account (accountId)
        LABEL PersonOwn
);


SELECT *
FROM GRAPH_TABLE (finbench
    MATCH (a:Account) -[e:Transfer]->{,3} (a2:Account)
    KEEP ALL WHERE a.accountId = 81064793292669306
    COLUMNS (e)
)
LIMIT 10

OS:

macOS 15.6.1

DuckDB Version:

1.3.2

DuckDB Client:

v1.3.2 (Ossivalis) 0b83e5d2f6

Full Name:

Alex Alvarez

Affiliation:

Pato Solutions LLC

How did you load the extension?

Community extension version

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[Dtenwolde]

Hi there,
The binder error is not very helpful, that should be improved.
The reason why you get the error is that whenever you have a variable length path: -[e]->{,3}, you cannot reference any of the columns of e, because e is potentially of variable length. If you want to return the edges on the path you can use:

FROM GRAPH_TABLE (finbench
    MATCH p = (a:Account) -[e:Transfer]->{,3} (a2:Account)
    KEEP ALL WHERE a.accountId = 81064793292669306
    COLUMNS (edges(p))
)

Which will return a list of rowids related to the edge table of e. Which you can translate back to get the column information. It's a bit of a hassle and I need to improve the way this is done, but the error you are seeing is somewhat expected :)
I hope that helps,
Kind regards,
Daniel

[alexander-alvarez]

Thanks so much for the insight and quick response @Dtenwolde .

That seems to be what I need. My follow-up question would be is this a deviation from the spec?

SQL/PGQ is new to me, so I’m learning from the spec website and would expect being able to copy and paste their examples would work.