Regression: migrating from build_sql() to glue_sql2() lost escaping

[fh-mthomson]

Problem summary

In #1228, most references to build_sql() where replaced with glue_sql2().

This subtly broke some fundamental SQL translations, since escape() methods were no longer being applied.

Reprex (using Snowflake as my motivating example)

Previously:

local_con(dbplyr::simulate_snowflake())
months = function(x) {
  build_sql("INTERVAL '", x, " month'")
}
months(1)
  <SQL> INTERVAL '1.0 month'

current translation, which fails:

months = function(x) {
  glue_sql2(sql_current_con(), "INTERVAL '{x} month'")
}
months(1)

Error in `.transformer()`:
! `value` must be a string or scalar SQL, not the number 1.
Run `rlang::last_trace()` to see where the error occurred.

Solutions considered

1. [hacky] Manually add escape(x) in applicable calls to glue_sql2() (e.g., add escaping for Snowflake interval functions #1350 to mitigate breaking Snowflake translations)
2. [robust] Migrate relevant escape() functionality from build_sql() to gluesql2() (https://github.com/tidyverse/dbplyr/blob/main/R/build-sql.R#L32-L44)

[fh-mthomson]

A simple Postgres translation also suffers from this error, when previously it was acceptable, suggesting a broader issue:

local_con(simulate_dbi())
test_translate_sql(if_else(x, -1, 2))

[fh-mthomson]

Ah, yea, the straight glue here removes the escaping: 83045c5#diff-1d16344a03920504ed530dc7fa4a10614ffc383ba2c5762d18706280747d1f97R102

[fh-mthomson]

Another (slightly more obscure) example:

mf <- lazy_frame(x = c(1:5), y = c(rep("A", 5)), con = simulate_snowflake())
> mf %>% dplyr::summarize(n = n_distinct(y))
<SQL>
SELECT COUNT(DISTINCT `y`) AS `n`
FROM `df`
> mf %>% dplyr::summarize(n = n_distinct(1))
Error in `.transformer()`:
! `value` must be a string or scalar SQL, not the number 1.
Run `rlang::last_trace()` to see where the error occurred.

[mgirlich]

I checked all uses of glue_sql2() and found different issues:

1. enpar() didn't work for some special cases, e.g. enpar(-1).
2. Some translations don't work but I don't think they make sense, e.g.

con <- simulate_dbi()
translate_sql(x[1L], con = con)
translate_sql(desc(1), con = con)
translate_sql(n_distinct(1), con = con)

We can fix them but is there a valid use case for them?

3. Snowflake date interval functions

[mgirlich]

Note that glue_sql2() already provides several helpers for escaping. This was part of the motivation to switch to glue_sql2() instead of build_sql().

[fh-mthomson]

Thank you, great call! Leaving (2) aside seems fine to me.