Does sql_variant work in dbplyr as it should?

Question

Let's look at an example in ?sql_variant :

We define a new translator function for aggregated functions, extended by default:

 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") ) 

Then we define a new variant, which is made of the translation functions of three 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") 

They do not look translated into me, should they not 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 behaves as expected, just like the others 2.

On the other hand, the functions translated by default work:

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

This is mistake? or am I missing something?

My goal is to create several options for Oracle and use it as follows, then for more complex functions ( SQLite example for playback):

 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 ? 

EDIT:

One generosity is still not resolved, I crossed the problem on the dplyr / dbplyr github page, where I’m not sure if it will or will attract attention, but in case I (or someone else) do not update it on time, check this url: https://github.com/tidyverse/dplyr/issues/3117

Answer 1

This is what Hadley Wickham responded to the github link provided:

translate_sql () no longer has a variant argument

In fact, the argument of the variant is not documented, although the examples use it, I believe that it will be fixed for the next version.

Answering a question on how to define custom SQL translations that he proposed:

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

I guess the other option is to get an older version of dbplyr::sql_variant .