With R the extensions cannot return paths

[ekianjo]

What happens?

When executing with a dbSendQuery in R the statement in the docs:

https://duckpgq.org/documentation/sql_pgq/

FROM GRAPH_TABLE (snb
    MATCH p = ANY SHORTEST (a:Person WHERE a.firstName = 'Jan')-[k:knows]->+(b:Person)
    COLUMNS (element_id(p), vertices(p), edges(p), path_length(p), b.firstName)
  )
ORDER BY firstName
LIMIT 5;

this does not work and returns an error:

Error in duckdb_post_execute(res, out) :
'list' object cannot be coerced to type 'double'

To Reproduce

to reproduce

library(duckdb)

con <- dbConnect(duckdb(),"mydb_yo.duckdb")
dbExecute(con, "INSTALL duckpgq from community")
dbExecute(con, "LOAD duckpgq")
dbExecute(con, "INSTALL httpfs")
dbExecute(con, "LOAD httpfs")
dbExecute(con, "CREATE TABLE Person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv'")
dbExecute(con, "CREATE TABLE Person_knows_person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/81c32c9002d4059c2c3073dbca155275/raw/8b440e810a48dcaa08c07086e493ec0e2ec6b3cb/person_knows_person-sf0.003.csv'")

statement <- "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
  )"


res <- dbSendQuery(con, "FROM GRAPH_TABLE (snb
    MATCH p = ANY SHORTEST (a:Person WHERE a.firstName = 'Jan')-[k:knows]->+(b:Person)
    COLUMNS (element_id(p), vertices(p), edges(p), path_length(p), b.firstName)
  )
ORDER BY firstName
LIMIT 5")

OS:

Linux Ubuntu

DuckDB Version:

22.04

DuckDB Client:

R

Full Name:

Ekianjo

Affiliation:

Private User

How did you load the extension?

Latest 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]

Thanks for the bug report! It seems that the functions that are supposed to return a list (such as vertices and element_id) are trying to return a double. I'll look into why this is happening.