pivot_wider now not transforming to sql correctly after upgrading R 4.0.3 -> 4.2.1

[rowrowrowyourboat]

I'm trying to pivot a table in SQL server and my code was working fine under R 4.0.3. I upgraded to R 4.2.1 and now having a problem where SQL throws an error at the SQL code generated.

It appears to be that previously the lines that are currently shown as MAX(IIF("carb" = 1.0, "gear", )) AS "1", had NULL after gear and before the closing parenthesis. I guess SQL server doesn't like it blank and needs to have the NULL there.

below is an example of connecting to our SQL server and doing the pivot using mtcars as an example as well as my sessionInfo.

Let me know if there's anything else I can provide.

library(tidyverse)
library(odbc)


dbsettings<-list(
  drv = odbc::odbc(),
  Driver="ODBC Driver 17 for SQL Server",
  Server="xxxx\\xxxx",
  Trusted_Connection="yes"
)

#setup database connections
con_all<-do.call(dbConnect, dbsettings )


copy_to(con_all, (mtcars %>% rownames_to_column()), name="mtcars2")
#> Created a temporary table named #mtcars2

tbl(con_all, "#mtcars2")
#> # Source:   table<#mtcars2> [?? x 12]
#> # Database: Microsoft SQL Server 15.00.4198[AC\wroger@P05UE0039\CGAZPRD/RawImportedData]
#>    rowname       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>    <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1 Mazda RX4    21       6  160    110  3.9   2.62  16.5     0     1     4     4
#>  2 Mazda RX4 …  21       6  160    110  3.9   2.88  17.0     0     1     4     4
#>  3 Datsun 710   22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
#>  4 Hornet 4 D…  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
#>  5 Hornet Spo…  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
#>  6 Valiant      18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
#>  7 Duster 360   14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
#>  8 Merc 240D    24.4     4  147.    62  3.69  3.19  20       1     0     4     2
#>  9 Merc 230     22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
#> 10 Merc 280     19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
#> # … with more rows

tbl(con_all, "#mtcars2") %>% pivot_wider(names_from = carb, values_from = gear)
#> Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
#> <SQL> 'SELECT TOP 11
#>   "rowname",
#>   "mpg",
#>   "cyl",
#>   "disp",
#>   "hp",
#>   "drat",
#>   "wt",
#>   "qsec",
#>   "vs",
#>   "am",
#>   MAX(IIF("carb" = 1.0, "gear", )) AS "1",
#>   MAX(IIF("carb" = 2.0, "gear", )) AS "2",
#>   MAX(IIF("carb" = 3.0, "gear", )) AS "3",
#>   MAX(IIF("carb" = 4.0, "gear", )) AS "4",
#>   MAX(IIF("carb" = 6.0, "gear", )) AS "6",
#>   MAX(IIF("carb" = 8.0, "gear", )) AS "8"
#> FROM "#mtcars2"
#> GROUP BY "rowname", "mpg", "cyl", "disp", "hp", "drat", "wt", "qsec", "vs", "am"'


sessionInfo()
#> R version 4.2.1 (2022-06-23 ucrt)
#> Platform: x86_64-w64-mingw32/x64 (64-bit)
#> Running under: Windows 10 x64 (build 19044)
#> 
#> Matrix products: default
#> 
#> locale:
#> [1] LC_COLLATE=English_United States.utf8 
#> [2] LC_CTYPE=English_United States.utf8   
#> [3] LC_MONETARY=English_United States.utf8
#> [4] LC_NUMERIC=C                          
#> [5] LC_TIME=English_United States.utf8    
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#>  [1] odbc_1.3.3      forcats_0.5.1   stringr_1.4.0   dplyr_1.0.9    
#>  [5] purrr_0.3.4     readr_2.1.2     tidyr_1.2.0     tibble_3.1.7   
#>  [9] ggplot2_3.3.6   tidyverse_1.3.1
#> 
#> loaded via a namespace (and not attached):
#>  [1] tidyselect_1.1.2 xfun_0.31        haven_2.5.0      colorspace_2.0-3
#>  [5] vctrs_0.4.1      generics_0.1.2   htmltools_0.5.2  yaml_2.3.5      
#>  [9] blob_1.2.3       utf8_1.2.2       rlang_1.0.3      pillar_1.7.0    
#> [13] glue_1.6.2       withr_2.5.0      DBI_1.1.3        bit64_4.0.5     
#> [17] dbplyr_2.2.1     modelr_0.1.8     readxl_1.4.0     lifecycle_1.0.1 
#> [21] cellranger_1.1.0 munsell_0.5.0    gtable_0.3.0     rvest_1.0.2     
#> [25] evaluate_0.15    knitr_1.39       tzdb_0.3.0       fastmap_1.1.0   
#> [29] fansi_1.0.3      highr_0.9        Rcpp_1.0.8.3     broom_1.0.0     
#> [33] backports_1.4.1  scales_1.2.0     jsonlite_1.8.0   bit_4.0.4       
#> [37] fs_1.5.2         hms_1.1.1        digest_0.6.29    stringi_1.7.6   
#> [41] grid_4.2.1       cli_3.3.0        tools_4.2.1      magrittr_2.0.3  
#> [45] crayon_1.5.1     pkgconfig_2.0.3  ellipsis_0.3.2   xml2_1.3.3      
#> [49] reprex_2.0.1     lubridate_1.8.0  assertthat_0.2.1 rmarkdown_2.14  
#> [53] httr_1.4.3       rstudioapi_0.13  R6_2.5.1         compiler_4.2.1

^{Created on 2022-07-01 by the reprex package (v2.0.1)}

[rowrowrowyourboat]

I was able to get around this by using the values_fill arguement in pivot_wider. Just an unpleasant surprise in how behavior changed.

pivot_wider(names_from = carb, 
                  values_from = gear,
                  values_fill=NA
)