Unexpected Row Counts Due to Redundant ON CONFLICT Clauses in SQLite

Conflict Resolution Ambiguity Leading to Index Corruption

Issue Overview

The core problem arises when executing a REPLACE INTO statement containing redundant ON CONFLICT clauses targeting the same unique constraint column. This redundancy creates ambiguity in SQLite’s conflict resolution logic, leading to index corruption. The corruption manifests as discrepancies between the data stored in tables and their associated indexes. For example, a SELECT query filtering on a column with a unique constraint might return more rows than expected because the index contains stale or duplicated entries. The corruption is not immediately visible in the table itself but becomes apparent when querying through indexes.

The example scenario involves a table v0 with two unique constraints: c1 as a PRIMARY KEY with ON CONFLICT REPLACE and c2 as a standalone UNIQUE constraint. A REPLACE INTO operation attempts to update c1 and c2 using two ON CONFLICT (c2) clauses. The first ON CONFLICT clause tries to update c1 and c2 using values derived from the conflicting row, while the second clause attempts a no-op update. This redundancy confuses SQLite’s upsert logic, causing it to mishandle index updates. The result is an index entry that points to a non-existent row or duplicates entries for the same unique key.

The immediate symptom is a mismatch between the row count returned by a full table scan (SELECT count(*) FROM v0) and an indexed query (SELECT count(*) FROM v0 WHERE c2 > 8). The former reads directly from the table, while the latter relies on the corrupted index, leading to inconsistent results. This inconsistency violates SQLite’s guarantees about data integrity and unique constraints.

Underlying Causes of Redundant Conflict Clause Corruption

The root cause is a bug introduced in SQLite 3.35.0’s “Generalized UPSERT” feature, which allowed multiple ON CONFLICT clauses for a single statement. Prior to this version, SQLite would reject such redundancy. The bug occurs when a REPLACE INTO statement includes multiple ON CONFLICT clauses targeting the same unique constraint. During execution, SQLite’s query planner fails to properly reconcile these clauses, leading to incomplete or incorrect index updates.

The REPLACE INTO operation itself is a higher-level abstraction built on top of INSERT OR REPLACE. When a conflict occurs, SQLite deletes the existing row and inserts a new one. However, with multiple ON CONFLICT clauses, the planner may attempt to resolve the conflict using both clauses in sequence, which is logically impossible. This creates a race condition in the index update phase: the first conflict resolution modifies the index, while the second resolution operates on outdated index state, leading to duplication or orphaned entries.

The c1 column’s ON CONFLICT REPLACE policy adds complexity. When a conflict on c1 occurs, the existing row is deleted, but the ON CONFLICT (c2) clauses introduce additional logic that may reference the deleted row’s values. This creates a transient state where the table and indexes are temporarily inconsistent. Under normal circumstances, SQLite’s transaction rollback mechanism would correct this, but the redundant clauses disrupt the rollback logic, leaving the database in an inconsistent state.

Resolving Index Corruption and Preventing Redundant Conflict Clauses

To diagnose this issue, run PRAGMA integrity_check;. This command scans all indexes and verifies their consistency with the underlying tables. If corruption is detected, the output will indicate which indexes are invalid. For example, it might report "rowid X missing from index sqlite_autoindex_v0_1" or "duplicate entry in index sqlite_autoindex_v0_2."

To repair the corruption, execute REINDEX v0;, which rebuilds all indexes associated with the table v0. This ensures that the indexes accurately reflect the current table data. After reindexing, rerun the SELECT queries to confirm consistency.

To prevent recurrence, upgrade SQLite to version 3.45.2 or later, where the bug has been patched. In fixed versions, redundant ON CONFLICT clauses are either rejected or handled correctly. Additionally, avoid writing REPLACE INTO statements with multiple ON CONFLICT clauses targeting the same column. Instead, consolidate conflict resolution logic into a single clause. For example, rewrite the problematic REPLACE statement as:

INSERT INTO v0 VALUES (0, 11) 
ON CONFLICT (c2) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2;  

This uses the excluded pseudo-table to reference values from the aborted INSERT, ensuring deterministic updates.

For existing applications, audit all UPSERT and REPLACE statements to ensure no redundant conflict clauses exist. Use static analysis tools or SQL linters to flag such patterns during development. If backward compatibility with older SQLite versions is required, refactor queries to use a single ON CONFLICT clause per statement.

In scenarios where multiple unique constraints might trigger conflicts, prioritize conflict resolution using a single ON CONFLICT clause with conditional logic. For example, use CASE expressions within the DO UPDATE SET clause to handle different conflict scenarios:

INSERT INTO v0 VALUES (0, 11)  
ON CONFLICT (c2) DO UPDATE SET  
c1 = CASE WHEN excluded.c1 IS NOT NULL THEN excluded.c1 ELSE c1 END,  
c2 = CASE WHEN excluded.c2 IS NOT NULL THEN excluded.c2 ELSE c2 END;  

This approach centralizes conflict resolution while maintaining index integrity.

For advanced use cases requiring multiple conflict targets, consider splitting the operation into separate statements within a transaction. For example:

BEGIN TRANSACTION;  
INSERT INTO v0 VALUES (0, 11) ON CONFLICT (c1) DO NOTHING;  
INSERT INTO v0 VALUES (0, 11) ON CONFLICT (c2) DO UPDATE SET c1 = excluded.c1;  
COMMIT;  

This ensures each conflict is handled atomically, avoiding the ambiguity of redundant clauses.

In summary, the corruption caused by redundant ON CONFLICT clauses is resolved by rebuilding indexes, upgrading SQLite, and refactoring queries to eliminate redundant conflict resolution logic. Proactive measures include rigorous query reviews, automated linting, and adopting deterministic conflict handling patterns.