mutate() %>% compute() fails. Error in `.transformer()`: ! `value` must be a string or scalar SQL, not the number.

[erydit]

Hello.
An error started to occur in ‘dbplyr’ version 2.4.0.

I am trying to collect and make some calculations in SQLite database:

DB_CON <- dbConnect(drv = RSQLite::SQLite(), dbname = "BandsTS.db")
BANDS_tbl <- tbl(DB_CON, "bands_ts_tbl") %>%
  mutate( ARVI2 = -0.18 + 1.17 * ((B08 - B04) / (B08 + B04)) ) %>%
compute()

Details

That code (and also show_query() instead of compute() ) throws an error: ```r Error in `.transformer()`: ! `value` must be a string or scalar SQL, not the number -0.18. Run `rlang::last_trace()` to see where the error occurred. > rlang::last_trace() Error in `.transformer()`: ! `value` must be a string or scalar SQL, not the number -0.18. --- Backtrace: ▆ 1. ├─dplyr::show_query(BANDS_tbl) 2. ├─dbplyr:::show_query.tbl_lazy(BANDS_tbl) 3. │ └─dbplyr::remote_query(x, cte = cte, sql_options = sql_options) 4. │ └─dbplyr::db_sql_render(remote_con(x), x, cte = cte, sql_options = sql_options) 5. │ ├─dbplyr::db_sql_render(con, sql, sql_options = sql_options) 6. │ └─dbplyr:::db_sql_render.DBIConnection(con, sql, sql_options = sql_options) 7. │ ├─dbplyr::sql_render(sql, con = con, ..., sql_options = sql_options) 8. │ └─dbplyr:::sql_render.tbl_lazy(sql, con = con, ..., sql_options = sql_options) 9. │ ├─dbplyr::sql_render(...) 10. │ └─dbplyr:::sql_render.lazy_query(...) 11. │ ├─dbplyr::sql_build(query, con = con, sql_options = sql_options) 12. │ └─dbplyr:::sql_build.lazy_select_query(query, con = con, sql_options = sql_options) 13. │ ├─dbplyr::sql_build(op$x, con, sql_options = sql_options) 14. │ └─dbplyr:::sql_build.lazy_select_query(op$x, con, sql_options = sql_options) 15. │ └─dbplyr:::get_select_sql(...) 16. │ └─dbplyr:::translate_select_sql(con, select) 17. │ └─dbplyr::translate_sql_(...) 18. │ └─base::lapply(...) 19. │ └─dbplyr (local) FUN(X[[i]], ...) 20. │ ├─dbplyr::escape(eval_tidy(x, mask), con = con) 21. │ └─rlang::eval_tidy(x, mask) 22. ├─-0.18 + 1.17 * ((B08 - B04)/(B08 + B04)) 23. │ └─dbplyr:::escape_infix_expr(enexpr(x), x, escape_unary_minus = TRUE) 24. │ └─dbplyr:::glue_sql2(sql_current_con(), "({x})") 25. │ ├─dbplyr::sql(...) 26. │ │ └─dbplyr:::c_character(...) 27. │ └─glue::glue(...) 28. │ └─glue::glue_data(...) 29. └─glue (local) ``("x") 30. ├─.transformer(expr, env) %||% .null 31. └─dbplyr (local) .transformer(text = expr, envir = env) ```

[hadley]

Can you please provide a minimal reprex (reproducible example)? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it: please help me help you! If you've never heard of a reprex before, start by reading about the reprex package, including the advice further down the page. Please make sure your reprex is created with the reprex package as it gives nicely formatted output and avoids a number of common pitfalls.

You can see dbplyr specific reprex advice at https://dbplyr.tidyverse.org/articles/reprex.html

[erydit]

Can you please provide a minimal reprex (_repr_oducible _ex_ample)? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it: please help me help you! If you've never heard of a reprex before, start by reading about the reprex package, including the advice further down the page. Please make sure your reprex is created with the reprex package as it gives nicely formatted output and avoids a number of common pitfalls.

You can see dbplyr specific reprex advice at https://dbplyr.tidyverse.org/articles/reprex.html

Sure:

library(tidyverse)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = db, name = "tbl", value = data.frame(x = 1:10))

db_tbl <- tbl(db, "tbl")

db_tbl %>%
  mutate(z = -1 + x)

# Error in `.transformer()`:

dbDisconnect(db)

[hadley]

Actual reprex:

library(tidyverse)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = db, name = "tbl", value = data.frame(x = 1:10))

db_tbl <- tbl(db, "tbl")

db_tbl %>% mutate(z = -1 + x)
#> Error in `.transformer()`:
#> ! `value` must be a string or scalar SQL, not the number -1.

^{Created on 2023-12-19 with reprex v2.0.2.9000}

Looks like problem is happening in escape_infix_expr().

[hadley]

More minimal reprex:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

lf <- lazy_frame(x = 1)
lf %>% mutate(z = -1 + x)

[hadley]

Even more minimal reprex:

library(dbplyr)
test_translate_sql(-1 + x, con = simulate_dbi())