When empty `data.frame`s are returned, the column classes are not correct

[nathaneastwood]

When an empty data.frame is returned from a query, I am getting strange column classes for a SQLite connection. This does not happen in our PostgreSQL database.

conn <- DBI::dbConnect(RSQLite::SQLite(), dbFile, ...)

str(DBI::dbGetQuery(conn, "select * from tbl where foo = -1"))
# 'data.frame':	0 obs. of  4 variables:
#  $ foo  : num 
#  $ bar: chr 
#  $ zzz: chr 
#  $ tmp: logi 

str(DBI::dbGetQuery(conn, "select * from tbl where foo = 1"))
# 'data.frame':	1 obs. of  4 variables:
#  $ foo  : int 1
#  $ bar: chr "1"
#  $ zzz: chr "hello"
#  $ tmp: chr "world"

[krlmlr]

Thanks. SQLite columns can contain arbitrary data types in each cell, the type returned for zero-row data frames depends on the declared type of the column.

Can you please post a complete example that includes code for creating the table? The reprex package makes this very easy, see also https://reprex.tidyverse.org/ .

[JasperSch]

I discussed with @nathaneastwood and the core issue seems to be at SQLite side, where it allows to define quite some nonsense types. Even have no type at all.

library(RSQLite)

tmpFile <- tempfile()
conn <- dbConnect(tmpFile, drv= RSQLite::SQLite())

dbExecute(conn, "CREATE TABLE test (id nonexisting_data_type PRIMARY KEY NOT NULL);")
dbExecute(conn, "ALTER TABLE test ADD COLUMN 'unknown';")
dbExecute(conn, "INSERT INTO test (id, unknown) VALUES ('foo', 1)")

str(dbGetQuery(conn, "select * from test limit 1"))
# 'data.frame':	1 obs. of  2 variables:
#      $ id     : chr "foo"
#      $ unknown: int 1
str(dbGetQuery(conn, "select * from test limit 0"))
# 'data.frame':	0 obs. of  2 variables:
#    $ id     : num 
#    $ unknown: logi

unlink(tmpFile)

RSQLite can maybe throw a warning and/or consistently return character in case of unrecognized types? Otherwise, I think there's not much more that can be done at the R side.

[krlmlr]

The current behavior is a balance that, when it was implemented, was useful for the most number of packages that import RSQLite. I'm reluctant to change any of this now.

If you can control the creation of the tables, perhaps the new STRICT mode would help? https://www.sqlite.org/stricttables.html

[JasperSch]

@krlmlr that makes sense. Thanks for thinking along, I think we can close this issue.

[github-actions[bot]]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.