Inconsistent handling of integer64s with dbplyr/dplyr

Using dbplyr with MS SQL (using DBI/odbc), I have queries that return integer64s, e.g.,

> str(encounters)
Classes ‘tbl_df’, ‘tbl’ and 'data.frame':	139879 obs. of  1 variable:
$ ENCOUNTER:integer64 1802 8380 8713 16726 17210 24539 34136 34827 ... 

I write this data to a database table:

DBI::dbWriteTable(dbconnectionABC, "xyz.abcdef.encounter", encounters)

MS SQL Server Management Studio reports this data type:

ENCOUNTER(varchar(255), null)

How did an integer64 become a varchar? Is this a bug, or expected behavior I don't understand?

Oddly, when displaying the tibble, the first three integer64s are shown as strings. Is this related?

> head(encounters)
# A tibble: 6 x 1
  <S3: integer64>
1 " 1802"        
2 " 8380"        
3 " 8713"        
4 16726          
5 17210          

Here's the sessionInfo if relevant:

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] bindrcpp_0.2.2   ggridges_0.5.1   writexl_1.1      readxl_1.1.0     kableExtra_0.9.0 dbplyr_1.2.2    
 [7] odbc_1.1.6       DBI_1.0.0        forcats_0.3.0    stringr_1.3.1    dplyr_0.7.7      purrr_0.2.5     
[13] readr_1.1.1      tidyr_0.8.1      tibble_1.4.2     ggplot2_3.1.0    tidyverse_1.2.1 

loaded via a namespace (and not attached):
 [1] tidyselect_0.2.5  haven_1.1.2       lattice_0.20-35   colorspace_1.3-2  viridisLite_0.3.0 htmltools_0.3.6  
 [7] mgcv_1.8-24       yaml_2.2.0        base64enc_0.1-3   utf8_1.1.4        blob_1.1.1        rlang_0.3.0.1    
[13] pillar_1.3.0      glue_1.3.0        withr_2.1.2       bit64_0.9-7       modelr_0.1.2      bindr_0.1.1      
[19] plyr_1.8.4        munsell_0.5.0     gtable_0.2.0      cellranger_1.1.0  rvest_0.3.2       evaluate_0.12    
[25] labeling_0.3      knitr_1.20        fansi_0.4.0       highr_0.7         broom_0.5.0       Rcpp_0.12.19     
[31] scales_1.0.0      backports_1.1.2   jsonlite_1.5      bit_1.1-14        hms_0.4.2         digest_0.6.18    
[37] stringi_1.2.4     grid_3.5.0        rprojroot_1.3-2   cli_1.0.1         tools_3.5.0       magrittr_1.5     
[43] lazyeval_0.2.1    crayon_1.3.4      pkgconfig_2.0.2   Matrix_1.2-14     xml2_1.2.0        lubridate_1.7.4  
[49] assertthat_0.2.0  rmarkdown_1.10    httr_1.3.1        rstudioapi_0.8    R6_2.3.0          nlme_3.1-137     
[55] compiler_3.5.0   
1 Like

Hi, I think that's something that may need to be addressed by the odbc package. Specifically here:

Can you open an issue in that repo please?