Some expected and documented rowid behavior
CREATE TABLE a(rowid INTEGER PRIMARY KEY);
CREATE TABLE b(id);
SELECT rowid FROM a; -- works as expected and documented
SELECT rowid FROM b; -- works as expected and documented
CREATE TABLE d(id PRIMARY KEY) WITHOUT ROWID;
SELECT rowid FROM d; -- fails as expected and documented
CREATE VIEW av AS SELECT * FROM a;
SELECT rowid FROM av; -- works as expected
Some rowid behavior which is reasonable (perhaps even expected) but I couldn't find documentation :
SELECT rowid FROM a,b; -- gets a.rowid, reasonable and expected
SELECT rowid FROM b,d; -- fails, even though b.rowid exists (sort-of)
CREATE VIEW bv AS SELECT * FROM b;
SELECT rowid FROM bv; -- gets null
-- works if exactly one of a,b,... has an explicit rowid column
SELECT rowid from a,b,...;
A bit surprising (but I'm not sure how much it matters) is that for views
SELECT rowid FROM myview;
works as expected if
SELECT * FROM myview;
produces a column named rowid, but produces null (rather than reporting an error) otherwise.