DBI introduced breaking change

[meztez]

DBI introduced a new dbQuoteLiteral that changes the behaviour of sqlData from DBI with BigQueryConnection class.

remotes::install_github("r-dbi/dbi@v1.1.1")
#> Skipping install of 'DBI' from a github remote, the SHA1 (71400d92) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(bigrquery)
library(DBI)
conn <- dbConnect(
  drv = bigquery(),
  project = bq_test_project(),
  dataset = "SHARE"
)
test_df <- data.frame(VAR1 = c(TRUE, FALSE, TRUE))
dbRemoveTable(conn, "TEST_DF")
dbWriteTable(conn, "TEST_DF", sqlData(conn, test_df))
dbReadTable(conn, "TEST_DF")
#> # A tibble: 3 x 1
#>   VAR1 
#>   <lgl>
#> 1 FALSE
#> 2 TRUE 
#> 3 TRUE
Created on 2022-01-06 by the reprex package (v2.0.1)

remotes::install_github("r-dbi/dbi@v1.1.2")
#> Downloading GitHub repo r-dbi/dbi@v1.1.2
#>          checking for file 'C:\Users\gen01914\AppData\Local\Temp\RtmpQXG3OY\remotes2dd044fc3839\r-dbi-DBI-f9d1f6a/DESCRIPTION' ...     checking for file 'C:\Users\gen01914\AppData\Local\Temp\RtmpQXG3OY\remotes2dd044fc3839\r-dbi-DBI-f9d1f6a/DESCRIPTION' ...   v  checking for file 'C:\Users\gen01914\AppData\Local\Temp\RtmpQXG3OY\remotes2dd044fc3839\r-dbi-DBI-f9d1f6a/DESCRIPTION'
#>       -  preparing 'DBI': (1.5s)
#>    checking DESCRIPTION meta-information ...     checking DESCRIPTION meta-information ...   v  checking DESCRIPTION meta-information
#>       -  installing the package to process help pages
#>       -  checking for LF line-endings in source and make files and shell scripts (11.2s)
#>       -  checking for empty or unneeded directories
#>       -  building 'DBI_1.1.2.tar.gz'
#>      
#> 
library(bigrquery)
library(DBI)
conn <- dbConnect(
  drv = bigquery(),
  project = bq_test_project(),
  dataset = "SHARE"
)
test_df <- data.frame(VAR1 = c(TRUE, FALSE, TRUE))
dbRemoveTable(conn, "TEST_DF")
dbWriteTable(conn, "TEST_DF", sqlData(conn, test_df))
dbReadTable(conn, "TEST_DF")
#> # A tibble: 3 x 1
#>    VAR1
#>   <int>
#> 1     0
#> 2     1
#> 3     1
Created on 2022-01-06 by the reprex package (v2.0.1)

Possible fix

#' @rdname DBI
#' @export
setMethod(
  "dbQuoteLiteral", c("BigQueryConnection", "logical"),
  function(conn, x, ...) {
    x <- as.character(x)
    x[is.na(x)] <- "NULL"
    SQL(x, names = names(x))
  }
)

remotes::install_github("r-dbi/dbi@v1.1.2")
#> Skipping install of 'DBI' from a github remote, the SHA1 (84e82d81) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(bigrquery)
library(DBI)
conn <- dbConnect(
  drv = bigquery(),
  project = bq_test_project(),
  dataset = "SHARE"
)
test_df <- data.frame(VAR1 = c(TRUE, FALSE, TRUE))
dbRemoveTable(conn, "TEST_DF")
dbWriteTable(conn, "TEST_DF", sqlData(conn, test_df))
dbReadTable(conn, "TEST_DF")
#> # A tibble: 3 x 1
#>   VAR1 
#>   <lgl>
#> 1 FALSE
#> 2 TRUE 
#> 3 TRUE
Created on 2022-01-06 by the reprex package (v2.0.1)

[hadley]

Rather more minimal reprex:

library(DBI)
con <- dbConnect(
  drv = bigrquery::bigquery(),
  project = bigrquery::bq_test_project(),
)
dbQuoteLiteral(con, c(FALSE, TRUE, NA))
#> <SQL> 0
#> <SQL> 1
#> <SQL> NULL

^{Created on 2023-11-03 with reprex v2.0.2}

The values should be TRUE, FALSE, and NULL: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#boolean_type