Unexpected Empty Results with Partial Indexes and DISTINCT in SQLite

Issue Overview: Partial Index UNIQUE Constraint Violation Suppressing INSERT and Affecting DISTINCT Query

The core issue revolves around a misunderstanding of how SQLite’s partial unique indexes interact with INSERT statements and how subsequent queries process NULL values in indexed columns. The problem manifests when attempting to insert multiple rows with values that violate the partial unique index’s constraint, leading to an unexpected empty table state that causes subsequent SELECT DISTINCT queries to return no results.

In the described scenario, the user creates a table v0 with two columns (v1 INT, v2) and defines a partial unique index v3 on v0(v1) with the condition v1 > 20. Two INSERT statements follow:

1. INSERT INTO v0(v1) VALUES (2), (22), (22)
2. INSERT INTO v0(v2) VALUES (2), (22), (22)

The first INSERT attempts to insert three rows into v1: 2, 22, and 22. The second INSERT adds three rows into v2: 2, 22, and 22. The user expects the first SELECT statement to return six rows (three from each INSERT), but the actual output shows only three rows. The second SELECT DISTINCT query targeting v1 > 20 returns empty results instead of the expected {22 | NULL}.

The root cause lies in SQLite’s handling of constraint violations during batch INSERT operations. The partial unique index v3 enforces uniqueness on v1 values greater than 20. When the first INSERT attempts to insert two instances of 22 into v1, it violates this constraint. SQLite treats multi-row INSERT statements as atomic transactions: if any row violates a constraint, the entire INSERT fails and rolls back. Consequently, none of the three rows from the first INSERT are persisted. Only the second INSERT succeeds, creating three rows where v1 is NULL and v2 contains 2, 22, and 22.

The SELECT DISTINCT query fails to return expected results because all v1 values in the table are NULL after the failed INSERT. The WHERE clause v1 > 20 filters out NULL values (since NULL comparisons yield unknown), resulting in an empty result set. This behavior is consistent with SQL standards but contradicts the user’s expectations due to a misdiagnosis of the table’s actual state post-INSERT.

Possible Causes: Partial Index Enforcement, Atomic INSERT Rollback, and NULL Comparison Semantics

1. Partial Unique Index Enforcement on Batch INSERT Operations

The partial index v3 is defined as UNIQUE on v1 for rows where v1 > 20. When inserting multiple rows in a single INSERT statement, SQLite evaluates the uniqueness constraint after applying the index’s WHERE condition. Rows with v1 <= 20 are ignored by the index, but rows with v1 > 20 must adhere to the UNIQUE constraint. In the first INSERT, the two 22 values violate uniqueness, causing the entire statement to fail.

Misconceptions arise when users assume that only the violating row is rejected, but SQLite’s atomic transaction model aborts the entire INSERT. This leads to an empty v1 column in the table, as the second INSERT targets v2 exclusively, leaving v1 NULL for all rows.

2. NULL Values in WHERE Clauses and DISTINCT Processing

The WHERE clause v1 > 20 in the second SELECT statement excludes rows where v1 is NULL because NULL is not greater than 20 under SQL’s three-valued logic (NULL comparisons return UNKNOWN, which is treated as false). Since all v1 values are NULL after the failed INSERT, the WHERE clause filters out all rows, resulting in an empty result set.

The DISTINCT keyword further complicates expectations. If any rows had satisfied v1 > 20, DISTINCT would eliminate duplicates based on all columns in v0. However, with no rows matching the WHERE condition, DISTINCT has no effect.

3. Implicit Column Population in INSERT Statements

The INSERT statement INSERT INTO v0(v2) VALUES (...) populates only the v2 column, leaving v1 as NULL for all inserted rows. Users expecting v1 to default to 0 or another implicit value misunderstand SQLite’s behavior: unmentioned columns in INSERT statements are set to NULL unless a DEFAULT clause exists in the table schema.

Troubleshooting Steps, Solutions & Fixes

Step 1: Diagnose INSERT Failures Using Error Handling

SQLite returns error codes and messages when constraints are violated. To identify failed INSERT operations:

1. Enable Error Reporting: In the SQLite command-line tool or client library, ensure error messages are visible. For example, the sqlite3 CLI prints Error: UNIQUE constraint failed: v0.v1 when the first INSERT is executed.
2. Use Atomic INSERTs: Split multi-row INSERT statements into individual INSERTs to isolate failures. For example:

   INSERT INTO v0(v1) VALUES (2);  -- Succeeds (v1=2 <=20, index ignored)  
   INSERT INTO v0(v1) VALUES (22); -- Succeeds (v1=22 >20, unique)  
   INSERT INTO v0(v1) VALUES (22); -- Fails (duplicate v1=22)  
   

   This approach allows non-violating rows to persist.

Step 2: Verify Table State Post-INSERT

Execute SELECT * FROM v0; to inspect the actual data. If the first INSERT failed, the table contains only the rows from the second INSERT:

┌──────┬──────┐  
│  v1  │  v2  │  
├──────┼──────┤  
│ NULL │  2   │  
│ NULL │  22  │  
│ NULL │  22  │  
└──────┴──────┘  

This explains the empty result for v1 > 20 queries.

Step 3: Adjust Queries to Account for NULL Values

To include rows where v1 might be NULL, modify the WHERE clause or use COALESCE:

SELECT DISTINCT * FROM v0 WHERE COALESCE(v1, 21) > 20;  

This converts NULL to 21, making the condition true. However, this workaround may not align with business logic.

Step 4: Revise Schema Design to Avoid Ambiguity

1. Explicit Defaults: Define DEFAULT values for v1 to avoid NULLs:

   CREATE TABLE v0 (v1 INT DEFAULT 0, v2);  
   

2. Non-Nullable Columns: Add NOT NULL constraints if NULL is not acceptable:

   CREATE TABLE v0 (v1 INT NOT NULL, v2);  
   

3. Conditional Inserts: Use INSERT triggers to validate data before insertion.

Step 5: Optimize Partial Index Conditions

If the partial index’s purpose is to enforce uniqueness only for v1 > 20, ensure other operations account for this:

-- Insert non-conflicting rows first  
INSERT INTO v0(v1) VALUES (2), (22);  
-- Handle duplicates separately  
INSERT OR IGNORE INTO v0(v1) VALUES (22);  

Step 6: Educate on SQLite’s Transactional INSERT Behavior

Understand that multi-row INSERTs are atomic. Use transactions to control rollback scope:

BEGIN;  
INSERT INTO v0(v1) VALUES (2);  
INSERT INTO v0(v1) VALUES (22);  
INSERT INTO v0(v1) VALUES (22);  
COMMIT;  

If any INSERT fails, the COMMIT will fail, but earlier successful INSERTs in separate statements will persist.

Final Solution: Correct the INSERT Workflow

To achieve the expected table state:

1. Insert non-conflicting rows in separate statements.
2. Use INSERT OR IGNORE to handle duplicates:

INSERT OR IGNORE INTO v0(v1) VALUES (2), (22), (22);  

This ignores duplicates for v1 > 20, inserting 2 and the first 22.

By addressing these areas, users can align their SQLite operations with expected results, ensuring proper handling of partial indexes, NULL values, and batch INSERT atomicity.