Bug when using R*tree module

[thomasp85]

I have an open question on StackOverflow (http://stackoverflow.com/questions/26816880/debugging-sqlite-rtree), where a commenter indicated that my problem was related to an RSQLite bug. The SO question comes with a minimal example, but in essence:

When creating an R*tree the comparator querying gets broken. Asking for all records where a value is above a certain level would yield no results, even though there is lots of records with higher values (again it is all described more thoroughly in the SO question).

Is it possible to examine whether it is indeed a bug in RSQLite that causes this behaviour?

[thomasp85]

It has been confirmed that the bug does not appear in the most recent version of SQLite (3.8.7.1), but this is outside the RSQLite framework, so it is still open whether it is a bug in the RSQLite framework or in SQLite 3.8.6 (the release history does not mention a bug fix that appears as this one)

[thomasp85]

The bug is specific to RSQLite on mac (both mavericks and snow leopard build). It cannot be replicated in other SQLite clients on Mac so it seems to be specific to the RSQLite client

[hadley]

Moving in your reproducible example from SO and simplifying it a bit:

library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbGetQuery(con, 'CREATE VIRTUAL TABLE test USING rtree(id, xmin, xmax, ymin, ymax)')
set.seed(1)
x <- abs(rnorm(100))
y <- abs(rnorm(100))
data <- data.frame(id=1:100, xmin=x, xmax=x+2, ymin=y, ymax=y+3)
dbWriteTable(con, "test", data, append = TRUE)

dbGetQuery(con, 'SELECT max(xmax) FROM test')
dbGetQuery(con, 'SELECT * FROM test WHERE xmax > 4')
subset(data, xmax > 4)

The query and the R command return the same results for me.

[thomasp85]

Ok so this is strange. Even though the bug was reproducible across several days on several R sessions it has suddenly vanished - both on the original and your modified example. I cannot give any explanation for this as there has been no changes to my system... I still have a database file with the example results from back when the bug existed - don't know whether that can shed any light on what might have happened?

[hadley]

No idea, sorry.

[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.