Filling Nulls with Previous Non-Null Values in SQLite

Handling Null Propagation in Ordered Data Sets: Mechanisms and Constraints

Understanding Null Propagation Requirements in Sequential Data

The core challenge revolves around replacing NULL values in a column with the most recent non-null value from preceding rows. This is a common requirement in time-series data, sensor readings, or ring buffers where gaps (nulls) must be filled with the last valid observation. SQLite’s lack of native IGNORE NULLS support in window functions complicates this task, necessitating alternative strategies.

Key considerations include:

1. Data Ordering: SQL tables are inherently unordered. Any solution must explicitly define row sequence (e.g., via rowid, an explicit timestamp, or user-defined sort key).
2. Performance: Methods must scale efficiently, especially for large datasets or high-frequency inserts.
3. Data Integrity: Solutions must handle edge cases like leading NULLs, consecutive NULL chains, and concurrent modifications.

Limitations of Native SQLite Functions and Row Order Assumptions

Cause 1: Absence of IGNORE NULLS in Window Functions

SQLite’s window functions (last_value(), first_value()) do not support the IGNORE NULLS modifier. This forces developers to emulate null propagation through subqueries, joins, or recursive CTEs.

Cause 2: Reliance on Implicit Row Ordering

Many solutions depend on rowid to infer insertion order. While valid in single-threaded, append-only scenarios, this becomes unreliable if:

• Rows are deleted, causing rowid gaps.
• VACUUM is executed, which may reuse rowid values.
• Concurrent writes occur, as SQLite serializes writes but doesn’t guarantee insertion order across threads.

Cause 3: Trigger-Based Overheads and Race Conditions

Using triggers to auto-fill NULLs during insertion introduces:

• Performance Penalties: Each insert triggers a subquery to fetch the prior non-null value.
• Concurrency Issues: If multiple threads insert rows, the "last" value might not correlate with the inserting thread’s expectations.

Strategies for Efficient Null Handling and Data Integrity

Solution 1: Correlated Subquery for On-Demand Null Filling

For read-time null replacement, use a correlated subquery to fetch the last non-null value preceding the current row:

SELECT 
  rowid, 
  a AS original_value,
  (
    SELECT t2.a 
    FROM t t2 
    WHERE t2.rowid <= t1.rowid 
      AND t2.a IS NOT NULL 
    ORDER BY t2.rowid DESC 
    LIMIT 1
  ) AS filled_value
FROM t t1
ORDER BY rowid;

Mechanics:

• For each row, the subquery scans all rows with rowid <= current_row, filters out NULLs, and picks the highest rowid value.
• Efficiency: O(n²) complexity. Suitable for small datasets or infrequent queries.

Optimization:

• Add an index on (a, rowid) to speed up the WHERE t2.a IS NOT NULL filter.

Solution 2: Recursive CTE for Batch Processing

For datasets requiring a one-time null fill (e.g., data migration), a recursive CTE propagates non-null values iteratively:

WITH RECURSIVE 
  sorted_data AS (
    SELECT 
      rowid, 
      a,
      ROW_NUMBER() OVER (ORDER BY rowid) AS seq
    FROM t
  ),
  cte AS (
    SELECT 
      rowid,
      a,
      seq,
      COALESCE(a, 0) AS filled_value  -- Default for leading NULLs
    FROM sorted_data
    WHERE seq = 1
    UNION ALL
    SELECT 
      s.rowid,
      s.a,
      s.seq,
      COALESCE(s.a, c.filled_value)
    FROM sorted_data s
    JOIN cte c ON s.seq = c.seq + 1
  )
SELECT rowid, a, filled_value
FROM cte
ORDER BY rowid;

Mechanics:

• sorted_data assigns a sequential number to each row.
• The CTE iterates row-by-row, carrying forward the last non-null value.
• Efficiency: O(n) complexity. Suitable for medium-sized datasets (up to ~1M rows).

Edge Cases:

• Leading NULLs are replaced with a default (here, 0). Adjust as needed.

Solution 3: Trigger-Based Insertion-Time Null Handling

For applications inserting rows sequentially (e.g., ring buffers), use an AFTER INSERT trigger to auto-fill NULLs:

CREATE TRIGGER fill_null_on_insert
AFTER INSERT ON t
WHEN NEW.a IS NULL
BEGIN
  UPDATE t
  SET a = (
    SELECT a 
    FROM t 
    WHERE rowid < NEW.rowid 
      AND a IS NOT NULL 
    ORDER BY rowid DESC 
    LIMIT 1
  )
  WHERE rowid = NEW.rowid;
  
  -- Optional: Enforce non-null constraint
  SELECT RAISE(ABORT, 'Cannot resolve NULL from prior rows')
  WHERE (SELECT a FROM t WHERE rowid = NEW.rowid) IS NULL;
END;

Mechanics:

• After inserting a row with NULL, the trigger updates it using the last non-null value from prior rows.
• The RAISE(ABORT) ensures no NULL remains if no prior value exists.

Caveats:

• Rowid Stability: Requires that rowid order strictly matches insertion order. Avoid VACUUM and deletions.
• Concurrency: Safe only in single-threaded environments. Concurrent inserts may lead to race conditions.

Solution 4: Hybrid Approach for Ring Buffers

For ring buffers (fixed-size tables overwriting oldest entries), combine triggers with careful rowid management:

1. Disable Auto-Vacuum:

   PRAGMA auto_vacuum = 0;  -- Prevent rowid reuse
   

2. Prevent Manual Vacuum: Ensure application doesn’t invoke VACUUM.
3. Trigger with Ring-Aware Logic:

   CREATE TRIGGER ring_buffer_insert
   AFTER INSERT ON t
   WHEN NEW.a IS NULL
   BEGIN
     UPDATE t
     SET a = (
       SELECT a 
       FROM t 
       WHERE rowid = (SELECT MAX(rowid) FROM t WHERE rowid < NEW.rowid)
     )
     WHERE rowid = NEW.rowid;
   END;
   

Mechanics:

• Assumes rowids are sequential and gaps are minimal (e.g., overwriting oldest row via DELETE + INSERT).

Optimization:

• Maintain a separate table or variable caching the last inserted value to avoid subqueries.

Comparative Analysis of Approaches

Method	Use Case	Performance	Data Freshness	Complexity
Correlated Subquery	Ad-hoc queries on small datasets	Low	Real-time	Low
Recursive CTE	Batch processing	Medium	Static	Medium
Trigger-Based	High-frequency inserts	High	Real-time	High
Hybrid Ring Buffer	Fixed-size buffers	High	Real-time	High

Best Practices and Pitfalls

1. Indexing: For subquery/CTE methods, index on both the sort key (rowid) and the nullable column:

   CREATE INDEX idx_t_rowid_a ON t(rowid, a);
   

2. Concurrency Control: Use write locks (BEGIN EXCLUSIVE) in multi-threaded environments to prevent race conditions.
3. Default Values: Handle leading NULLs by setting a default in COALESCE (e.g., COALESCE(a, 0)).
4. Monitoring: Profile query plans using EXPLAIN QUERY PLAN to detect full table scans.

Final Recommendations

• For Static Datasets: Use recursive CTEs for simplicity and predictable performance.
• For Real-Time Inserts (Single-Threaded): Implement triggers with rowid-based lookups.
• For High-Concurrency Systems: Avoid triggers; instead, handle null propagation at the application layer using cached values.