duckdb_post_execute can't handle CALL statements

[amoeba]

The internal function duckdb_post_execute seems to not handle CALL statements correctly. I'm not sure if other type of statements have similar issues.

For example,

library(duckdb)

con <- dbConnect(duckdb())

> dbExecute(con, "CALL duckdb_functions();")
[1] NA               
Warning message:
In duckdb_post_execute(res, out) : NAs introduced by coercion

I think the correct result should be [1] 0 and no warnings should be produced.

Another example would be in the Substrait extension:

library(duckdb)

con <- dbConnect(duckdb())

# setup
dbExecute(con, "INSTALL substrait FROM community;")
# => [1] 0
dbExecute(con, "LOAD substrait")
# => [1] 0                
dbExecute(con, "CREATE TABLE crossfit (exercise text, difficulty_level int);")
# => [1] 0
dbExecute(con, "INSERT INTO crossfit VALUES ('Push Ups', 3), ('Pull Ups', 5) , (' Push Jerk', 7), ('Bar Muscle Up', 10);")
# => [1] 4

# this call errors:
dbExecute(con, "CALL get_substrait('select count(exercise) as exercise from crossfit where difficulty_level <=5');")
# => Error in duckdb_post_execute(res, out) : 
#   'list' object cannot be coerced to type 'double'``

This fails because get_substrait returns a table with a BLOB column in this case with one row.

I'm not sure what the best fix would be here so I'm filing an issue. Happy to file a PR if there's a clear fix.

`devtools::session_info()` output

> devtools::session_info()
─ Session info ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.4.2 (2024-10-31)
 os       macOS Sequoia 15.3.1
 system   aarch64, darwin20
 ui       RStudio
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       America/Los_Angeles
 date     2025-02-27
 rstudio  2024.12.1+563 Kousa Dogwood (desktop)
 pandoc   3.6.3 @ /opt/homebrew/bin/pandoc

─ Packages ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version    date (UTC) lib source
 cachem        1.1.0      2024-05-16 [2] CRAN (R 4.4.0)
 cli           3.6.4      2025-02-13 [2] CRAN (R 4.4.1)
 DBI         * 1.2.3      2024-06-02 [2] CRAN (R 4.4.0)
 devtools      2.4.5      2022-10-11 [2] CRAN (R 4.4.0)
 digest        0.6.37     2024-08-19 [2] CRAN (R 4.4.1)
 duckdb      * 1.2.0      2025-02-21 [1] CRAN (R 4.4.1)
 ellipsis      0.3.2      2021-04-29 [2] CRAN (R 4.4.0)
 fastmap       1.2.0      2024-05-15 [2] CRAN (R 4.4.0)
 fs            1.6.5      2024-10-30 [2] CRAN (R 4.4.1)
 glue          1.8.0      2024-09-30 [2] CRAN (R 4.4.1)
 htmltools     0.5.8.1    2024-04-04 [2] CRAN (R 4.4.0)
 htmlwidgets   1.6.4      2023-12-06 [2] CRAN (R 4.4.0)
 httpuv        1.6.15     2024-03-26 [2] CRAN (R 4.4.0)
 later         1.3.2      2023-12-06 [2] CRAN (R 4.4.0)
 lifecycle     1.0.4      2023-11-07 [2] CRAN (R 4.4.0)
 magrittr      2.0.3      2022-03-30 [2] CRAN (R 4.4.0)
 memoise       2.0.1      2021-11-26 [2] CRAN (R 4.4.0)
 mime          0.12       2021-09-28 [2] CRAN (R 4.4.0)
 miniUI        0.1.1.1    2018-05-18 [2] CRAN (R 4.4.0)
 pkgbuild      1.4.5      2024-10-28 [2] CRAN (R 4.4.1)
 pkgload       1.4.0      2024-06-28 [2] CRAN (R 4.4.0)
 profvis       0.4.0      2024-09-20 [2] CRAN (R 4.4.1)
 promises      1.3.0      2024-04-05 [2] CRAN (R 4.4.0)
 purrr         1.0.4      2025-02-05 [2] CRAN (R 4.4.1)
 R6            2.6.1      2025-02-15 [2] CRAN (R 4.4.1)
 Rcpp          1.0.14     2025-01-12 [2] CRAN (R 4.4.1)
 remotes       2.5.0.9000 2025-02-25 [2] Github (r-lib/remotes@bcd35d5)
 rlang         1.1.5      2025-01-17 [2] CRAN (R 4.4.1)
 rstudioapi    0.17.0     2024-10-16 [2] CRAN (R 4.4.1)
 sessioninfo   1.2.2      2021-12-06 [2] CRAN (R 4.4.0)
 shiny         1.9.1      2024-08-01 [2] CRAN (R 4.4.0)
 urlchecker    1.0.1      2021-11-30 [2] CRAN (R 4.4.0)
 usethis       3.0.0      2024-07-29 [2] CRAN (R 4.4.0)
 vctrs         0.6.5      2023-12-01 [2] CRAN (R 4.4.0)
 xtable        1.8-4      2019-04-21 [2] CRAN (R 4.4.0)

 [1] /Users/bryce/Library/R/arm64/4.4/library
 [2] /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/library

─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

[krlmlr]

Thanks. We need to do better with dbExecute() and data-frame-returning SQL. Do you need dbGetQuery() ?

library(duckdb, quietly = TRUE)
library(dplyr, quietly = TRUE, warn.conflicts = FALSE)

con <- dbConnect(duckdb(), ":memory:")
dbGetQuery(con, "FROM duckdb_functions()") |>
  tibble::as_tibble()
#> # A tibble: 2,464 × 18
#>    database_name database_oid schema_name function_name      function_type
#>    <chr>         <chr>        <chr>       <chr>              <chr>        
#>  1 system        0            main        range              table        
#>  2 system        0            main        range              table        
#>  3 system        0            main        range              table        
#>  4 system        0            main        range              table        
#>  5 system        0            main        arrow_scan         table        
#>  6 system        0            main        arrow_scan_dumb    table        
#>  7 system        0            main        checkpoint         table        
#>  8 system        0            main        checkpoint         table        
#>  9 system        0            main        duckdb_columns     table        
#> 10 system        0            main        duckdb_constraints table        
#> # ℹ 2,454 more rows
#> # ℹ 13 more variables: description <chr>, comment <chr>, tags <list>,
#> #   return_type <chr>, parameters <list>, parameter_types <list>,
#> #   varargs <chr>, macro_definition <chr>, has_side_effects <lgl>,
#> #   internal <lgl>, function_oid <dbl>, examples <list>, stability <chr>

^{Created on 2025-03-07 with reprex v2.1.1}

[krlmlr]

Checked the other example too, dbGetQuery() works for me:

library(duckdb)
#> Loading required package: DBI

con <- dbConnect(duckdb())

dbExecute(con, "INSTALL substrait FROM community;")
#> [1] 0
dbExecute(con, "LOAD substrait")
#> [1] 0
dbExecute(con, "CREATE TABLE crossfit (exercise text, difficulty_level int);")
#> [1] 0
dbExecute(con, "INSERT INTO crossfit VALUES ('Push Ups', 3), ('Pull Ups', 5) , (' Push Jerk', 7), ('Bar Muscle Up', 10);")
#> [1] 4

out <- dbGetQuery(con, "CALL get_substrait('select count(exercise) as exercise from crossfit where difficulty_level <=5');")
tibble::as_tibble(out)
#> # A tibble: 1 × 1
#>   `Plan Blob`
#>   <list>     
#> 1 <raw [372]>

^{Created on 2025-03-13 with reprex v2.1.1}

Fixing the confusing output with dbExecute(con, "CALL ...") .

[amoeba]

Thanks @krlmlr, and sorry I missed your earlier reply. I appreciate the quick response and fix!

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.