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")