unqualified rowid as a SELECT column
(1) By Bill Wade (billwade) on 2021-04-23 12:53:35 [source]
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.
(2) By Richard Hipp (drh) on 2021-04-23 13:08:28 in reply to 1 [link] [source]
As of version 3.36.0, the following gives an error:
CREATE TABLE b(id); CREATE VIEW bv AS SELECT * FROM b; SELECT rowid FROM bv; -- Error, as of version 3.36.0
This is due to check-in a2ddb89b206c1387 on 2021-04-07. The -DSQLITE_ALLOW_ROWID_IN_VIEW compile-time option is provided for legacy code that needs bug compatibility.