SQLite gains window functions and column renaming

A report often needs each row and a calculation over related rows. Grouping can calculate a total, but it removes the individual rows that explain that total. Application code can recover the detail through another query or an in-memory pass, at the cost of carrying part of the query's meaning outside SQL. SQLite 3.25.0 adds window functions so the database can express both requirements together.

The 15 September 2018 release notes also introduce column renaming through ALTER TABLE and improve table renames so references in triggers and views follow the new name. These are different changes with a related practical consequence. Queries and migrations can describe more of their intent directly to the engine.

I value SQLite as a complete SQL engine inside an application. Window functions strengthen that role for analysis without requiring the data to move to another service. Column renaming addresses the equally ordinary problem of keeping a schema understandable as its terminology changes.

Aggregation can preserve the detail rows

The window-function tests shipped in 3.25.0 begin with a revealing example. A sum over an empty window specification returns the total alongside each row. The input rows remain separate even though each result contains information calculated across the whole input.

Partitioning narrows the related set. A sum with PARTITION BY account can put an account total beside every entry for that account. Unlike GROUP BY, the window does not collapse those entries into a single result row. The calculation annotates detail that a consumer may still need to inspect.

An illustrative ledger query can calculate a running total for each account.

CREATE TABLE entries(
  id INTEGER PRIMARY KEY,
  account TEXT NOT NULL,
  amount INTEGER NOT NULL
);

SELECT id, account, amount,
       sum(amount) OVER (
         PARTITION BY account
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount
FROM entries
ORDER BY account, id;

The partition keeps different accounts separate. Ordering by the unique entry identifier establishes the sequence within an account. The explicit ROWS frame includes every preceding row in that partition and the current row, so the sum grows as the query proceeds through the sequence.

The final ORDER BY has a separate purpose. It specifies how the result is presented to the caller. A window's ordering describes how its calculation relates rows. Keeping both clauses explicit makes the query readable even when its display order eventually differs from its calculation order.

The test suite exercises exactly that separation. It calculates partitioned sums and changes the outer ordering independently. This is useful evidence that window ordering is part of the computation, not a substitute for ordering the returned result.

A frame is part of the calculation

The frame determines which part of the partition contributes to an aggregate at a particular row. A running total and a whole-account total can share the same partition while answering different questions. Naming the frame makes that difference visible in the SQL.

The release tests include ROWS frames with a bounded number of preceding rows and with unbounded endpoints. They also include RANGE examples. Those forms should not be treated as interchangeable decoration. A query author needs to choose the relationship among rows that the calculation requires.

Ordering by a unique key in the ledger example avoids an unresolved ordering choice among equal keys. If entries instead share a timestamp, a running calculation needs a deliberate policy for how those equal timestamps should be treated. Adding a unique tie-breaker can establish a row sequence when the application needs one.

Built-in functions extend the facility beyond sums. The implementation commentary in the release's window.c lists ranking functions and functions for accessing another row's value, including lag() and lead(). These let a statement describe comparisons that otherwise invite a loop over ordered query results.

Ranking also requires a clear definition of the ordering keys. A row number identifies a position under the chosen order. It does not create a business identity for the row. Applications still need their own primary keys when a result will later be used to address stored records.

SQLite rewrites the SELECT to process its windows

The implementation explains how the engine arranges the work. A SELECT containing window functions is rewritten so an inner query supplies rows ordered for window processing. FROM and WHERE move into that subquery, together with GROUP BY and HAVING. The outer query retains its result ordering and limit.

SQLite disables flattening for the transformed query. That preserves the structure required by the window implementation. The inner query runs as a coroutine, and generated virtual-machine code calculates the window results associated with each output row.

Multiple functions using the same window declaration can share a scan. Functions with different partitioning or ordering requirements can require nested transformations so each receives rows in the order it needs. A compact SELECT can therefore contain several distinct arrangements of its input.

The implementation also says that some functions and frames cache an entire partition in a temporary table before returning rows. Others do not require that behavior. Window syntax avoids an application-side pass, but it does not imply that the engine can compute every result with a single accumulator and constant storage.

That mechanism is a useful guide to query design. Two calculations that use the same definition have an opportunity to share work. Different ordering requirements express additional work even when the SQL remains short. The release's source commentary explains these consequences more directly than a claim that analytical SQL is automatically faster.

Window functions belong in the result list or ORDER BY. The regression tests reject their use directly in WHERE, GROUP BY and HAVING. When a result must be filtered by a calculated window value, a surrounding query can provide a separate filtering stage. This follows the distinction between selecting the input rows and calculating a window over that input.

A column rename has dependent SQL to preserve

The new migration syntax names the table and the old and new column names. In the illustrative ledger, a rename can clarify that the amount uses minor currency units. That operation changes the identifier through which SQL refers to the value. It does not convert the stored numbers or determine which currency they represent.

ALTER TABLE entries RENAME COLUMN amount TO amount_minor;

The name changes, but references elsewhere in the schema make the implementation more involved than replacing one token in a table declaration. SQLite stores SQL definitions for schema objects. A column can appear in an index expression, a constraint or a trigger body.

The column-rename regression tests show those dependencies explicitly. They rename columns appearing in CHECK constraints, composite primary keys and expression indexes. The expected schema SQL reflects the new name at the relevant references.

Views and triggers receive similar attention. A test renames a column used in a view and checks that the stored view definition changes. Another checks a trigger containing several references to the renamed column, including references through NEW. The engine resolves the identifier's role before changing the SQL that refers to it.

That is necessary because the same spelling can mean different things in different contexts. Blind text replacement cannot reliably distinguish a column reference from unrelated text. The versioned tests include quoted identifiers and expressions, making the problem visible beyond the simplest table definition.

A rename also needs a coherent surrounding schema. One test contains an invalid view referring to nonexistent columns and expects an error when a rename encounters it. A migration operates within the schema's dependency relationships, so an unrelated-looking definition can matter when SQLite parses and resolves those relationships.

Applications still own queries outside the schema

Automatic schema rewriting covers definitions SQLite knows about. An SQL string embedded in an application's source remains outside the database. A migration that renames amount to amount_minor still needs the application's later queries to use the new name.

This distinction is especially relevant to a database file shared with several tools. SQLite can preserve a stored view's reference, but a consumer issuing its own SELECT has a separate dependency on the schema. The new command simplifies the database operation without making every external consumer aware of the change.

I prefer column names that state what a value means, so a supported rename is useful maintenance functionality. The gain is that the engine can preserve the references it owns while the application changes the references it owns. Window functions create a similar improvement for query logic, letting SQL describe relationships among detail rows while the application remains responsible for their meaning and presentation.