Unexpected behavior when passing the dots to a wrapper of `glue_sql()`

[calderonsamuel]

Problem

I made a wrapper to more easily create my queries and found an unexpected behaviour in glue_sql(). These are minimal examples of the problem. The wrapper passes a character vector and named arguments to the ... argument of glue_sql() after a connection to take advantage of the SQL quoting and to avoid typing.

make_query <- function(...) {
    con <- DBI::dbConnect(RSQLite::SQLite())
    query <- glue::glue_sql(..., .con = con)
    DBI::dbDisconnect(con)
    return(query)
}

When used directly, it behaves as expected.

make_query("SELECT * FROM table WHERE id = {id}", id = "007")
#> <SQL> SELECT * FROM table WHERE id = '007'

However, wrapping this function makes sense for easily making more specific queries. Let's say I want the data from an id:

specific_query <- function(id) {
    query <- "SELECT * FROM table WHERE id = {id}"
    make_query(query, id = id)
}

But this raises the following error:

specific_query("007")
#> Error in eval(parse(text = text, keep.source = FALSE), envir): object 'id' not found

I'm not sure what is happening here. The error message makes me think that glue_sql() receives query but not id = id. However, the function documentation says:

... [expressions] Unnamed arguments are taken to be expression string(s) to format. Multiple inputs are concatenated together before formatting. Named arguments are taken to be temporary variables available for substitution.

Which makes me think that make_query(query, id = id) may be treating query as a named argument, leaving no strings to format for glue_sql(). ¿Or maybe the problem is the .con argument?

Workaround

I get the expected output when instead of passing directly to glue_sql() I list the dots contents and use do.call() with them. So, I found a solution, yes. But I report this because I know why this one works, but I don't know why just passing the dots doesn't.

make_query2 <- function(...) {
    con <- DBI::dbConnect(RSQLite::SQLite())
    dots <- list(...)
    dots$`.con` <- con
    query <- do.call(glue::glue_sql, dots)
    DBI::dbDisconnect(con)
    return(query)
}

make_query2("SELECT * FROM table WHERE id = {id}", id = "007")
#> <SQL> SELECT * FROM table WHERE id = '007'

specific_query2 <- function(id) {
    query <- "SELECT * FROM table WHERE id = {id}"
    make_query2(query, id = id)
}

specific_query2("007")
#> <SQL> SELECT * FROM table WHERE id = '007'

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.2.1 (2022-06-23 ucrt)
#>  os       Windows 10 x64 (build 18362)
#>  system   x86_64, mingw32
#>  ui       RTerm
#>  language (EN)
#>  collate  Spanish_Peru.utf8
#>  ctype    Spanish_Peru.utf8
#>  tz       America/Bogota
#>  date     2022-07-15
#>  pandoc   2.17.1.1 @ C:/Program Files/RStudio/bin/quarto/bin/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  bit           4.0.4   2020-08-04 [1] CRAN (R 4.2.1)
#>  bit64         4.0.5   2020-08-30 [1] CRAN (R 4.2.1)
#>  blob          1.2.3   2022-04-10 [1] CRAN (R 4.2.1)
#>  cachem        1.0.6   2021-08-19 [1] CRAN (R 4.2.1)
#>  cli           3.3.0   2022-04-25 [1] CRAN (R 4.2.1)
#>  crayon        1.5.1   2022-03-26 [1] CRAN (R 4.2.1)
#>  DBI           1.1.3   2022-06-18 [1] CRAN (R 4.2.1)
#>  digest        0.6.29  2021-12-01 [1] CRAN (R 4.2.1)
#>  ellipsis      0.3.2   2021-04-29 [1] CRAN (R 4.2.1)
#>  evaluate      0.15    2022-02-18 [1] CRAN (R 4.2.1)
#>  fansi         1.0.3   2022-03-24 [1] CRAN (R 4.2.1)
#>  fastmap       1.1.0   2021-01-25 [1] CRAN (R 4.2.1)
#>  fs            1.5.2   2021-12-08 [1] CRAN (R 4.2.1)
#>  glue          1.6.2   2022-02-24 [1] CRAN (R 4.2.1)
#>  highr         0.9     2021-04-16 [1] CRAN (R 4.2.1)
#>  htmltools     0.5.2   2021-08-25 [1] CRAN (R 4.2.1)
#>  knitr         1.39    2022-04-26 [1] CRAN (R 4.2.1)
#>  lifecycle     1.0.1   2021-09-24 [1] CRAN (R 4.2.1)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.2.1)
#>  memoise       2.0.1   2021-11-26 [1] CRAN (R 4.2.1)
#>  pillar        1.7.0   2022-02-01 [1] CRAN (R 4.2.1)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.2.1)
#>  purrr         0.3.4   2020-04-17 [1] CRAN (R 4.2.1)
#>  R.cache       0.15.0  2021-04-30 [1] CRAN (R 4.2.1)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.2.0)
#>  R.oo          1.25.0  2022-06-12 [1] CRAN (R 4.2.0)
#>  R.utils       2.12.0  2022-06-28 [1] CRAN (R 4.2.1)
#>  Rcpp          1.0.8.3 2022-03-17 [1] CRAN (R 4.2.1)
#>  reprex        2.0.1   2021-08-05 [1] CRAN (R 4.2.1)
#>  rlang         1.0.3   2022-06-27 [1] CRAN (R 4.2.1)
#>  rmarkdown     2.14    2022-04-25 [1] CRAN (R 4.2.1)
#>  RSQLite       2.2.14  2022-05-07 [1] CRAN (R 4.2.1)
#>  rstudioapi    0.13    2020-11-12 [1] CRAN (R 4.2.1)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.2.1)
#>  stringi       1.7.6   2021-11-29 [1] CRAN (R 4.2.0)
#>  stringr       1.4.0   2019-02-10 [1] CRAN (R 4.2.1)
#>  styler        1.7.0   2022-03-13 [1] CRAN (R 4.2.1)
#>  tibble        3.1.7   2022-05-03 [1] CRAN (R 4.2.1)
#>  utf8          1.2.2   2021-07-24 [1] CRAN (R 4.2.1)
#>  vctrs         0.4.1   2022-04-13 [1] CRAN (R 4.2.1)
#>  withr         2.5.0   2022-03-03 [1] CRAN (R 4.2.1)
#>  xfun          0.31    2022-05-10 [1] CRAN (R 4.2.1)
#>  yaml          2.3.5   2022-02-21 [1] CRAN (R 4.2.0)
#> 
#>  [1] C:/Users/dgco93/AppData/Local/Programs/R/R-4.2.1/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

^{Created on 2022-07-15 by the reprex package (v2.0.1)}

[calderonsamuel]

Just found that the behaviour is the same in glue()

wrap_glue <- function(...) glue::glue(...)

wrap_glue("Name is {name}" , name = "Samuel")
#> Name is Samuel

glue_name <- function(name) wrap_glue("Name is {name}", name = name)

glue_name("Samuel")
#> Error in eval(parse(text = text, keep.source = FALSE), envir): object 'name' not found

Workarund is the same

wrap_glue2 <- function(...) {
    dots <- list(...)
    do.call(glue::glue, dots)
}

glue_name2 <- function(name) wrap_glue2("Name is {name}", name = name)

glue_name2("Samuel")
#> Name is Samuel

^{Created on 2022-07-15 by the reprex package (v2.0.1)}

[hadley]

Duplicate of #281: you need to capture and pass on the .envir argument:

wrap_glue <- function(..., envir = parent.frame()) {
  glue::glue(..., .envir = parent.frame())
}
wrap_glue("Name is {name}" , name = "Samuel")
#> Name is Samuel

glue_name <- function(name) wrap_glue("Name is {name}", name = name)
glue_name("Samuel")
#> Name is Samuel

^{Created on 2023-01-25 with reprex v2.0.2}