Weird character when writing text fields

[rickyars]

I'm getting a weird character added to all my text fields. E.g., If I run:

db <- dbConnect(SQLite(), dbname="test.sqlite")

dbGetQuery(db, "CREATE TABLE Test
(
  TestId INTEGER PRIMARY KEY, -- Autoincrement
  TestVariable TEXT,
  TestValue INTEGER
)")

# made up data
test.df <- data.frame(TestVariable = letters[sample.int(26, 10)], TestValue = runif(10))

# write data frame to database
dbWriteTable(db, "Test", test.df, append=TRUE, row.names=FALSE)

And then look at the table in SQLite Manager, I see a funny looking character (a box with four small zeros in a 2x2 grid) after the letter. If I read the table back into R, this character is no longer there. But the problem is when I try to work in the database itself. E.g., I can't match on any text field since the strings have this weird character appended to them.

I can send you a screen shot if you're interested in seeing what I'm seeing.

I'm using the development version of RSQLite.

[rickyars]

A quick Google search indicates it might be the Unicode null character: U+0000

[hadley]

It would be really useful if you could come up with some way to test this inside R. I tried using length() and filtering based on the value, but the results seem ok to me:

library(DBI)
con <- dbConnect(RSQLite::SQLite())

dbGetQuery(con, "CREATE TABLE test (x CHARACTER)")
dbGetQuery(con, "INSERT INTO test VALUES ('a')")
dbGetQuery(con, "SELECT length(x) FROM test")
dbGetQuery(con, "SELECT * FROM test WHERE x = 'a'")

[rickyars]

Hadely,

If you use dbWriteTable and then try dbGetQuery(con, "SELECT * FROM test WHERE x = 'a'") you will see that if fails. Very curious that the length query returns the correct value.

[rickyars]

Sorry for being terse, I had to run out the door:

library(RSQLite)

con <- dbConnect(SQLite())
dbGetQuery(con, "CREATE TABLE test (x CHARACTER)")
dbWriteTable(con, "test", data.frame(x = "a"), append=TRUE)

This works:

> dbGetQuery(con, "SELECT length(x) FROM test")
  length(x)
1         1

This does not:

> dbGetQuery(con, "SELECT * FROM test WHERE x = 'a'")
[1] x
<0 rows> (or 0-length row.names)

Yet there is data in the table:

> dbReadTable(con, "test")
  x
1 a

I think the problem is with dbWriteTable.

[hadley]

BTW it's not super surprising that length didn't return the correct value - null is a weird character that isn't normally found in strings

[github-actions]

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.