#696022 rowid not usable in complex queries

Package:
libsqlite3-0
Source:
sqlite3
Description:
SQLite 3 shared library
Submitter:
Tobias Ellinghaus
Date:
2012-12-19 12:57:15 UTC
Severity:
normal
#696022#5
Date:
2012-12-15 22:03:43 UTC
From:
To:
When using sub queries I can no longer make use of the rowid. With the version
shipped in testing (3.7.13-1) it works as expected.

A small example:

sqlite3 test.db
sqlite> create table test (line varchar);
sqlite> insert into test (line) values ("a");
sqlite> insert into test (line) values ("b");
sqlite> insert into test (line) values ("c");
sqlite> insert into test (line) values ("d");
sqlite> select rowid from (select * from test limit 0, -1) where line="c";

With 3.7.15-1 this returns an empty line while 3.7.13-1 returns '3' as
expected.

Since this also doesn't work in C code using the library I filed this against
libsqlite3-0 and not sqlite3.
#696022#10
Date:
2012-12-19 12:43:52 UTC
From:
To:
SQLite nowhere guarantees or even suggests that subqueries have rowids.  In
fact, they might or they might not, depending what query plan the optimizer
chooses.  The optimizer was improved between 3.7.13 and 3.7.14, resulting
in a new query plan that does not have a rowid.  This is not a bug.