glue::glue_sql() returns differently when with or without NA

[shrektan]

When the values contain NA, the single quotes for the character will disappear, see the reproducible example below.

Is it a bug?

Thanks.

The reproducible example

Pay attention to the last letter of each SQL. When no NA, it's 'c'. When there's NA, it's c without the single quotes.

conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") 
dt <- tibble::tibble(
  COL1 = c("a", "b"),
  COL2 = c("A", "B")
)
DBI::dbWriteTable(conn, "test", dt)
sql <- "insert into test values ({col1}, {col2})"
glue::glue_sql(
  sql,
  col1 = c("c", "d"),
  col2 = c("C", "D"),
  .con = conn
)
#> <SQL> insert into test values ('c', 'C')
#> <SQL> insert into test values ('d', 'D')

glue::glue_sql(
  sql,
  col1 = c("c", "d"),
  col2 = c("C", NA_character_),
  .con = conn
)

### The expected output to me is : insert into test values ('c', 'C')
#> <SQL> insert into test values ('c', C) 
#> <SQL> insert into test values ('d', NULL)

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

Session info

devtools::session_info()
#> ─ Session info ──────────────────────────────────────────────────────────
#>  setting  value                                              
#>  version  R version 3.4.4 (2018-03-15)                       
#>  os       Windows 7 x64 SP 1                                 
#>  system   x86_64, mingw32                                    
#>  ui       RTerm                                              
#>  language (EN)                                               
#>  collate  Chinese (Simplified)_People's Republic of China.936
#>  ctype    Chinese (Simplified)_People's Republic of China.936
#>  tz       Asia/Taipei                                        
#>  date     2018-11-01                                         
#> 
#> ─ Packages ──────────────────────────────────────────────────────────────
#>  package     * version     date       source                            
#>  assertthat    0.2.0       2017-04-11 CRAN (R 3.4.4)                    
#>  backports     1.1.2       2017-12-13 CRAN (R 3.4.3)                    
#>  base64enc     0.1-3       2015-07-28 CRAN (R 3.4.1)                    
#>  bit           1.1-14      2018-05-29 CRAN (R 3.4.4)                    
#>  bit64         0.9-7       2017-05-08 CRAN (R 3.4.0)                    
#>  blob          1.1.1       2018-03-25 CRAN (R 3.4.4)                    
#>  callr         3.0.0       2018-08-24 CRAN (R 3.4.4)                    
#>  cli           1.0.0       2017-11-05 CRAN (R 3.4.4)                    
#>  clisymbols    1.2.0       2017-05-21 CRAN (R 3.4.4)                    
#>  crayon        1.3.4       2017-09-16 CRAN (R 3.4.4)                    
#>  DBI           1.0.0       2018-05-02 CRAN (R 3.4.4)                    
#>  debugme       1.1.0       2017-10-22 CRAN (R 3.4.3)                    
#>  desc          1.2.0       2018-05-01 CRAN (R 3.4.4)                    
#>  devtools      1.13.6.9000 2018-09-21 git (@74df201)                    
#>  digest        0.6.17      2018-09-12 CRAN (R 3.4.4)                    
#>  evaluate      0.11        2018-07-17 CRAN (R 3.4.4)                    
#>  fs            1.2.6       2018-08-23 CRAN (R 3.4.4)                    
#>  glue          1.3.0       2018-07-17 CRAN (R 3.4.4)                    
#>  htmltools     0.3.6.9000  2018-05-07 local                             
#>  knitr         1.20        2018-02-20 CRAN (R 3.4.4)                    
#>  magrittr      1.5         2014-11-22 CRAN (R 3.4.4)                    
#>  memoise       1.1.0       2017-04-21 CRAN (R 3.4.4)                    
#>  pillar        1.3.0       2018-07-14 CRAN (R 3.4.4)                    
#>  pkgbuild      1.0.1       2018-09-18 CRAN (R 3.4.4)                    
#>  pkgconfig     2.0.2       2018-08-16 CRAN (R 3.4.4)                    
#>  pkgload       1.0.0       2018-07-07 CRAN (R 3.4.4)                    
#>  processx      3.2.0       2018-08-16 CRAN (R 3.4.4)                    
#>  ps            1.1.0       2018-08-10 CRAN (R 3.4.4)                    
#>  R6            2.2.2       2017-06-17 CRAN (R 3.4.4)                    
#>  Rcpp          0.12.18     2018-07-23 CRAN (R 3.4.4)                    
#>  remotes       1.1.1.9000  2018-09-21 Github (r-lib/remotes@cd2ab59)    
#>  rlang         0.2.2       2018-08-16 CRAN (R 3.4.4)                    
#>  rmarkdown     1.10        2018-06-11 CRAN (R 3.4.4)                    
#>  rprojroot     1.3-2       2018-01-03 CRAN (R 3.4.3)                    
#>  RSQLite       2.1.1       2018-05-06 CRAN (R 3.4.4)                    
#>  sessioninfo   1.0.1.9000  2018-09-21 Github (r-lib/sessioninfo@418dc5e)
#>  stringi       1.1.7       2018-03-12 CRAN (R 3.4.4)                    
#>  stringr       1.3.1       2018-05-10 CRAN (R 3.4.4)                    
#>  testthat      2.0.0       2017-12-13 CRAN (R 3.4.4)                    
#>  tibble        1.4.2       2018-01-22 CRAN (R 3.4.3)                    
#>  usethis       1.4.0       2018-08-14 CRAN (R 3.4.4)                    
#>  withr         2.1.2       2018-03-15 CRAN (R 3.4.4)                    
#>  yaml          2.2.0       2018-07-25 CRAN (R 3.4.4)

[shrektan]

Any ideas ?

[philibe]

I have the same problem in glue version 1.3.0 in my dev server but on my prod server with glue version 1.2.0, there is not this issue.
I force 1.2.0 on my dev server and the issue doesn't occur anymore.

[philibe]

But I have this issue for dplyr so I replace by glue 1.3.0.

ERROR: package or namespace load failed for ‘dplyr’ in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]):
 namespace ‘glue’ 1.2.0 is already loaded, but >= 1.3.0 is required

Session_info dev server, glue 1.3 because dplyr needs glue 1.3, but the bug with NA

> sessionInfo()
R version 3.4.4 (2018-03-15)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.4 LTS

Matrix products: default
BLAS: /usr/lib/libblas/libblas.so.3.6.0
LAPACK: /usr/lib/lapack/liblapack.so.3.6.0

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

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

other attached packages:
 [1] stringr_1.3.1         gtools_3.5.0          shinyjs_1.0           collapsibleTree_0.1.6 tidyr_0.8.2          
 [6] dplyr_0.7.6           statnet.common_4.0.0  xtable_1.8-3          shinydashboard_0.7.0  glue_1.3.0           
[11] odbc_1.1.6            DBI_1.0.0             DT_0.4                shinyRGL_0.1.0        rgl_0.95.1441        
[16] shiny_1.1.0           RSQLite_2.1.1        

loaded via a namespace (and not attached):
 [1] viridis_0.5.1      jsonlite_1.5       bit64_0.9-7        viridisLite_0.3.0  assertthat_0.2.0   blob_1.1.1        
 [7] yaml_2.2.0         pillar_1.2.2       backports_1.1.2    downloader_0.4     digest_0.6.17      RColorBrewer_1.1-2
[13] promises_1.0.1     colorspace_1.3-2   htmltools_0.3.6    httpuv_1.4.3.9002  plyr_1.8.4         devtools_1.13.6   
[19] XML_3.98-1.3       clipr_0.4.1        pkgconfig_2.0.2    DiagrammeR_1.0.0   purrr_0.2.5        scales_1.0.0      
[25] processx_3.1.0     brew_1.0-6         whisker_0.3-2      later_0.7.5.9001   tibble_1.4.2       ggplot2_3.0.0.9000
[31] influenceR_0.1.0   withr_2.1.2        lazyeval_0.2.1     rgexf_0.15.3       magrittr_1.5       crayon_1.3.4      
[37] mime_0.5           memoise_1.1.0      evaluate_0.11      data.tree_0.7.5    fs_1.2.6           Rook_1.1-1        
[43] tools_3.4.4        hms_0.4.2          munsell_0.5.0      reprex_0.2.1       bindrcpp_0.2.2     callr_2.0.4       
[49] compiler_3.4.4     rlang_0.2.2        grid_3.4.4         rstudioapi_0.7     htmlwidgets_1.3    visNetwork_2.0.3  
[55] igraph_1.2.1       rmarkdown_1.9      gtable_0.2.0       R6_2.3.0           gridExtra_2.3      knitr_1.20        
[61] bit_1.1-14         bindr_0.1.1        rprojroot_1.3-2    readr_1.1.1        stringi_1.2.2      Rcpp_0.12.19      
[67] tidyselect_0.2.5  

Session_info prod server, glue 1.2 ok, and not the bug with NA

> sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.3 LTS

Matrix products: default
BLAS: /usr/lib/libblas/libblas.so.3.6.0
LAPACK: /usr/lib/lapack/liblapack.so.3.6.0

locale:
 [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C               LC_TIME=fr_FR.UTF-8       
 [4] LC_COLLATE=fr_FR.UTF-8     LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8   
 [7] LC_PAPER=fr_FR.UTF-8       LC_NAME=C                  LC_ADDRESS=C              
[10] LC_TELEPHONE=C             LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       

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

other attached packages:
 [1] glue_1.2.0            odbc_1.1.5            DBI_1.0.0             dplyr_0.7.5          
 [5] tidyr_0.8.1           stringr_1.3.1         gtools_3.5.0          devtools_1.13.5      
 [9] shinyjs_1.0           collapsibleTree_0.1.6 statnet.common_4.0.0  xtable_1.8-2         
[13] shinydashboard_0.7.0  RODBCext_0.3.1        RODBC_1.3-15          DT_0.4               
[17] shinyRGL_0.1.0        rgl_0.95.1441         shiny_1.1.0          

loaded via a namespace (and not attached):
 [1] viridis_0.5.1      bit64_0.9-7        jsonlite_1.5       viridisLite_0.3.0  assertthat_0.2.0  
 [6] blob_1.1.1         yaml_2.1.19        pillar_1.2.2       RSQLite_2.1.1      backports_1.1.2   
[11] downloader_0.4     digest_0.6.15      RColorBrewer_1.1-2 promises_1.0.1     colorspace_1.3-2  
[16] htmltools_0.3.6    httpuv_1.4.3.9002  plyr_1.8.4         clipr_0.5.0        XML_3.98-1.11     
[21] pkgconfig_2.0.1    DiagrammeR_1.0.0   purrr_0.2.4        scales_0.5.0       processx_3.2.1    
[26] brew_1.0-6         whisker_0.3-2      later_0.7.3        tibble_1.4.2       ggplot2_2.2.1     
[31] influenceR_0.1.0   withr_2.1.2        lazyeval_0.2.1     rgexf_0.15.3       magrittr_1.5      
[36] mime_0.5           ps_1.3.0           memoise_1.1.0      evaluate_0.10.1    data.tree_0.7.5   
[41] fs_1.2.6           Rook_1.1-1         tools_3.4.3        hms_0.4.2          munsell_0.4.3     
[46] reprex_0.2.1       callr_3.1.1        bindrcpp_0.2.2     compiler_3.4.3     rlang_0.2.1       
[51] grid_3.4.3         rstudioapi_0.7     htmlwidgets_1.2    visNetwork_2.0.3   igraph_1.2.1      
[56] rmarkdown_1.9      gtable_0.2.0       R6_2.2.2           gridExtra_2.3      knitr_1.20        
[61] bit_1.1-13         bindr_0.1.1        rprojroot_1.3-2    readr_1.1.1        stringi_1.2.2     
[66] Rcpp_0.12.17       tidyselect_0.2.4