`compute()` throws error but the temp table is actually created.

[Steven314]

Issue

When calling compute() my understanding is that it creates a temporary table which can optionally be named using the name argument, then this table can be later called as any other table in DuckDB. When using this to query DuckDB (persistent or in memory), compute() throws an error, claiming the resulting temporary table does not exist. However, the temporary table looks to created and is available.

Also, a similar error happens when running the segment below ending in compute(name = "test1") a second time. It says the table already exists, which would be correct but it may be nice to have an overwrite option.

Use Case

My use case is in a Shiny app (that lives within a package) which connects to a persistent DuckDB database file. I am hoping to "cache" some calculations using these computed temporary tables to allow passing data between Shiny modules without passing several reactive objects.
I suppose I could create a temporary table through SQL like the docs show if my understanding of compute() is totally off.

reprex

library(duckdb)
#> Loading required package: DBI
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(duckplyr)
#> The duckplyr package is configured to fall back to dplyr when it encounters an
#> incompatibility. Fallback events can be collected and uploaded for analysis to
#> guide future development. By default, data will be collected but no data will
#> be uploaded.
#> ℹ Automatic fallback uploading is not controlled and therefore disabled, see
#>   `?duckplyr::fallback()`.
#> ✔ Number of reports ready for upload: 10.
#> → Review with `duckplyr::fallback_review()`, upload with
#>   `duckplyr::fallback_upload()`.
#> ℹ Configure automatic uploading with `duckplyr::fallback_config()`.
#> ✔ Overwriting dplyr methods with duckplyr methods.
#> ℹ Turn off with `duckplyr::methods_restore()`.
#> 
#> Attaching package: 'duckplyr'
#> The following object is masked from 'package:duckdb':
#> 
#>     read_csv_duckdb

# create persistent db
con <- dbConnect(duckdb(), "test.duckdb")

# add table to persistent db
mtcars |>
  as_tibble(rownames = "model") %>%
  dbWriteTable(con, "mt", ., overwrite = TRUE)

# give a name in compute:
read_sql_duckdb(sql = "select * from mt where mpg >= 20", con = con) |>
  compute(name = "test1")
#> Error processing duckplyr query with DuckDB, falling back to dplyr.
#> Caused by error in `compute()`:
#> ! {"exception_type":"Catalog","exception_message":"Table test1 does not exist!"}
#> Error in `compute()`:
#> ! {"exception_type":"Catalog","exception_message":"Table test1 does not exist!"}

# named table works:
tbl(con, "test1")
#> # Source:   table<test1> [?? x 12]
#> # Database: DuckDB v1.3.0 [...@Windows 10 x64:R 4.4.3/C:\Users\...\AppData\Local\Temp\RtmpyqfkMl\reprex-36fc5eb65303-mid-pig\test.duckdb]
#>    model         mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1 Mazda RX4    21       6 160     110  3.9   2.62  16.5     0     1     4     4
#>  2 Mazda RX4 …  21       6 160     110  3.9   2.88  17.0     0     1     4     4
#>  3 Datsun 710   22.8     4 108      93  3.85  2.32  18.6     1     1     4     1
#>  4 Hornet 4 D…  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1
#>  5 Merc 240D    24.4     4 147.     62  3.69  3.19  20       1     0     4     2
#>  6 Merc 230     22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2
#>  7 Fiat 128     32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1
#>  8 Honda Civic  30.4     4  75.7    52  4.93  1.62  18.5     1     1     4     2
#>  9 Toyota Cor…  33.9     4  71.1    65  4.22  1.84  19.9     1     1     4     1
#> 10 Toyota Cor…  21.5     4 120.     97  3.7   2.46  20.0     1     0     3     1
#> 11 Fiat X1-9    27.3     4  79      66  4.08  1.94  18.9     1     1     4     1
#> 12 Porsche 91…  26       4 120.     91  4.43  2.14  16.7     0     1     5     2
#> 13 Lotus Euro…  30.4     4  95.1   113  3.77  1.51  16.9     1     1     5     2
#> 14 Volvo 142E   21.4     4 121     109  4.11  2.78  18.6     1     1     4     2

^{Created on 2025-06-23 with reprex v2.1.1}

Session info

sessioninfo::session_info()
#> Warning in system2("quarto", "-V", stdout = TRUE, env = paste0("TMPDIR=", :
#> running command '"quarto"
#> TMPDIR=C:/Users/.../AppData/Local/Temp/RtmpiAqoLx/file951417701dae -V'
#> had status 1
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.4.3 (2025-02-28 ucrt)
#>  os       Windows 11 x64 (build 26100)
#>  system   x86_64, mingw32
#>  ui       RTerm
#>  language (EN)
#>  collate  English_United States.utf8
#>  ctype    English_United States.utf8
#>  tz       America/New_York
#>  date     2025-06-23
#>  pandoc   3.4 @ C:/Program Files/RStudio/resources/app/bin/quarto/bin/tools/ (via rmarkdown)
#>  quarto   NA @ C:\\Users\\...\\AppData\\Local\\Programs\\Quarto\\bin\\quarto.exe
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  ... (removed for brevity)
#>  DBI         * 1.2.3   2024-06-02 [1] CRAN (R 4.4.1)
#>  dbplyr        2.5.0   2024-03-19 [1] CRAN (R 4.4.1)
#>  dplyr       * 1.1.4   2023-11-17 [1] CRAN (R 4.4.1)
#>  duckdb      * 1.3.0   2025-06-02 [1] CRAN (R 4.4.3)
#>  duckplyr    * 1.1.0   2025-05-08 [1] CRAN (R 4.4.3)
#>  ...
#>  reprex        2.1.1   2024-07-06 [1] CRAN (R 4.4.1)
#>  ...
#>  sessioninfo   1.2.3   2025-02-05 [1] CRAN (R 4.4.2)
#>  tibble        3.3.0   2025-06-08 [1] CRAN (R 4.4.3)
#>  tidyselect    1.2.1   2024-03-11 [1] CRAN (R 4.4.1)
#>  ...
#>  [1] C:/Users/.../AppData/Local/R/win-library/4.4
#>  * ── Packages attached to the search path.
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[krlmlr]

Thanks. It turns out that the con argument to read_sql_duckdb() is even less useful than anticipated. Please ATTACH any database to duckplyr's internal connection:

options(conflicts.policy = list(warn = FALSE))
library(duckdb)
#> Loading required package: DBI
library(dplyr)
library(duckplyr)
#> ✔ Overwriting dplyr methods with duckplyr methods.
#> ℹ Turn off with `duckplyr::methods_restore()`.

# create persistent db
con <- dbConnect(duckdb(), "test.duckdb")

# add table to persistent db
mtcars |>
  as_tibble(rownames = "model") %>%
  dbWriteTable(con, "mt", ., overwrite = TRUE)

dbDisconnect(con)

# connect to the persistent db
db_exec("ATTACH './test.duckdb' AS test")

# give a name in compute:
read_sql_duckdb(sql = "select * from test.mt where mpg >= 20") |>
  compute(name = "test1")
#> # A duckplyr data frame: 12 variables
#>    model         mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1 Mazda RX4    21       6 160     110  3.9   2.62  16.5     0     1     4     4
#>  2 Mazda RX4 …  21       6 160     110  3.9   2.88  17.0     0     1     4     4
#>  3 Datsun 710   22.8     4 108      93  3.85  2.32  18.6     1     1     4     1
#>  4 Hornet 4 D…  21.4     6 258     110  3.08  3.22  19.4     1     0     3     1
#>  5 Merc 240D    24.4     4 147.     62  3.69  3.19  20       1     0     4     2
#>  6 Merc 230     22.8     4 141.     95  3.92  3.15  22.9     1     0     4     2
#>  7 Fiat 128     32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1
#>  8 Honda Civic  30.4     4  75.7    52  4.93  1.62  18.5     1     1     4     2
#>  9 Toyota Cor…  33.9     4  71.1    65  4.22  1.84  19.9     1     1     4     1
#> 10 Toyota Cor…  21.5     4 120.     97  3.7   2.46  20.0     1     0     3     1
#> 11 Fiat X1-9    27.3     4  79      66  4.08  1.94  18.9     1     1     4     1
#> 12 Porsche 91…  26       4 120.     91  4.43  2.14  16.7     0     1     5     2
#> 13 Lotus Euro…  30.4     4  95.1   113  3.77  1.51  16.9     1     1     5     2
#> 14 Volvo 142E   21.4     4 121     109  4.11  2.78  18.6     1     1     4     2

^{Created on 2025-06-27 with reprex v2.1.1}

[Steven314]

Thank you for the workaround. I ended up creating temp tables through SQL extracted from {dbplyr} for the time being. I will probably convert it to {duckplyr} at some point.

Trying the workaround with an in-memory database, out of curiosity, doesn't work. I'm guessing dbDisconnect(con) destroys the in-memory db, so then ATTACH ':memory:' AS test is making a fresh db which of course doesn't have any table from the prior db.