SQLite 3.44.0 makes aggregate input order explicit

A grouped string can contain the correct values and still be wrong for its consumer because the values appear in the wrong sequence. The SQLite 3.44.0 release, dated November 1, allows an aggregate call to contain its own ORDER BY clause after the last argument. The aggregate processes its inputs in that order.

This directly expresses a requirement that otherwise sits outside the function call. A query constructing a string or array can say which row comes first. An outer order on the query's result rows answers a different question, because it orders the results after aggregation has produced them.

I like SQLite's ability to put a useful relational calculation inside a small application. Aggregate-local ordering makes that calculation easier to state precisely. It keeps the sequence requirement with the operation that consumes the sequence, where a reader of the SQL can find it.

Ordering inputs and ordering results

Suppose an application stores the members of several named groups. Each member has a display position and a stable identifier. The application needs a comma-separated list for each group, with members in display order. The following example defines a complete ordering for each aggregate.

CREATE TABLE members(
  group_id INTEGER,
  member_id INTEGER PRIMARY KEY,
  position INTEGER,
  name TEXT
);

SELECT
  group_id,
  string_agg(name, ', ' ORDER BY position, member_id)
FROM members
GROUP BY group_id
ORDER BY group_id;

The order inside string_agg() controls the member sequence within each resulting string. The final ORDER BY group_id controls the sequence of result rows. Removing that final clause would not change what the aggregate's own order asks it to do.

Including member_id resolves equal positions in this example. If two names share a position and the application specifies no further order, that requirement alone does not decide their relative sequence. A stable secondary key makes the requested output more precise. It is an application choice, not an additional behavior automatically supplied by the new syntax.

The aggregate documentation at the release date says concatenation order is arbitrary without an order argument. That is the relevant contract. A result that happened to appear in the desired order under one access plan did not establish that the query requested that order.

The feature can also matter to json_group_array(), which the release notes name alongside string aggregation. A sequence of array elements can carry meaning even when all elements are individually correct. The new syntax gives the aggregate an explicit ordering instruction, without requiring the application to infer a guarantee from the arrangement of surrounding SQL.

A familiar name for string aggregation

SQLite 3.44.0 adds string_agg(X,Y) as an alias for group_concat(X,Y). The two-argument form includes an explicit separator. The release describes compatibility with PostgreSQL and SQL Server, while the existing group_concat() name is familiar from MySQL.

That naming addition is useful for SQL shared conceptually across systems. It does not mean that every aggregate expression from another engine can be copied without checking its syntax and semantics. The precise SQLite contract remains the alias and behavior documented for these arguments.

The aggregate concatenates non-NULL values. A NULL name consequently does not contribute a text item with a separator of its own. If an application needs to display a placeholder for a missing name, the query must request that representation before aggregation. Missing data and an empty string are different inputs.

The existing one-argument group_concat(X) form retains its default comma separator. string_agg() supplies the familiar two-argument spelling. An application can choose a name for readability or compatibility while still stating the ordering and missing-value policy that its output requires.

These details matter in exported reports and generated text. A consumer may compare strings directly, even when the application treats their components as a set. If sequence is immaterial to the business meaning, stable ordering can still make textual representations easier to compare. That benefit follows from explicitly choosing the sequence.

Concatenating fields within a row

The release also adds the scalar functions concat() and concat_ws(). They operate on values within an expression, rather than combining values from multiple rows in a group. The core-function documentation from November 1 defines how they handle NULL arguments.

The concat() function joins the string representations of its non-NULL arguments. If all arguments are NULL, it returns an empty string. This means an application can combine optional fragments without every absent fragment making the result absent.

SELECT concat('entry-', NULL, 7);

For this expression the result is entry-7. The NULL argument contributes no text. The integer contributes its string representation. That behavior is part of the function definition, so a caller does not need to turn the missing fragment into an empty string separately.

The concat_ws() function takes its separator first. It then joins the non-NULL arguments after that separator. A NULL separator makes the result NULL. If every argument after a non-NULL separator is NULL, the result is an empty string.

SELECT concat_ws(' / ', 'north', NULL, 'archive');

This produces north / archive. There is no extra separator corresponding to the missing middle value. The function therefore suits a particular presentation policy in which absent fields disappear along with the separators that would have joined them.

It does not provide an escaping or serialization format. If values can contain the separator, the resulting text alone does not define how another program should reconstruct the original fields. Choosing a display string and choosing a reversible data representation are separate application decisions.

My preference is to use these functions where their NULL behavior matches the intended display. A compact expression is useful only if its treatment of absent fields is also correct. The names improve familiarity, while the documented rules determine whether the expression fits the data.

Combining scalar and aggregate operations

A query may first construct one display value per row and then combine those values across a group. In that case concat_ws() governs optional fields within a member's label, while string_agg() governs the sequence of member labels. Keeping those operations distinct makes the two missing-value and ordering decisions visible.

For example, a person's optional title can disappear within a label without removing the person from an ordered group. Alternatively, an application may want an entirely missing label to contribute no aggregate element. Those are different policies that should follow the data model, not an accidental consequence of a familiar function name.

The release does not require an application to change existing expressions. An existing group_concat() call remains available. The new aggregate ordering lets a caller strengthen its request where the sequence matters. The scalar functions add another way to express how values become text.

There is also a compatibility boundary at the SQL parser. A query using aggregate-local ordering or these new functions requires an engine that supports them. An application with several embedded SQLite copies needs to know which copy executes the statement. Upgrading a development CLI alone does not change a separately bundled application library.

Ordering is useful only where sequence matters

The aggregate documentation names count and maximum as operations whose input order does not affect the result. Supplying an order to those functions does not create a new counting or comparison rule. The main reason to use the new syntax is an operation whose construction depends on the incoming sequence.

Filtering is another independent decision. An aggregate's FILTER clause includes only rows for which its condition is true. That determines membership in the aggregate, while ORDER BY determines the sequence of the included inputs. A report that needs only selected members in a particular order has both requirements. Expressing them separately makes it possible to review whether the correct rows entered the string before examining their presentation.

The release tightens other semantic checks

SQLite 3.44.0 also moves many errors associated with CREATE TABLE to execution of the create statement itself. Previously, some appeared only when the table was first used. Earlier reporting makes the invalid definition visible at the operation that introduces it, reducing the distance between the schema change and its diagnostic.

Another addition lets PRAGMA integrity_check verify content consistency in several built-in virtual tables through the new xIntegrity method. The release names the FTS and spatial extensions it covers. This extends what the integrity operation examines, while the aggregate feature extends what a query can state about its own input.

The planner changes remain separate from the ordering contract. This release changes partial-index reasoning and disables a view-scan optimization that caused performance regressions. An application should not infer an aggregate sequence from whichever plan those optimizations select. The new syntax specifies that sequence directly, so the SQL records the required result while the planner chooses how to produce it.