Big int is not properly handled

[gaizoule]

When dbGetQuery from sqlite table which contains big ints, the result is not right.
for eample:
table: test
id (integer), price(double)
4294967296, 3.1415

then dbGetQuery(con, "select * from test") will return
id,price
0,3.1415

[dankelley]

INTRODUCTION

I think I am running into the same bug. The basic problem is that I am dealing
with datafiles (whose format I cannot change) that store times as milliseconds
since the Unix start time, which means those integers are too large to fit into
32-bit. Although sqlite3 on my machine handles this well, RSLite misinterprets
the number.

DETAILS

For example, the start of the present year

options(digits=22)
1000*(as.numeric(as.POSIXct("2015-01-01 00:00:00", tz="UTC"))-as.numeric(as.POSIXct("1970-01-01 00:00:00", tz="UTC")))

is 1420070400000 in this millisecond notation, a value exceeding the limit for a 32-bit integer.

I find that sqlite3 (version 3.8.5 2014-08-15 22:37:57
c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace) on OSX handles these values properly,
but that RSQLite (either the CRAN or github version) does not.

Rather than attach a real data file, I created a minimal one with

sqlite3 test.db
create table data(t longint);
insert into data values(1420070400000);

The expected result is retrieved by sqlite3, i.e.

sqlite3 test.db "select * from data;"

in the shell yields 1420070400000, but the R code

library(RSQLite)
drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname="test.db")
res <- dbSendQuery(con, "select * from data")
dbFetch(res)

yields

            t
1 -1563774976

SUMMARY QUESTION is there an RSQLite option that will let me handle a long
integer of this sort?

[hadley]

The basic problem is that R doesn't have a 64 bit integer type. So there's definitely going to be some loss somewhere. I don't know what the best strategy is - but we should definitely at least print a warning.

[dankelley]

Thanks. I thought I should ask, in case there was a clever scheme for hooking with int64 or something. No worries -- I'll cook up a workaround within SQL (which will be operating on 64 bit machines for anyone working with this data type), e.g. a query returning an integer pair from which the required millisecond time can be constructed.

[hadley]

I'm planning on rewriting with Rcpp and c++ to make the code much simpler. Once I've done that, I'll expore the option of using the bit64 package. It stores them in doubles, then provides S3 methods to mostly make them behave as integers.

[dankelley]

I thought I'd post my workaround, in case it helps others. This is not a terribly general result, but it should be ok for practical times measured in milliseconds from 1970.

First, create a database that has a time 1/2 second into year 2015.

\rm -f test.db
sqlite3 test.db
create table data(t longint);
insert into data values(1420070400500);

Next, run the following R code

library(RSQLite)
drv <- dbDriver("SQLite")
con <- dbConnect(drv, dbname="test.db")
res <- dbSendQuery(con, "select cast(t as real) from data")
t <- as.numeric(dbFetch(res))
as.POSIXct("1970-01-01 00:00:00", tz="UTC") + t/1000
dbClearResult(res)
dbDisconnect(con)

to get result

"2015-01-01 00:00:00.5 UTC"

I've just run into the same problem. However, I was dealing with IDs, where some loss definitely is a problem. Any ideas?

Edit: This seems to work.

[thatfloflo]

Oh, I've run into that problem. I've posted on stackoverflow to ask about this before discovering this here.

I've used a slightly different workaround by casting the column as TEXT in the query, then having R apply as.numeric() to that column.

Makes me wonder though why the result cannot directly be represented as the same type of value created by as.numeric() in this case?

Example:

library("DBI"); library("RSQLite")
db.h <- dbConnect(RSQLite::SQLite(), dbname=":memory:")
dbGetQuery(db.h, "CREATE TABLE test (a TEXT, b INT)")
dbGetQuery(db.h, "INSERT INTO test VALUES ('value1', 282817178)")
dbGetQuery(db.h, "INSERT INTO test VALUES ('value2', 15620693910)")
r1 = dbGetQuery(db.h, "SELECT * FROM test"); r1
r2 = dbGetQuery(db.h, "SELECT a, cast(b as TEXT) as b from test"); r2$b <- as.numeric(r2$b); r2
dbDisconnect(db.h)

[hpmcwill]

While the 64-bit integer used by SQLite can be mapped into a double precision floating point number by as.numeric() this results in potential loss of precision since a double provides a maximum of 17 significant decimal digits, but the 64-bit integer may have had up to 19 significant decimal digits. Thus, since loss of information may occur, the conversion has to be explicit.

[krlmlr]

There will be an implicit conversion, with a way to retrieve the original numbers with full precision, see r-dbi/DBI#42 (comment).

[krlmlr]

Needs #198 first.