Unable to use duckpgq in DuckDB its `query` table function

[JelteF]

What happens?

When I use a duckpgq query inside a call to DuckDB its query table function it throws an error that it expects a single SELECT statement:

FROM query('
  FROM GRAPH_TABLE (snb
    MATCH (a:Person)-[k:knows]->(b:Person)
    COLUMNS (a.id, b.id)
  )
  LIMIT 1
');

Parser Error:
Expected a single SELECT statement

To Reproduce

INSTALL duckpgq FROM community;
LOAD duckpgq;
CREATE TABLE Person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv';
CREATE TABLE Person_knows_person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/81c32c9002d4059c2c3073dbca155275/raw/8b440e810a48dcaa08c07086e493ec0e2ec6b3cb/person_knows_person-sf0.003.csv';

CREATE PROPERTY GRAPH snb
  VERTEX TABLES (
    Person
  )
  EDGE TABLES (
    Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
                        DESTINATION KEY (Person2Id) REFERENCES Person (id)
    LABEL knows
  );

-- works
FROM GRAPH_TABLE (snb
  MATCH (a:Person)-[k:knows]->(b:Person)
  COLUMNS (a.id, b.id)
)
LIMIT 1;
-- fails
FROM query('
  FROM GRAPH_TABLE (snb
    MATCH (a:Person)-[k:knows]->(b:Person)
    COLUMNS (a.id, b.id)
  )
  LIMIT 1
');

For context on why I'm interested in this: By supporting the query table function duckdpgq would work pretty much automatically in pg_duckdb.

OS:

Linux

DuckDB Version:

1.2.0

DuckDB Client:

cli

Full Name:

Jelte Fennema-Nio

Affiliation:

MotherDuck

How did you load the extension?

Latest version

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

Yes

Did you include all code required to reproduce the issue?

• [x] Yes, I have

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

• [x] Yes, I have

[Dtenwolde]

Thanks for the bug report! I'm gonna look into this because it would be could to get this to also work with pg_duckdb :)

[Dtenwolde]

For future me:

Branches: https://github.com/cwida/duckpgq-extension/tree/issue/210-unable-to-use-duckpgq-in-duckdb-its-query-table-function https://github.com/cwida/duckdb-pgq/tree/issue/210-unable-to-use-duckpgq-in-duckdb-its-query-table-function

Current status: Parsing is all going correctly but at the binder step we are missing the argument that matches the duckpgq_match table function to the correct match data. This might require a larger refactor where instead of storing an index of the corresponding match info (and then storing the match info in a vector in the DuckPGQState). Instead, we could store the match info serialized as a blob argument in the duckpgq_match, which no longer requires storing this index.