SQLite repairs an UPSERT index inconsistency

A query that adds a filter should not discover an extra row because it took a different route through the database. That was the visible symptom in the report behind SQLite 3.45.2's UPSERT correction. The table and its index disagreed after a particular replacement statement, so two queries over the same logical data could return incompatible counts.

Released on March 12, SQLite 3.45.2 corrects that problem and narrows a separate optimization involving NULL tests. Both defects trace to changes introduced with 3.35.0 three years earlier. Their age is relevant because neither problem belongs only to applications adopting January's JSONB release. The patch repairs behavior in existing SQL operations.

I place considerable weight on SQLite's file format and its role as an engine inside an application. An index inconsistency matters in that setting because a program can receive an answer without a process crash or an obvious error. The maintainer's explanation is unusually helpful about the precise trigger and the extent of the repair.

Redundant conflict clauses can damage an index

The March 8 report constructs a table with an integer primary key and a second column carrying a unique constraint. It inserts two rows, then runs a REPLACE INTO statement containing two ON CONFLICT clauses for the same unique column. That redundancy is central to the defect described by Richard Hipp in his response.

The original reporter compares an unrestricted count with a count filtered on the unique column. The unrestricted query returns two rows, while the filtered query returns three. That discrepancy exposes an index that no longer describes the table correctly. It is stronger evidence of a consistency problem than a query merely returning an unexpected application value.

Hipp identifies the generalized UPSERT work in 3.35.0 as the origin. His response says a replacement statement with redundant UPSERT clauses can corrupt a unique index. It also distinguishes the flaw from a memory-safety vulnerability or a route to unauthorized access. The stated prerequisite for a malicious use is the ability to execute arbitrary SQL against the database.

That is a specific assessment, rather than a reason to dismiss the correction. An application generating unusual SQL can still care about maintaining its indexes correctly. The source does not establish that every UPSERT, every conflict clause, or an ordinary replacement statement triggers the bug. Broadening the description to those cases would misrepresent the evidence.

Detection and repair address existing damage

The maintainer states that PRAGMA integrity_check detects corruption caused by this defect and that REINDEX repairs it. Those statements give an operator two distinct operations with different purposes. Checking reports whether the existing structures agree. Rebuilding replaces index contents using the table data.

PRAGMA integrity_check;
REINDEX;
PRAGMA integrity_check;

This sequence illustrates the detection and repair operations named in the report. It is not evidence that the current database has suffered the bug, and running the first command does not itself repair anything. The important distinction is between installing an engine that no longer produces this inconsistency and rebuilding an index that already contains it.

The release note records the corrected engine behavior, while the forum response supplies the recovery detail. Reading them together prevents an incomplete account in which changing the library version somehow implies that all previously written index entries have changed too. The two actions operate on different things.

I appreciate that the explanation gives a bounded recovery mechanism. It lets the discussion stay about a particular index defect, rather than turning a rare SQL construction into an unsupported claim about general database corruption. The table contents remain the basis for the repair described by the maintainer.

A declared constraint does not describe every query result

The other substantive correction concerns NOT NULL strength reduction. The optimizer had simplified X IS NULL to false, or X IS NOT NULL to true, when it knew the underlying column was declared not nullable. This reasoning can avoid unnecessary expression work. It fails when a query produces a value whose origin includes aggregation over an empty input.

In the related forum discussion, Hipp reduces the problem to a small example. An empty table has a column declared NOT NULL, and an aggregate query selects a NULL test on the bare column beside a count. The aggregate still produces a result, but the bare column's value is NULL in that result.

CREATE TABLE t1(X INT NOT NULL);
SELECT X IS NULL, count(*) FROM t1;

The correct result is 1 for the NULL test and 0 for the count. Previously, the faulty optimization returned 0 and 0. The column constraint describes values stored in table rows. With no rows contributing to this aggregate, it does not justify the same claim about the bare expression's output.

The correction described in the thread restricts the simplification to expressions inside the WHERE clause. Placement matters because a condition over input rows and an expression in the aggregate's output have different semantics. An optimizer cannot carry a useful fact into every later context merely because the expression retains the same column name.

The two fixes preserve different invariants

The UPSERT defect concerns agreement between stored table data and an index. The NULL defect concerns agreement between SQL semantics and a simplified expression. Their visible symptoms can both be wrong query results, but the mechanisms and recovery implications differ. Rebuilding an index addresses the first mechanism's existing damage, while it has no role in correcting the optimizer's reasoning about an empty aggregate.

The examples also explain why small reports can be valuable even when their SQL looks unusual. Redundant conflict clauses exercise a path through replacement and index maintenance. An empty aggregate removes ordinary row values and reveals an assumption about nullability. Each example isolates a condition that is easy to overlook in a larger application query.

For me, the useful maintenance story in 3.45.2 is the specificity of those explanations. Applications get a corrected engine, and the public discussions identify what the engine previously got wrong. The release restores index consistency for the reported UPSERT construction and keeps a row-level constraint from determining an aggregate result where that constraint does not apply.