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 [link]

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

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](src:/info/a2ddb89b206c1387)
on [2021-04-07](src:/timeline?c=a2ddb89b206c1387).  The
-DSQLITE_ALLOW_ROWID_IN_VIEW compile-time option is provided for
legacy code that needs bug compatibility.