Casting integer -> numeric is not currently possible with SQLlite

[DavisVaughan]

I'm not exactly sure why this is happening, but using the sql translation of CAST(x as NUMERIC) does not actually cast an integer to a double value in SQLlite.

I would've thought that it would work, because the documentation seems to suggest this would be the expected behavior, but maybe I am reading it wrong.
https://www.sqlite.org/datatype3.html#affname

At the very least, I think an "easy" solution would just be providing a translation for as.double and as.numeric that casts to REAL or DOUBLE rather than the base scalar default of NUMERIC

library(dplyr)
library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
ex <- data.frame(x = 1L)
copy_to(con, ex)
ex_tbl <- tbl(con, "ex")

# it is an integer, as expected
ex_tbl
#> # Source:   table<ex> [?? x 1]
#> # Database: sqlite 3.22.0 [:memory:]
#>       x
#>   <int>
#> 1     1

# no conversion done
mutate(ex_tbl, xx = as.double(x))
#> # Source:   lazy query [?? x 2]
#> # Database: sqlite 3.22.0 [:memory:]
#>       x    xx
#>   <int> <int>
#> 1     1     1

# sql maybe incorrect?
mutate(ex_tbl, xx = as.double(x)) %>%
  show_query()
#> <SQL>
#> SELECT `x`, CAST(`x` AS NUMERIC) AS `xx`
#> FROM `ex`

# confirming that it does not work manually
res_int <- dbGetQuery(con, "SELECT CAST(x as NUMERIC) as xx FROM ex")

class(res_int$xx)
#> [1] "integer"

# casting to REAL or DOUBLE works
res_dbl <- dbGetQuery(con, "SELECT CAST(x as REAL) as xx FROM ex")
class(res_dbl$xx)
#> [1] "numeric"

res_dbl2 <- dbGetQuery(con, "SELECT CAST(x as DOUBLE) as xx FROM ex")
class(res_dbl2$xx)
#> [1] "numeric"

Created on 2018-10-06 by the reprex
package (v0.2.0).

Session info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.5.1 (2018-07-02)
#>  system   x86_64, darwin15.6.0        
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_US.UTF-8                 
#>  tz       America/New_York            
#>  date     2018-10-06
#> Packages -----------------------------------------------------------------
#>  package    * version    date       source                          
#>  assertthat   0.2.0      2017-04-11 CRAN (R 3.5.0)                  
#>  backports    1.1.2      2017-12-13 CRAN (R 3.5.0)                  
#>  base       * 3.5.1      2018-07-05 local                           
#>  bindr        0.1.1      2018-03-13 CRAN (R 3.5.0)                  
#>  bindrcpp     0.2.2      2018-03-29 CRAN (R 3.5.0)                  
#>  bit          1.1-14     2018-05-29 CRAN (R 3.5.0)                  
#>  bit64        0.9-7      2017-05-08 CRAN (R 3.5.0)                  
#>  blob         1.1.1      2018-03-25 CRAN (R 3.5.0)                  
#>  cli          1.0.1      2018-09-25 cran (@1.0.1)                   
#>  compiler     3.5.1      2018-07-05 local                           
#>  crayon       1.3.4      2017-09-16 CRAN (R 3.5.0)                  
#>  datasets   * 3.5.1      2018-07-05 local                           
#>  DBI        * 1.0.0      2018-05-02 CRAN (R 3.5.0)                  
#>  dbplyr       1.2.2      2018-07-25 CRAN (R 3.5.0)                  
#>  devtools     1.13.6     2018-06-27 CRAN (R 3.5.0)                  
#>  digest       0.6.17     2018-09-12 CRAN (R 3.5.1)                  
#>  dplyr      * 0.7.6      2018-06-29 CRAN (R 3.5.1)                  
#>  evaluate     0.11       2018-07-17 CRAN (R 3.5.0)                  
#>  fansi        0.3.0      2018-08-13 CRAN (R 3.5.0)                  
#>  glue         1.3.0      2018-07-17 CRAN (R 3.5.0)                  
#>  graphics   * 3.5.1      2018-07-05 local                           
#>  grDevices  * 3.5.1      2018-07-05 local                           
#>  htmltools    0.3.6      2017-04-28 CRAN (R 3.5.0)                  
#>  knitr        1.20       2018-02-20 CRAN (R 3.5.0)                  
#>  magrittr     1.5        2014-11-22 CRAN (R 3.5.0)                  
#>  memoise      1.1.0      2017-04-21 CRAN (R 3.5.0)                  
#>  methods    * 3.5.1      2018-07-05 local                           
#>  pillar       1.3.0.9000 2018-09-25 Github (r-lib/pillar@7be5b8a)   
#>  pkgconfig    2.0.2      2018-08-16 CRAN (R 3.5.0)                  
#>  purrr        0.2.5      2018-05-29 CRAN (R 3.5.0)                  
#>  R6           2.2.2      2017-06-17 CRAN (R 3.5.0)                  
#>  Rcpp         0.12.19.2  2018-10-02 Github (RcppCore/Rcpp@8166c3c)  
#>  rlang        0.2.2.9002 2018-10-04 Github (tidyverse/rlang@3dbbb9c)
#>  rmarkdown    1.10       2018-06-11 CRAN (R 3.5.0)                  
#>  rprojroot    1.3-2      2018-01-03 CRAN (R 3.5.0)                  
#>  RSQLite      2.1.1      2018-05-06 CRAN (R 3.5.0)                  
#>  stats      * 3.5.1      2018-07-05 local                           
#>  stringi      1.2.4      2018-07-20 CRAN (R 3.5.0)                  
#>  stringr      1.3.1      2018-05-10 CRAN (R 3.5.0)                  
#>  tibble       1.4.2      2018-01-22 CRAN (R 3.5.0)                  
#>  tidyselect   0.2.4      2018-02-26 CRAN (R 3.5.0)                  
#>  tools        3.5.1      2018-07-05 local                           
#>  utf8         1.1.4      2018-05-24 CRAN (R 3.5.0)                  
#>  utils      * 3.5.1      2018-07-05 local                           
#>  withr        2.1.2      2018-03-15 CRAN (R 3.5.0)                  
#>  yaml         2.2.0      2018-07-25 CRAN (R 3.5.0)

[hadley]

Minimal reprex:

library(dplyr, warn.conflicts = FALSE)

mf <- dbplyr::memdb_frame(x = 1L)

mf %>% transmute(
  x = TYPEOF(x),
  x_num = TYPEOF(as.numeric(x))
)
#> # Source:   lazy query [?? x 2]
#> # Database: sqlite 3.22.0 [:memory:]
#>   x       x_num  
#>   <chr>   <chr>  
#> 1 integer integer

^{Created on 2019-01-02 by the reprex package (v0.2.1)}