`dbReadTable` converts arbitrary text to 0 if initial values in column INTEGER or REAL

[joethorley]

Although this behaviour appears to be due to SQLite and a warning is thrown I thought it was worth flagging in case you were not aware of it because it could lead errors in data analysis.

conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

x <- data.frame(z = c(1L, 0L))
DBI::dbWriteTable(conn, "x", x)

x <- data.frame(z = c("1", "0", "not a number", NA))
DBI::dbWriteTable(conn, "x", x, append = TRUE)

# "not a number" is converted to 0
DBI::dbReadTable("x", conn = conn)
#> Warning in result_fetch(res@ptr, n = n): Column `z`: mixed type, first seen
#> values of type integer, coercing other values of type string
#>    z
#> 1  1
#> 2  0
#> 3  1
#> 4  0
#> 5  0
#> 6 NA

DBI::dbDisconnect(conn)

^{Created on 2022-10-15 with reprex v2.0.2}