INSERT OR IGNORE Fails on Virtual Table Due to Constraint Handling

Virtual Table Constraint Enforcement and Conflict Resolution Mismatch

Issue Overview

The problem arises when using the INSERT OR IGNORE statement to batch-insert rows into a SQLite virtual table that implements the xUpdate method and enforces a NOT NULL constraint on a specific column. The virtual table’s xUpdate method returns SQLITE_CONSTRAINT_NOTNULL when a row violates the NOT NULL constraint. However, instead of ignoring such rows (as intended by the OR IGNORE conflict clause), the entire INSERT operation aborts, propagating the constraint error back to the caller. This behavior undermines the goal of inserting all valid rows while skipping invalid ones, particularly when a subset of source data contains NULL values in the constrained column.

The scenario involves a custom virtual table designed to store computed values generated by a slow, deterministic function (slow_fn). The function is invoked during the INSERT operation via a SELECT subquery. The challenge is twofold:

1. Constraint Handling: The virtual table’s strict enforcement of the NOT NULL constraint overrides the OR IGNORE conflict resolution strategy.
2. Performance Overhead: Workarounds that filter out NULL values before insertion (e.g., WHERE slow_fn(someColumn) IS NOT NULL) result in redundant function calls, doubling execution time for a computationally expensive operation.

The root issue stems from SQLite’s default behavior when interacting with virtual tables. Unlike standard tables, virtual tables do not automatically honor SQLite’s conflict resolution policies unless explicitly configured to do so. This leads to a mismatch between the conflict clause specified in the SQL statement and the virtual table’s internal handling of constraint violations.

Virtual Table Constraint Support Configuration and xUpdate Semantics

Possible Causes

1. Missing Constraint Support Flag:
   Virtual tables must explicitly enable constraint awareness by invoking sqlite3_vtab_config(db, SQLITE_VTAB_CONSTRAINT_SUPPORT). Without this configuration, SQLite assumes the virtual table does not enforce constraints, bypassing conflict resolution logic. The xUpdate method’s constraint violations (e.g., SQLITE_CONSTRAINT_NOTNULL) are treated as fatal errors, aborting the entire statement.

2. Incorrect xUpdate Implementation:
   Even with constraint support enabled, the virtual table’s xUpdate method must correctly interpret the conflict mode provided by SQLite during insertion. The conflict mode (e.g., SQLITE_IGNORE, SQLITE_ABORT) is passed as an argument to xUpdate, but the method may fail to handle it, leading to unintended rollbacks.

3. Deterministic Function Re-Evaluation:
   When filtering NULL values via a WHERE clause (e.g., WHERE slow_fn(someColumn) NOT NULL), SQLite’s query optimizer may re-evaluate slow_fn for the same row, despite its DETERMINISTIC declaration. This occurs because subqueries or projections in the SELECT statement are treated as separate execution stages, preventing result caching across clauses.

4. Virtual Table Bytecode Misalignment:
   The EXPLAIN output shows a VUpdate opcode with a conflict mode value of 4 (corresponding to SQLITE_IGNORE). However, if the virtual table’s internal logic does not recognize this mode, it may default to SQLITE_ABORT, overriding the conflict resolution strategy.

Enabling Constraint Awareness and Optimizing Function Evaluation

Troubleshooting Steps, Solutions & Fixes

Step 1: Enable Virtual Table Constraint Support
Modify the virtual table’s initialization logic to signal constraint awareness:

sqlite3_vtab_config(db, SQLITE_VTAB_CONSTRAINT_SUPPORT);  

This must be called during the virtual table’s creation (e.g., in the xCreate or xConnect method). After this change, SQLite will propagate conflict resolution modes (e.g., OR IGNORE) to the virtual table’s xUpdate method.

Step 2: Implement Conflict Handling in xUpdate
The xUpdate method receives the conflict mode as an argument. Adjust the method to honor SQLITE_IGNORE:

if (conflictMode == SQLITE_IGNORE) {  
    // Skip the row without raising an error  
    return SQLITE_OK;  
} else {  
    // Enforce constraint and return SQLITE_CONSTRAINT_NOTNULL if violated  
}  

This ensures that rows violating the NOT NULL constraint are silently skipped when OR IGNORE is specified.

Step 3: Prevent Redundant Function Calls
To avoid re-evaluating slow_fn, use a materialized subquery or Common Table Expression (CTE) to cache results:

INSERT OR IGNORE INTO myvtab (docid, vector)  
WITH precomputed AS (  
    SELECT docid, slow_fn(someColumn) AS vec  
    FROM sourceTable  
)  
SELECT docid, vec FROM precomputed WHERE vec IS NOT NULL;  

While this approach may still invoke slow_fn once per row, it prevents duplicate calls by materializing the result in a temporary scope.

Step 4: Verify Bytecode and Conflict Mode
Use EXPLAIN to confirm the VUpdate opcode’s conflict mode:

addr | opcode      | p1 | p2 | p3  | p4             | ...  
19   | VUpdate     | 1  | 7  | 1   | vtab:11F894540 | ...  

Register r[5] holds the conflict mode. A value of 4 (SQLITE_IGNORE) confirms the correct conflict resolution is active.

Step 5: Benchmark and Optimize
If performance remains suboptimal, precompute slow_fn results in a temporary table:

CREATE TEMP TABLE temp_results AS  
SELECT docid, slow_fn(someColumn) AS vec FROM sourceTable;  

INSERT OR IGNORE INTO myvtab (docid, vector)  
SELECT docid, vec FROM temp_results WHERE vec IS NOT NULL;  

This isolates the expensive function call from the insertion logic, ensuring it is executed exactly once per row.

Step 6: Update SQLite Version
Ensure SQLite is updated to version 3.40.1 or newer, as earlier versions contain edge-case bugs in virtual table constraint handling.

Final Solution Summary

1. Enable SQLITE_VTAB_CONSTRAINT_SUPPORT during virtual table initialization.
2. Modify the xUpdate method to respect SQLITE_IGNORE conflict modes.
3. Refactor the INSERT statement to materialize slow_fn results, avoiding re-evaluation.

By addressing both the virtual table’s constraint configuration and the query structure, the INSERT OR IGNORE operation will correctly skip invalid rows while preserving performance.