[Debian-med-packaging] Bug#850037: r-cran-rsqlite: Row value IN queries give syntax errors.

[Kirill Müller]

After upgrading the embedded SQLite sources to 3.16.2, the query now 
works. Please install the branch that contains the upgrade with

#install.packages("remotes")
remotes::install_github("rstats-db/RSQLite at f-upgrade")

(not recommended for production!)


-Kirill


On 03.01.2017 21:36, Andreas Tille wrote:
> Hi Kirill,
>
> I'm hereby forwarding a bug from a Debian user.
>
> Kind regards
>
>          Andreas.
>
> On Tue, Jan 03, 2017 at 12:12:48PM +0000, Andy Wood wrote:
>> Package: r-cran-rsqlite
>> Version: 1.1-1-1
>> Severity: important
>>
>> Dear Maintainer,
>>
>> After the upgrade from 1.0.0-2 to 1.1-1-1, row value subqueries using IN no longer
>> appear to work.  Here is an example (taken from https://www.sqlite.org/rowvalue.html)
>>
>> 1. create a database and populate. The file create_db.sql contains:
>>
>> CREATE TABLE t2(x,y,z);
>> INSERT INTO t2(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
>>
>> # sqlite3 test.db < create_db.sql
>>
>> 2. query the database with sqlite3. The file query_db.sql contains:
>>
>> select
>>   (1,2,3) in (select x,y,z from t2),
>>   (7,8,9) in (select x,y,z from t2),
>>   (1,3,5) in (select x,y,z from t2);
>>
>> # sqlite3 test.db < query_db.sql
>>
>> 1|0|
>>
>> 3. query the database with RSQLite. The file query_db.r contains:
>>
>> library(DBI)
>> library(RSQLite)
>> dbd <- dbDriver("SQLite")
>> dbf <- "test.db"
>> con <- dbConnect(dbd, dbf)
>> qry <- dbSendQuery(con, statement = paste(
>>         "select",
>>         " (1,2,3) in (select x,y,z from t2),",
>>         " (7,8,9) in (select x,y,z from t2),",
>>         " (1,3,5) in (select x,y,z from t2)"))
>> dbFetch(qry, n=-1)
>> dbClearResult(qry)
>> dbDisconnect(con)
>> quit()
>>   
>> # R --quiet --no-save < query_db.r
>>
>>> library(DBI)
>>> library(RSQLite)
>>> #
>>> dbd <- dbDriver("SQLite")
>>> dbf <- "test.db"
>>> con <- dbConnect(dbd, dbf)
>>> qry <- dbSendQuery(con, statement = paste(
>> +        "select",
>> +        " (1,2,3) in (select x,y,z from t2),",
>> +        " (7,8,9) in (select x,y,z from t2),",
>> +        " (1,3,5) in (select x,y,z from t2)"))
>> Error in rsqlite_send_query(conn at ptr, statement) : near ",": syntax error
>> Calls: dbSendQuery ... initialize -> initialize -> rsqlite_send_query -> .Call
>> Execution halted
>>
>>
>> -- System Information:
>> Debian Release: stretch/sid
>>    APT prefers testing
>>    APT policy: (900, 'testing'), (300, 'unstable')
>> Architecture: amd64 (x86_64)
>>
>> Kernel: Linux 4.8.0-2-amd64 (SMP w/4 CPU cores)
>> Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)
>> Shell: /bin/sh linked to /bin/dash
>> Init: systemd (via /run/systemd/system)
>>
>> Versions of packages r-cran-rsqlite depends on:
>> ii  libc6                  2.24-8
>> ii  libgcc1                1:6.2.1-5
>> ii  libstdc++6             6.2.1-5
>> ii  r-base-core [r-api-3]  3.3.2-1
>> ii  r-cran-dbi             0.5-1-1
>> ii  r-cran-memoise         1.0.0-1
>> ii  r-cran-rcpp            0.12.8-1
>>
>> r-cran-rsqlite recommends no packages.
>>
>> Versions of packages r-cran-rsqlite suggests:
>> pn  r-cran-dbitest   <none>
>> ii  r-cran-knitr     1.15.1-1
>> ii  r-cran-testthat  1.0.2-2
>>
>> -- no debconf information
>>
>> _______________________________________________
>> Debian-med-packaging mailing list
>> Debian-med-packaging at lists.alioth.debian.org
>> http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/debian-med-packaging
>>