SQLite 3.37.1 fixes competing conflict policies in UPSERT

A table can declare one conflict policy while an insert names another. SQLite has to compile the interaction into a valid program even when one path is unusual. The December 30 release of SQLite 3.37.1 fixes incorrect bytecode generation for some valid UPSERT statements. The notes trace the defect to the UPSERT enhancements in 3.35.0 and say that it could lead to a null-pointer dereference.

The release's regression test provides a more concrete shape than the short release-note description. It combines an integer primary key whose declared conflict policy is REPLACE with an UPSERT targeting a different unique column. That is a useful case to understand because the schema and the statement each contribute rules. Neither can be interpreted in isolation.

I value SQLite's ability to express conflict handling inside a write. It keeps the uniqueness decision close to the constraint that defines it. This patch shows why the compiler must also account for conflict policies declared elsewhere in the schema.

The regression case uses two different constraints

The UPSERT tests in the 3.37.1 tag include a case attributed to Jingzhou Fu, with a comment dated December 29. The comment identifies the problematic combination as INTEGER PRIMARY KEY ON CONFLICT REPLACE and an UPSERT on another constraint. Its expected result is small enough to examine directly.

CREATE TABLE t1(
  a INTEGER PRIMARY KEY ON CONFLICT REPLACE,
  b UNIQUE
);
INSERT INTO t1(b) VALUES(22);
INSERT INTO t1 VALUES(2,22)
  ON CONFLICT(b) DO NOTHING;
SELECT * FROM t1;

The test expects the existing row, with primary key 1 and value 22, to remain. The attempted second insert targets a conflict on b, and its specified action is to do nothing. A primary-key declaration's replacement policy still forms part of the schema the compiler must process, even though this example supplies a different primary-key value.

This matters because a defect in compilation need not correspond to an ambiguous application requirement. The SQL can have a clear expected result while the generated instruction sequence is invalid. The release notes and test comment describe that failure class. They do not say that every UPSERT crashes or that every use of REPLACE produces bad data.

The example is the released regression case, not a recommendation to combine policies unnecessarily. An application reading such a schema must examine the declared constraints as well as the text of each insert. Statement-local review alone can miss behavior that the schema contributes.

Conflict targets identify particular uniqueness rules

Earlier cases in the same test file explain the broader constraint model. A conflict target that names a nonexistent column fails. A target that names a column with no matching primary-key or unique constraint fails too. The target is not merely an arbitrary list of fields on which the application would prefer to ignore errors.

The tests also cover a partial unique index. An insert's conflict target must match the relevant constraint shape, including its predicate. Another case creates an expression index and verifies that changing the expression can prevent a target from matching. These details explain why UPSERT compilation needs structural information about the database's constraints.

The March 3.35.0 release notes document the enhancements that preceded this fix. SQLite added multiple ordered ON CONFLICT clauses and allowed a final targetless clause to use DO UPDATE. The December patch corrects implementation behavior within that expanded functionality. It does not introduce those syntax forms again.

I prefer the narrow explanation because it gives maintainers something concrete to compare with generated SQL and table definitions. The important combination involves a schema-level integer-primary-key replacement policy and an UPSERT on another constraint. Reducing it to a generic warning about complicated SQL would lose the part the regression case actually establishes.

Corrupt FTS5 files exercise a different boundary

The patch also fixes an out-of-bounds read in FTS5 when reading corrupt database files. That is a separate failure class from the valid UPSERT statement. One concerns compilation of accepted SQL. The other concerns how an extension handles database contents that are already corrupt.

The distinction limits what can be concluded about ordinary searches. The notes do not claim that correctly formed FTS5 databases produce this read, nor do they present a general corruption-repair operation. They identify a read outside the expected bounds while processing corrupt input. Correcting that behavior makes the reader more robust without changing the meaning of an ordinary full-text query.

Version 3.37.1 also strengthens the command-line application's --safe option. That option belongs to the CLI, and the release lists its robustness work separately from the SQL engine and FTS5 fixes. A program embedding SQLite should therefore identify which part it uses when assessing the patch. The December release carries forward November's STRICT-table additions, but its new work is maintenance, including valid UPSERT bytecode generation for the competing-policy case and safer handling of malformed FTS5 content.