dplyr::copy_to() does not respect overwrite

[lgaborini]

I can't understand how dplyr::copy_to() is implemented for this backend.
Partly related to #209.
It seems I cannot overwrite existing tables even if I tell duckdb to do so:

The fixture:

library(duckdb)
library(dplyr)

f_db <- withr::local_tempfile()
con_file <- DBI::dbConnect(duckdb::duckdb(f_db))

DBI::dbWriteTable(con_file, "table", mtcars, overwrite = TRUE, temporary = FALSE)
DBI::dbWriteTable(con_file, "table2", faithful, overwrite = TRUE, temporary = FALSE)

tbl_mtcars <- dplyr::tbl(con_file, "table")
tbl_mtcars
#> # Source:   table<"table"> [?? x 11]
#> # Database: DuckDB v1.1.3-dev165 [user@Windows 10 x64:R 4.4.1/C:\Users\user\AppData\Local\Temp\RtmpEhn4GF\file40e46011b49]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4
#>  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
#>  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
#>  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
#>  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
#> # ℹ more rows

tbl_faithful <- dplyr::tbl(con_file, "table2")
tbl_faithful
#> # Source:   table<table2> [?? x 2]
#> # Database: DuckDB v1.1.3-dev165 [user@Windows 10 x64:R 4.4.1/C:\Users\user\AppData\Local\Temp\RtmpEhn4GF\file40e46011b49]
#>    eruptions waiting
#>        <dbl>   <dbl>
#>  1      3.6       79
#>  2      1.8       54
#>  3      3.33      74
#>  4      2.28      62
#>  5      4.53      85
#>  6      2.88      55
#>  7      4.7       88
#>  8      3.6       85
#>  9      1.95      51
#> 10      4.35      85
#> # ℹ more rows

The issue:

# overwrite = FALSE → should fail

dplyr::copy_to(
  con_file,
  tbl_faithful,
  name = "table",
  overwrite = FALSE,
  temporary = FALSE
)
#> Error in `db_compute()`:
#> ! Can't copy query to table "table".
#> Caused by error in `db_save_query.DBIConnection()`:
#> ! Can't save query to table "table".
#> ℹ Using SQL: CREATE TABLE "table" AS SELECT * FROM table2
#> Caused by error in `duckdb_result()`:
#> ! rapi_execute: Failed to run query
#> Error: Catalog Error: Table with name "table" already exists!

# overwrite = TRUE → should succeed

dplyr::copy_to(
  con_file,
  tbl_faithful,
  name = "table",
  overwrite = TRUE,
  temporary = FALSE
)
#> Error in `db_compute()`:
#> ! Can't copy query to table "table".
#> Caused by error in `db_save_query.DBIConnection()`:
#> ! Can't save query to table "table".
#> ℹ Using SQL: CREATE TABLE "table" AS SELECT * FROM table2
#> Caused by error in `duckdb_result()`:
#> ! rapi_execute: Failed to run query
#> Error: Catalog Error: Table with name "table" already exists!

^{Created on 2024-11-29 with reprex v2.1.1}

The behavior changes with temporary tables (starting from scratch):

# it seems that `name` is ignored?
# expected? as temp tables have different names

tbl_mtcars_new <- dplyr::copy_to(
  con_file,
  tbl_faithful,
  name = "table",
  overwrite = FALSE,
  temporary = TRUE
)

tbl_mtcars_new
#> # Source:   table<"table"> [?? x 2]
#> # Database: DuckDB v1.1.3-dev165 [user@Windows 10 x64:R 4.4.1/C:\Users\user\AppData\Local\Temp\RtmpE5CRQr\file40443b8c2184]
#>    eruptions waiting
#>        <dbl>   <dbl>
#>  1      3.6       79
#>  2      1.8       54
#>  3      3.33      74
#>  4      2.28      62
#>  5      4.53      85
#>  6      2.88      55
#>  7      4.7       88
#>  8      3.6       85
#>  9      1.95      51
#> 10      4.35      85
#> # ℹ more rows

# Forcing computation creates a temp table with another name:

tbl_mtcars_new |> dplyr::compute()
#> # Source:   table<dbplyr_4rRMAsZtXC> [?? x 2]
#> # Database: DuckDB v1.1.3-dev165 [user@Windows 10 x64:R 4.4.1/C:\Users\user\AppData\Local\Temp\RtmpE5CRQr\file40443b8c2184]
#>    eruptions waiting
#>        <dbl>   <dbl>
#>  1      3.6       79
#>  2      1.8       54
#>  3      3.33      74
#>  4      2.28      62
#>  5      4.53      85
#>  6      2.88      55
#>  7      4.7       88
#>  8      3.6       85
#>  9      1.95      51
#> 10      4.35      85
#> # ℹ more rows

# Now further calls fail 

dplyr::copy_to(
  con_file,
  tbl_faithful,
  name = "table",
  overwrite = FALSE,
  temporary = TRUE
)
#> Error in `db_compute()`:
#> ! Can't copy query to table "table".
#> Caused by error in `db_save_query.DBIConnection()`:
#> ! Can't save query to table "table".
#> ℹ Using SQL: CREATE TEMPORARY TABLE "table" AS SELECT * FROM table2
#> Caused by error in `duckdb_result()`:
#> ! rapi_execute: Failed to run query
#> Error: Catalog Error: Table with name "table" already exists!

# Even by forcing overwriting

dplyr::copy_to(
  con_file,
  tbl_faithful,
  name = "table",
  overwrite = TRUE,
  temporary = TRUE
)
#> Error in `db_compute()`:
#> ! Can't copy query to table "table".
#> Caused by error in `db_save_query.DBIConnection()`:
#> ! Can't save query to table "table".
#> ℹ Using SQL: CREATE TEMPORARY TABLE "table" AS SELECT * FROM table2
#> Caused by error in `duckdb_result()`:
#> ! rapi_execute: Failed to run query
#> Error: Catalog Error: Table with name "table" already exists!

^{Created on 2024-11-29 with reprex v2.1.1}

(I just wanted to write to duckdb from another lazy tbl without collecting.
With this bug, I had to collect() and use DBI::dbWriteTable().

Am I misusing the dplyr interface?)

Using the latest versions from CRAN:

• duckdb 1.1.3
• dbplyr 2.5.0
• dplyr 1.1.4
• DBI 1.2.3

Thanks!