Error: Cannot pass NA to dbQuoteIdentifier() in sqldf package in R

Question

Error: Cannot pass NA to dbQuoteIdentifier()

In addition: Warning message:

In field_types[] <- field_types[names(data)] :
  number of items to replace is not a multiple of replacement length

This is the error message i am getting upon trying to run anything with sqldf package today.the same queries which ran yesterday dont run today, what am i doing wrong?

Beg

Please review How to Ask and minimal reproducible example. – Brower

Answer 1

I had the same problem:

Error: Cannot pass NA to dbQuoteIdentifier()
In addition: Warning message:
In field_types[] <- field_types[names(data)] :
number of items to replace is not a multiple of replacement length

after some research, I noticed I selected the same column twice in one table:

table1<- sqldf("select columnA,
                       columnA,
                       keyA
                from tableA")
table2<- sqldf("select columnB,
                       keyB
                from tableB")

problematicMerge<- sqldf("select a.*, 
                                 b.* 
                          from tableA a join 
                               tableB 
                          on a.keyA = b.keyB")

this was solved by altering table1 to remove the duplicate column (see below: --I suspect aliasing one of the columns to have a different name will also do the trick):

table1<-sqldf("select columnA,
                      keyA
               from tableA")

Hope this helps

Adventuress

I have added issue #230 to the RSQLite package issue list. github.com/rstats-db/RSQLite/issues/230 – Brower

this description of the problem is little messy, but it's correct about the core problem. In my case, the error was caused by very simple query, without any duplicate columns: sqldf("select rok from d"). The data.frame d itself was looking normal and OK, no duplicate column names, so it was super confusing why would this fail. But the data.frame d itself was created with a query that had duplicate column in select phrase - so the data.frame d itself must be in some unhealthy inner state which you cannot see (looks like any normal data.frame). That's why this issue is so strange :-) – Hawkes

Answer 2

I had the same problem yesterday when I was suddenly unable to upload a table from R to an SQLite db on my remote desktop.

lghdb <- dbConnect(SQLite(), 'lgh.db'
dbWriteTable(lghdb, 'SrtrRisks', SrtrRisks)
Error: Cannot pass NA to dbQuoteIdentifier()...

After muddling around for a while, I realized that this error was due to the addressed SQLite database being "locked" due to an uncompleted (not committed) transaction, related to my simultaneous work using the SQLite Browser. The problem disappeared once I committed the pending transaction.

I guess that you must have figured this out, too, since there has been no follow-up to your post. It might be nice for the RSQLite folks to see whether they can return a more helpful error message under these circumstances.

Larry Hunsicker

Borecole

Answer 3

I too encountered the same error:

    ## step1: encountered the error as below while joining two tables
    screens_temp_2 = sqldf("SELECT a.* , b.ue as 'sp_used_ue' , b.te as 
    'sp_used_te'  from screens_temp a left outer join sp_temp b on  
    a.screen_name = b.screen_name ")
    Error: Cannot pass NA to dbQuoteIdentifier()
    In addition: Warning message:
    In field_types[] <- field_types[names(data)] :
    number of items to replace is not a multiple of replacement length
    ##  step2: while checking the column names , this is what i found
   colnames(screens_temp)
     [1] "screen_name" "usv"         "tsv"         "20_ue"       "20_te"      
    [6] "40_ue"       "40_te"       "60_ue"       "60_te"       "80_ue"      
    [11] "80_te"       "100_ue"      "100_te"      "sp_load_ue"  "sp_load_te" 
   [16] "sp_load_ue"  "sp_load_te" 

The above result shows that sp_load_ue and sp_load_te are repeated.

    ## below i corrected the column names:
    colnames(screens_temp) <- c("screen_name", "usv", "tsv", "20_ue", "20_te", "40_ue"   ,    "40_te"   ,    "60_ue"   ,    "60_te"    ,   "80_ue" ,  "80_te"     ,"100_ue"  ,    "100_te"   ,   "sp_load_ue" , "sp_load_te" , "sp_used_ue" , "sp_used_te" )
     write.table(screens_temp, "screens_temp_corrected.csv",  row.names = FALSE ,col.names = TRUE, sep = ",")

    ## again i ran step 1, it worked fine.

Note: I think there is a bug in sqldf due to which it allows column names to be repeated while assigning output to a dataframe. It should throw an error/warning while assigning the output to a dataframe so that the user can rename the columns appropriately.

Symphonious

This occurs when the data frame is written to the database in the dbWriteTable call, not when it is read back. This occurs even without sqldf. For example, if library(RSQLite); dd <- data.frame(1,2); names(dd) <- c("a", "a"); con <- dbConnect(SQLite()); dbWriteTable(con, "dd", dd) gives that error whereas sqldf("select demand, demand from BOD") does not. If you use the RH2 backend it will report Duplicated column as the error and will identify the duplicated column name. – Brower

Answer 4

Had same issue with sqldf inside a loop. Solved it by putting it inside data.frame call: data.frame(sqldf(..)).

Longford