sqldf : create table from data frame error: "no such table". and two tables created instead of one

Question

I've recently upgraded R, RSQLite, and sqldf (versions below).

Normally:

sqldf('create table foo as select * from bar', db = 'test.db')

should create a table called 'foo' in an attached sqlite database, using data frame 'bar' if it exists to load the new table.

Instead, I'm getting a 'no such table' error, and also when I look at the database there are both 'foo' and 'bar' tables created.

Reproducible example:

library(RSQLite)
library(sqldf)
mydb = 'test.db'
## remove file if it exists                                                                                                                                                                                       
system(paste('rm', mydb))
## open connection                                                                                                                                                                                                
##con <- dbConnect(SQLite(), dbname=mydb)                                                                                                                                                                         
system(paste('ls -l', mydb))
sqldf( paste0( 'attach "', mydb, '" as new' ) )
system(paste('ls -l', mydb))
class(mtcars)
sqldf( 'create table mycars as select * from mtcars', dbname = mydb )
sqldf('select * from sqlite_master', dbname = mydb)
sqldf('select * from main.mycars limit 1', dbname = mydb)
sqldf('select * from main.mtcars limit 1', dbname = mydb)
sessionInfo()

which produces two tables and throws an error (to add insult to injury):

    > library(RSQLite)                                                                                                                                                                                               
     > library(sqldf)                                                                                                                                                                                                 
     Loading required package: gsubfn
     Loading required package: proto
     > mydb = 'test.db'                                                                                                                                                                                               
     > ## remove file if it exists                                                                                                                                                                                    
     > system(paste('rm', mydb))                                                                                                                                                                                      
     > ## open connection                                                                                                                                                                                             
     > ##con <- dbConnect(SQLite(), dbname=mydb)                                                                                                                                                                      
     > system(paste('ls -l', mydb))                                                                                                                                                                                   
     ls: test.db: No such file or directory
     > sqldf( paste0( 'attach "', mydb, '" as new' ) )                                                                                                                                                                
     Loading required package: tcltk
     data frame with 0 columns and 0 rows
     > system(paste('ls -l', mydb))                                                                                                                                                                                   
     -rwxrwxrwx  1 nathan  staff  1 Jan  6 10:01 test.db
     > class(mtcars)                                                                                                                                                                                                  
     [1] "data.frame"
     > sqldf( 'create table mycars as select * from mtcars', dbname = mydb )                                                                                                                                          
     Error in rsqlite_send_query(conn@ptr, statement) :
       no such table: `mtcars`
     In addition: Warning message:
     Quoted identifiers should have class SQL, use DBI::SQL() if the caller performs the quoting.
     > sqldf('select * from sqlite_master', dbname = mydb)                                                                                                                                                            
        type   name tbl_name rootpage
     1 table mtcars   mtcars        2
     2 table mycars   mycars        5
                                                                                                                                                                                                   sql
     1 CREATE TABLE `mtcars` (\n  "mpg" REAL,\n  "cyl" REAL,\n  "disp" REAL,\n  "hp" REAL,\n  "drat" REAL,\n  "wt" REAL,\n  "qsec" REAL,\n  "vs" REAL,\n  "am" REAL,\n  "gear" REAL,\n  "carb" REAL\n)
     2                          CREATE TABLE mycars(\n  mpg REAL,\n  cyl REAL,\n  disp REAL,\n  hp REAL,\n  drat REAL,\n  wt REAL,\n  qsec REAL,\n  vs REAL,\n  am REAL,\n  gear REAL,\n  carb REAL\n)
     > sqldf('select * from main.mycars limit 1', dbname = mydb)                                                                                                                                                      
       mpg cyl disp  hp drat   wt  qsec vs am gear carb
     1  21   6  160 110  3.9 2.62 16.46  0  1    4    4
     > sqldf('select * from main.mtcars limit 1', dbname = mydb)                                                                                                                                                      
       mpg cyl disp  hp drat   wt  qsec vs am gear carb
     1  21   6  160 110  3.9 2.62 16.46  0  1    4    4
     > sessionInfo()                                                                                                                                                                                                  
     R version 3.3.2 (2016-10-31)
     Platform: x86_64-apple-darwin13.4.0 (64-bit)
     Running under: OS X El Capitan 10.11.6

     locale:
     [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

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

     other attached packages:
     [1] sqldf_0.4-10    gsubfn_0.6-6    proto_1.0.0     RSQLite_1.1-1   devtools_1.12.0

     loaded via a namespace (and not attached):
     [1] DBI_0.5-1     withr_1.0.2   Rcpp_0.12.8   memoise_1.0.0 digest_0.6.11 chron_2.3-48

Is this a bug or some new intended behavior?  Thanks for your help.

Answer 1

UPDATE: newest versions of RSQLite and sqldf do not have the incompatibility issues addressed by this question i.e.: sqldf_0.4-10 RSQLite_1.1-2 work together well - Nathan

All: Thanks to G. Grothendieck for pointers to incompatibility issues introduced by RSQLite 1.1-1. As stated in the comment to the answer, downgrade RSQLite to 1.0.0:

devtools::install_url("https://cran.r-project.org/src/contrib/Archive/RSQLite/RSQLite_1.0.0.tar.gz")

Answer 2

This is indeed a compatibility problem between the current version of RSQLite and the sqldf package. RSQLite is now stricter about the arguments it accepts for dbReadTable(), dbWriteTable() and dbRemoveTable(), warnings will be issued (but only once per session) until sqldf is adapted.

I'll release a compatibility update soon, there is an issue on GitHub that will contain progress updates.