Pass through DB functions does not seem to work in select()

[edgararuiz-zz]

Pass through DB functions does not seem to work in select()

[sverchkov]

As far as I can tell, select doesn't support any functions at all, even in plain dplyr with tibbles. transmute does what you want (and works).

[edgararuiz-zz]

cc @javierluraschi

[cderv]

A reprex to illustrate @sverchkov comment. 100% with him, select does not accept applying functions to column. It allows selecting or deselecting column, with the ability of renaming them. See examples.

library(dplyr, warn.conflicts = FALSE)

# without db
mtcars %>%
  mutate(b = sqrt(mpg)) %>% head(10)
#>     mpg cyl  disp  hp drat    wt  qsec vs am gear carb        b
#> 1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4 4.582576
#> 2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4 4.582576
#> 3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1 4.774935
#> 4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1 4.626013
#> 5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2 4.324350
#> 6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1 4.254409
#> 7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4 3.781534
#> 8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2 4.939636
#> 9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2 4.774935
#> 10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4 4.381780
mtcars %>%
  select(b = sqrt(mpg)) %>% head(10)
#> Error in .f(.x[[i]], ...): objet 'mpg' introuvable
mtcars %>%
  transmute(b = sqrt(mpg)) %>% head(10)
#>           b
#> 1  4.582576
#> 2  4.582576
#> 3  4.774935
#> 4  4.626013
#> 5  4.324350
#> 6  4.254409
#> 7  3.781534
#> 8  4.939636
#> 9  4.774935
#> 10 4.381780

# with db
library(DBI)

con <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")

dates_tbl <- copy_to(con, mtcars)

dates_tbl %>%
  mutate(b = sqrt(mpg)) %>% head(10)
#> # Source:   lazy query [?? x 12]
#> # Database: sqlite 3.22.0 [:memory:]
#>      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb     b
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1  21       6  160    110  3.9   2.62  16.5     0     1     4     4  4.58
#>  2  21       6  160    110  3.9   2.88  17.0     0     1     4     4  4.58
#>  3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1  4.77
#>  4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1  4.63
#>  5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2  4.32
#>  6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1  4.25
#>  7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4  3.78
#>  8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2  4.94
#>  9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2  4.77
#> 10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4  4.38
#> # ... with more rows
dates_tbl %>%
  select(b = sqrt(mpg)) %>% head(10)
#> Error in .f(.x[[i]], ...): objet 'mpg' introuvable
dates_tbl %>%
  transmute(b = sqrt(mpg)) %>% head(10)
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.22.0 [:memory:]
#>        b
#>    <dbl>
#>  1  4.58
#>  2  4.58
#>  3  4.77
#>  4  4.63
#>  5  4.32
#>  6  4.25
#>  7  3.78
#>  8  4.94
#>  9  4.77
#> 10  4.38
#> # ... with more rows

transmute does the job of translating to a select with function call

dates_tbl %>%
  transmute(b = sqrt(mpg)) %>% 
  show_query()
#> <SQL>
#> SELECT `b`
#> FROM (SELECT `mpg`, `cyl`, `disp`, `hp`, `drat`, `wt`, `qsec`, `vs`, `am`, `gear`, `carb`, SQRT(`mpg`) AS `b`
#> FROM `mtcars`)

dbDisconnect(con)   

May be it needs some addition in select documentation or somewhere in dbplyr as it is about dplyr & DB.