|Title:||rowid not usable in complex queries|
|Last Modified:||2012-12-19 12:46:56|
|Version Found In:||3.7.15|
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  but since people using macports and Gentoo reported the same problem I decided to better tell you directly.  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.