SQLite Forum

unqualified rowid as a SELECT column
Login

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.