SQLite 3.36 makes query identity and execution clearer

A view can expose a table's identifier as a column, but that does not give every view an implicit row identity of its own. SQLite 3.36.0 makes that distinction explicit. The June 18 release notes say that attempts to access the rowid of a view or subquery now raise an error. Previously, a view's rowid was indeterminate and often NULL. A query that happened to receive a value was relying on behavior the engine did not define usefully.

The same release improves EXPLAIN QUERY PLAN output. These changes concern different interfaces, SQL name resolution and execution diagnostics, but both help an application distinguish a declared value from an implementation detail. A query should name the identifier it means. A plan should identify which intermediate result it materializes or scans.

I value SQLite's local execution model because it keeps SQL operations close to the application. Clear failures are part of that appeal. Returning an indeterminate identifier makes a mistake harder to recognize than rejecting the unsupported reference when the statement is prepared.

Project an identifier into the view

The distinction is visible in the view tests at the 3.36.0 tag. One group creates views that explicitly select rowid from an underlying table. Those projected columns have names and values in the view's result. Another example explicitly selects an ordinary identifier column through a join. In both cases the view declares what its consumer can read.

The following example uses that established projection pattern. It assigns a descriptive output name so the caller's reference has an explicit definition.

CREATE TABLE message(body TEXT);
INSERT INTO message VALUES ('hello');
CREATE VIEW message_list AS
  SELECT rowid AS message_id, body FROM message;
SELECT message_id, body FROM message_list;

Here, message_id is a projected value from message. It is not an implicit rowid attached to an arbitrary view result. This distinction becomes more useful when a view contains a join or aggregation, since its output can have a different relationship to the source rows. The application should choose the identifier whose meaning its query actually establishes.

The June change also covers subqueries. Wrapping a selection in another query does not establish an additional implicit identifier. Applications that need the source identifier can carry it into the subquery's selected columns, then refer to the resulting name. This makes the data dependency legible in the SQL itself.

For applications that require the old behavior, the release provides the compile-time option SQLITE_ALLOW_ROWID_IN_VIEW. That is a compatibility mechanism, not a new definition of stable view identifiers. The notes still describe the earlier value as indeterminate. Re-enabling it preserves legacy behavior without making the original assumption more reliable.

Plans identify intermediate work

The query-plan tests in the release show how SQLite describes materialization and scans. A query joining a table to a constant subquery produces a materialization step and a subsequent scan of the intermediate result. When the subquery has an alias, the expected output uses that alias in both descriptions.

That is useful when several subqueries occur in one statement. A label such as MATERIALIZE x1 tells the reader which named expression the engine turns into an intermediate result. A later SCAN x1 identifies consumption of that result. This does not mean every subquery is materialized. The tests contain different plans for different query shapes, including correlated scalar subqueries and co-routines.

The suite also distinguishes indexed searches from covering-index scans. With an index on a column, selecting that column with an equality predicate can produce SEARCH ... USING COVERING INDEX. Ordering by the indexed column can instead produce a covering-index scan. Those descriptions answer different questions about how the engine reaches and traverses values.

CREATE TABLE item(code INTEGER, description TEXT);
CREATE INDEX item_code ON item(code);
EXPLAIN QUERY PLAN
SELECT code FROM item WHERE code = 4;

This is the same basic query shape as a covering-index search in the tagged tests. The example asks the engine for its plan, rather than treating the plan as an application result. The SQL query's meaning is the selected code values. Diagnostic wording describes the chosen implementation of that selection.

An optimization is also removed

The release backs out the EXISTS to IN optimization introduced in 3.35.0. According to the June notes, it slowed queries more often than it accelerated them. That is a concrete reason for removing a transformation that had looked promising in the preceding release family.

The point is not that EXISTS and IN should be mechanically exchanged in application SQL. The removed optimization was the engine's conditional transformation. This release history shows that a transformation can preserve intended results yet still make a poor performance choice across the workloads the project encounters. Its reversal changes the engine's decisions without requiring applications to express another logical query.

I find this more informative than a generic claim that a release improves performance. The project names the transformation and gives the reason for backing it out. The accompanying plan diagnostics provide a way to describe the implementation a particular statement receives, without pretending that every statement benefits equally.

Embedders receive different defaults too

The June notes enable sqlite3_serialize() and sqlite3_deserialize() by default. Builds no longer need the previous enabling option for those interfaces. A new SQLITE_OMIT_DESERIALIZE option can leave them out. This is a build-configuration change that matters to software embedding the C library, separate from the SQL name-resolution change.

The release also allows multiple connections in the same process to share an in-memory database through the memdb VFS when its name begins with /. The process boundary in that description matters. Sharing remains within one process, so this arrangement does not provide a network database service. Applications that open several connections locally can use that shared database arrangement, while applications in different processes still need another means of communicating their data. The documented name rule is part of selecting the new behavior.

Together, the June changes give applications more explicit information at several boundaries. A view consumer must name a declared identifier. A person inspecting a query can see named materialization steps in the release's plan output. An embedder has documented serialization defaults and an opt-out. For SQL that previously accessed an implicit view rowid, the immediate behavior change is a preparation error that points back to an undeclared value.