dbplyr::sql_variant doesn't seem to work as it should

[moodymudskipper]

I am cross posting this from stack overflow as it seems to be a bug rather than a misunderstanding :

Let's take a look at the example in ?sql_variant:

We define a new translator function for aggregated functions, expanded from the default one:

postgres_agg <- sql_translator(.parent = base_agg,
  cor = sql_prefix("corr"),
  cov = sql_prefix("covar_samp"),
  sd =  sql_prefix("stddev_samp"),
  var = sql_prefix("var_samp")
)

We then define a new variant, which is made from translation functions of the 3 different types (here 2):

postgres_var <- sql_variant(
  base_scalar,
  postgres_agg
)

translate_sql(cor(x, y), variant = postgres_var)
# <SQL> COR("x", "y")
translate_sql(sd(income / years), variant = postgres_var)
# <SQL> SD("income" / "years")

These don't look translated to me, shouldn't they be "CORR" and "STDDEV_SAMP" ?

# Original comment:
# Any functions not explicitly listed in the converter will be translated
# to sql as is, so you don't need to convert all functions.
translate_sql(regr_intercept(y, x), variant = postgres_var)
# <SQL> REGR_INTERCEPT("y", "x")

This one behaves as expected, which is just like the other 2.

On the other hand default translated functions work, see:

translate_sql(mean(x), variant = postgres_var)
#<SQL> avg("x") OVER ()

It's a bug right ? or am I missing something ?

My goal is to create some variants for Oracle and use it in the following fashion,then for more complicated functions (example with SQLite to be reproducible):

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, cars, "cars")
con %>% tbl("cars") %>% summarize(dist = group_concat(dist)) # works as expected, as we're stealing the keyword from sqlite directly
sqlite_variant <- sql_variant(aggregate=sql_translator(.parent = base_agg,gpc = sql_prefix("group_concat")))
con %>% tbl("cars") %>% summarize(dist = gpc(dist)) # how do I make this work ?

[hadley]

Could you please rework your reproducible example to use the reprex package ? That makes it easier to see both the input and the output, formatted in such a way that I can easily re-run in a local session.

[moodymudskipper]

There you go

library(dbplyr)
## Copy pasting the example from doc, my comments after double "#"

# An example of adding some mappings for the statistical functions that
# postgresql provides: http://bit.ly/K5EdTn
postgres_agg <- sql_translator(.parent = base_agg,
                               cor = sql_prefix("corr"),
                               cov = sql_prefix("covar_samp"),
                               sd =  sql_prefix("stddev_samp"),
                               var = sql_prefix("var_samp")
)
postgres_var <- sql_variant(
  base_scalar,
  postgres_agg
)
#> Warning: Translator is missing window functions:
#> cor, cov, max, mean, min, n, n_distinct, sd, sum, var

translate_sql(cor(x, y), variant = postgres_var)
#> <SQL> COR("x", "y")
#> <SQL> "postgres_var"
translate_sql(sd(income / years), variant = postgres_var)
#> <SQL> SD("income" / "years")
#> <SQL> "postgres_var"
## we just defined translations for cor and sd, I expected to see:
## > <SQL> CORR("x", "y")
## > <SQL> STDDEV_SAMP("income" / "years")

## If I try a function translated by default such as `mean` however I get a translation:
translate_sql(mean(x))
#> <SQL> avg("x") OVER ()

[hadley]

translate_sql() doesn't have a variant argument any more

[moodymudskipper]

I see, thank you. The only example for sql_variant uses this out of date feature only, so now I struggle to see what's its expected use.

What route would you recommend to define one's own SQL translations of R (possibly custom) functions so that we can use them in mutate calls such as con %>% tbl("my_table") %>% mutate(x=my_function(y,z)) ? That's what I thought I could use sql_variant for.

[hadley]

Have a look at http://dbplyr.tidyverse.org/articles/new-backend.html and http://dbplyr.tidyverse.org/articles/sql-translation.html

[moodymudskipper]

Thanks!