[R-sig-DB] dbWriteTable() is renaming the 'end' column

[Hervé Pagès]

Gabor Grothendieck wrote:
> end is a reserved SQL keyword.

Ok, maybe so what?

This works:

   dbSendQuery(con, "CREATE TABLE mydata (start INTEGER, end INTEGER)")

and if it didn't, I should get an error (or at least a warning).
Then I could try again with:

   dbSendQuery(con, "CREATE TABLE mydata (`start` INTEGER, `end` INTEGER)")

Using backquotes allows me to use whatever name I want for my columns, 
even SQL keywords. This is ANSI SQL.

IMO, it would be much better if dbWriteTable() was:
   - not trying to be too smart and just send the CREATE statement,
     leaving the DB backend decide whether it's valid or not, and
     eventually return an error,
   - if dbWriteTable() really wants to be smart, then it could try
     a little bit harder to create the table I want with the column
     names I want by using backquotes.

Just my 2 c.

Cheers,
H.

> 
> 2009/9/29 Hervé Pagès <hpages using fhcrc.org>:
>> Hi,
>>
>> Consider the following data:
>>
>>  mydata <- data.frame(start=1:5, end=11:15)
>>
>> Then storing and retrieving the data shows that the 'end'
>> column was renamed:
>>
>>  drv <- dbDriver("SQLite")
>>  con <- dbConnect(drv, dbname=":memory:")
>>
>>  > dbWriteTable(con, "mydata", mydata)
>>  [1] TRUE
>>  > dbReadTable(con, "mydata")
>>    start end__1
>>  1     1     11
>>  2     2     12
>>  3     3     13
>>  4     4     14
>>  5     5     15
>>
>> The culprit doesn't seem to be dbReadTable():
>>
>>  > dbGetQuery(con, "SELECT * FROM mydata")
>>    row_names start end__1
>>  1         1     1     11
>>  2         2     2     12
>>  3         3     3     13
>>  4         4     4     14
>>  5         5     5     15
>>
>> and using the sqlite3 client from outside R confirms this.
>>
>> Looks like a bug in dbWriteTable().
>>
>> Cheers,
>> H.
>>
>> --
>> Hervé Pagès
>>
>> Program in Computational Biology
>> Division of Public Health Sciences
>> Fred Hutchinson Cancer Research Center
>> 1100 Fairview Ave. N, M2-B876
>> P.O. Box 19024
>> Seattle, WA 98109-1024
>>
>> E-mail: hpages using fhcrc.org
>> Phone:  (206) 667-5791
>> Fax:    (206) 667-1319
>>
>> _______________________________________________
>> R-sig-DB mailing list -- R Special Interest Group
>> R-sig-DB using stat.math.ethz.ch
>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>

-- 
Hervé Pagès

Program in Computational Biology
Division of Public Health Sciences
Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N, M2-B876
P.O. Box 19024
Seattle, WA 98109-1024

E-mail: hpages using fhcrc.org
Phone:  (206) 667-5791
Fax:    (206) 667-1319