sql translations with metaprogramming

[edward-burn]

In the example below, I was expecting that when I inject new variables via the splice operator, that I would get the same SQL as if I had included the variables directly. But it seems the types are being lost when this is done.

I have put a workaround at the end, where I metaprogramming will work via parsing expressions.

I'm not sure if all this is expected behaviour, but was thinking not as when I work with the local data frame I do retain classes of new variables etc when using the splice operator. What do you think @hadley?

library(dplyr, warn.conflicts = FALSE)
#> Warning: package 'dplyr' was built under R version 4.2.3
library(dbplyr, warn.conflicts = FALSE)
#> Warning: package 'dbplyr' was built under R version 4.2.3
library(rlang)

df <- data.frame(x = 1, y = 2)
df_db <- tbl_lazy(df, con = simulate_odbc())

# metaprogramming with dataframe
add_z_date <- list(z = as.Date("2000-01-01"))
df %>% summarise(!!!add_z_date)
#>            z
#> 1 2000-01-01
df %>% summarise(!!!add_z_date) |> pull() |> class()
#> [1] "Date"

add_z_date <- list(z = as.Date("2000-01-01"))
df_db %>% summarise(z = as.Date("2000-01-01")) %>% show_query()
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`
#> FROM `df`
df_db %>% summarise(!!!add_z_date) %>% show_query()
#> <SQL>
#> SELECT '2000-01-01' AS `z`
#> FROM `df`

# workaround
add_z_date <- 'as.Date("2000-01-01")' |> 
  parse_exprs() |> 
  set_names("z")
df_db %>% summarise(z = as.Date("2000-01-01")) %>% show_query()
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`
#> FROM `df`
df_db %>% summarise(!!!add_z_date) %>% show_query()
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`
#> FROM `df`

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

[ablack3]

Hi @edward-burn,

The issue seems to be when as.Date is evaluated, or more specifically, in what environment as.Date is evaluated in.

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

df <- data.frame(x = 1, y = 2)
df_db <- tbl_lazy(df, con = simulate_odbc())

add_z_date <- list(z = as.Date("2000-01-01"))

# as.Date has already been evaluated in the global environment
add_z_date
#> $z
#> [1] "2000-01-01"

# as.Date function is evaluated by dbplyr
df_db %>% summarise(z = as.Date("2000-01-01")) %>% show_query() 
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`
#> FROM `df`

# as.Date function has already been evaluated 
df_db %>% summarise(!!!add_z_date) %>% show_query() 
#> <SQL>
#> SELECT '2000-01-01' AS `z`
#> FROM `df`

# This works
add_z_date <- list(z = rlang::expr(as.Date("2000-01-01")))
df_db %>% summarise(!!!add_z_date) %>% show_query() 
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`
#> FROM `df`

# exprs could work too
add_z_date <- rlang::exprs(z = as.Date("2000-01-01"), y = as.Date("2000-01-01"))
add_z_date # a list of expressions
#> $z
#> as.Date("2000-01-01")
#> 
#> $y
#> as.Date("2000-01-01")
df_db %>% summarise(!!!add_z_date) %>% show_query() 
#> <SQL>
#> SELECT CAST('2000-01-01' AS DATE) AS `z`, CAST('2000-01-01' AS DATE) AS `y`
#> FROM `df`

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