ROWID in compound SELECT
(1) By jwvanooijen on 2021-07-20 13:11:35 [link] [source]
In my application I have used for a long time the ROWID alias to sort, also in compound SELECT statements, such as: SELECT * FROM (SELECT * FROM <MainTable> ORDER BY ROWID) AS main <JoinStatement> ORDER BY main.ROWID My tables never have an own declared ROWID column, but always an INTEGER PRIMARY KEY column like "Nr" that will function as ROWID; according to the documentation ROWID will act as an alias to this "Nr". After updating to SQLite3 version 3.36.0 (I have skipped several versions) the system does not recognize anymore the alias "main.ROWID". To solve this I have to add explicitly ROWID in the primary SELECT statement: SELECT * FROM (SELECT ROWID,* FROM <MainTable> ORDER BY ROWID) AS main <JoinStatement> ORDER BY main.ROWID My question is, was this change in alias behaviour of ROWID made deliberately? And why? Thanks.
See point #3 of https://www.sqlite.org/changes.html for 3.36
was this change in alias behaviour of ROWID made deliberately?
Subqueries and views do not have ROWIDs. Referencing the ROWID of a subquery or view would sometimes work, and would at other times give you a NULL or an arbitrary integer, depending on the chosen query plan. This was a bug in that SQLite was not raising an error when it should have. The change to disallow ROWIDs for subqueries and views is considered a bug fix.
I anticipated that there might be legacy applications that depend on the
old buggy behavior. For that reason there is a compile-time option
-DSQLITE_ALLOW_ROWID_IN_VIEW that disables the bug fix and restores the
legacy behavior. In as much as the legacy ROWIDs would sometimes work and
sometimes not, though, a better solution is to fix your application.
Row-id aliases have always worked and still works, and is the only good way to do anything "by rowid".
It's not good SQL to refer stuff in SORT BY clauses, or any other, that are not explicitly defined in your schema, and that SQLite even allows this is a specific peculiarity. It did however cause problems, especially in subqueries and views, which I believe is why the change was made.
You /can/ compile a version that allows it still, but honestly my advice is to change your queries or schemata and use explicit aliases (you did demonstrated a clear understanding of that mechanism).