SQLite
View Ticket
Not logged in
Ticket UUID: dec444cd6817755b293558c79d55fabc227c0f09
Title: rowid not usable in complex queries
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Not_A_Bug
Last Modified: 2012-12-19 12:46:56
Version Found In: 3.7.15
User Comments:
nobody added on 2012-12-18 20:41:32:
When using sub queries I can no longer make use of the rowid. With version 
3.7.13 which is shipped in Debian testing (and probably 3.7.14, too) 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 this returns an empty line while 3.7.13 returns '3' as 
expected.

I already reported this against Debian in [0] but since people using macports and Gentoo reported the same problem I decided to better tell you directly.

[0] http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=696022

drh added on 2012-12-19 12:46:56:

Subqueries do not (necessarily) have rowids. This is not a bug.

You should not use rowids. If you want an integer primary key, then declare one as part of your schema and use that instead. Rowids are an example of the implementation showing through into the interface and should be avoided in all applications.