SQLite fixes an aggregate query simplified into the wrong result

Simplifying a false expression can change more than a value if it also removes the aggregate that determines a query's shape. That is the problem in one of the fixes shipped in SQLite 3.50.3 on 17 July. A query over an empty table behaved differently when the zero in an AND expression was a literal instead of a bound parameter.

The distinction is small enough to look like an oddity in a test. It matters because an optimizer must preserve the number of result rows as well as their values. An aggregate query that returns one false value and a nonaggregate query that returns no rows are different SQL results, even when the application informally describes both as false.

The release also repairs a possible memory error when querying an FTS5 index deliberately corrupted in a specific way. These are separate fixes with different preconditions. The aggregate example gives a particularly clear account of the SQL correctness problem, while the FTS5 item concerns safe handling of malformed database contents.

The report's initial expectation was reversed

The 1 July forum report creates an empty table and prepares a query containing MAX(column) AND (?). After binding the parameter to zero, the program receives one row containing zero. The reporter expects no row and observes that substituting a literal zero produces that empty result.

Richard Hipp reduces the example and says the parameterized query is the correct one. That response is the important part of the report. Reproducing a difference does not, by itself, identify which behavior is wrong. The expected result must come from the query's semantics, not from treating the first observed result as the reference.

The simplified shell example uses an empty table and a parameter named $zero. It compares the literal and bound forms directly.

CREATE TABLE empty(x);
.parameter set $zero 0
SELECT max(x) AND 0 FROM empty;
SELECT max(x) AND $zero FROM empty;

The dot command belongs to the SQLite shell and is not SQL sent through an application API. An application can make the same comparison by preparing the parameterized statement and binding an integer zero, as the original report does. The SQL expressions are deliberately small so that joins and schema complexity cannot explain the difference.

Constant folding must preserve aggregate classification

The forum discussion explains that the literal form simplifies during query preparation. Reducing an expression ending in AND 0 to false seems locally reasonable. But the discarded expression contains max(x), which makes the statement an aggregate query. Losing that information affects how the statement produces results on empty input.

The parameterized form retains the aggregate operation. The aggregate produces NULL for the empty input, and the AND with zero yields false. This produces one row. In the faulty literal form, simplification leaves a false expression evaluated over an empty table, so there is no input row from which to produce a result.

That is a useful distinction between expression equivalence and statement equivalence. An expression rewrite needs to retain information that the surrounding statement uses. A value can be known in advance while the presence of an aggregate still matters for result cardinality. Treating the expression's final value as its only observable property is insufficient.

The same reasoning explains why the bug can affect code that uses parameters consistently and still appear during a seemingly harmless SQL refactor. Replacing a bound constant with a literal changes the information available during preparation. The intended result should stay the same. The engine is responsible for preserving that equivalence through optimization.

Empty input is a separate case from a false value

Applications often consume a query through APIs that distinguish an absent row from a row containing a false or NULL value. A function returning an optional result can therefore behave differently from one returning a boolean. Collapsing these cases in a test would conceal the defect that the forum example exposes.

The comparison needs to inspect both whether a row exists and what it contains. Checking only the first value after substituting a default for an absent row can make two different results appear identical. Conversely, counting rows alone would miss a wrong value in a query that still returned the expected single row.

I value SQLite's ability to run these small relational operations inside application code. That makes the result contract especially important. A caller should not need a separate interpretation of aggregate behavior depending on whether a constant arrives through SQL text or a binding API.

The report also illustrates the usefulness of minimization. Its entire schema is one empty table. The defect remains visible without application-specific data, an ORM, or a complicated plan. The shorter statement makes it possible to discuss the incorrect transformation directly and avoids attributing the behavior to unrelated infrastructure.

The FTS5 fix has a different input boundary

The July release describes the FTS5 issue as a possible memory error involving a deliberately corrupted index. It does not say that ordinary FTS5 queries corrupt their own indexes. Nor does the release note establish a particular exploit outcome. The stated scope supports a concrete conclusion about malformed-input handling without requiring either of those broader claims.

SQLite's security guidance treats malicious SQL and malformed database files as inputs the engine should validate safely. That principle matters for an embedded database because applications can receive files produced elsewhere. Accepting a file gives its internal structures to database code, even when the application supplies only its own fixed SQL statements.

A parameter binding addresses the representation of a query value. It does not validate every byte of the database file being queried. The aggregate bug and the FTS5 memory bug therefore occupy different parts of an integration. One concerns a transformation of valid SQL. The other concerns what happens when the engine encounters a specially malformed index.

I regard both as relevant to the same library upgrade, without treating them as one failure mode. Their evidence and expected behavior differ. The aggregate example requires equivalent row counts and values for literal and bound zero. The FTS5 fix requires safe behavior for the particular malformed structure described by the release.

For the aggregate query, 3.50.3 restores the relationship the reduced example makes visible. A preparation-time simplification must retain the fact that the statement contains an aggregate, even when its final expression value is already false. The result on empty input remains a row, which the caller can distinguish from the absence of any result.