Unexpected NULL Handling in Aggregate Queries with Views

Understanding Non-Deterministic Results in Views with Aggregate Functions and NULL Values

Root Cause Analysis: Why Aggregate Queries with NULLs Produce Inconsistent Results

The core issue revolves around SQLite’s handling of non-aggregated columns in aggregate queries and NULL semantics, particularly when combined with views. Let’s dissect the problem step-by-step.

1. Table Structure and Data
   The table v0 has two columns: c1 INT and c2 INT. Two rows are inserted:

   • (NULL, 1)
   • (NULL, NULL)

   The view v5 is defined as:

   CREATE VIEW v5 AS SELECT MIN(c1), c2 FROM v0 WHERE c1 IS NULL;
   

   The MIN(c1) aggregate operates on rows where c1 IS NULL, which includes both rows. Since all c1 values in the group are NULL, MIN(c1) returns NULL. However, the c2 column is not aggregated and is instead a "bare column" (a non-aggregated column not in a GROUP BY clause).

2. Bare Columns in Aggregate Queries
   SQLite allows bare columns in aggregate queries (a deviation from standard SQL). When no GROUP BY is specified, the query treats all rows as a single group. For bare columns, SQLite selects a value from an arbitrary row in the group. This is documented in SQLite’s Bare Columns in Aggregate Queries.

   In this case, the c2 value in v5 could come from either (NULL, 1) or (NULL, NULL). The choice is non-deterministic and depends on query planner internals (e.g., index usage, row order).

3. Impact of Indexes and Query Plans
   The presence of an index (i4 ON v0(c1)) can influence which row SQLite selects for the bare column. For example:

   • If the index is scanned, SQLite might pick the first row encountered.
   • Without the index, it might scan the table in natural order.

   However, no guarantees are made about which row is chosen. This explains why SELECT * FROM v5 sometimes returns NULL|1 and other times NULL|NULL (though the user observed NULL|1 in their test).

4. Interaction with Filter Conditions
   The query SELECT * FROM v5 WHERE v5.c2 introduces a filter on c2. The condition v5.c2 is shorthand for v5.c2 != 0. However:

   • If c2 is 1 (from the first row), the condition evaluates to 1 != 0 → true, and the row is returned.
   • If c2 is NULL (from the second row), the condition evaluates to NULL != 0 → NULL, which is treated as false.

   Since the view v5 is non-materialized, each access to v5 re-executes the underlying query. When the WHERE clause is added, SQLite may re-evaluate the view and select a different row for c2. This explains the empty result: the second execution of v5 picked the NULL value for c2, which was filtered out.

5. NULL Semantics in SQL
   SQL treats NULL as "unknown." Comparisons involving NULL (e.g., NULL = NULL, NULL != 1) return NULL, not true or false. This leads to counterintuitive behavior:

   SELECT COUNT(*) FROM v5 WHERE v5.c2;  -- Returns 0
   

   Here, v5.c2 is NULL (from the second row), so the WHERE clause excludes the row. The COUNT returns 0, even though v5 appears to contain a row.

Why the Observed Behavior Is Not a Bug

1. Bare Columns Are Intentionally Non-Deterministic
   SQLite explicitly documents that bare columns in aggregate queries return values from arbitrary rows when no GROUP BY is used. This is a deliberate design choice to optimize performance, not a bug.

2. Views Are Not Materialized
   SQLite views are virtual tables that execute their underlying query each time they are accessed. There is no guarantee of consistency between separate accesses to the same view.

3. NULL Handling Follows SQL Standards
   SQLite’s treatment of NULL aligns with ANSI SQL. For example, CAST(NULL AS BOOL) returns NULL, not false. The TOTAL() function ignores NULL values, which explains why TOTAL(CAST(v5.c2 AS BOOL) != 0) returns 1.0 (only the 1 is counted).

Resolving the Issue: Best Practices for Deterministic Queries

1. Avoid Bare Columns in Aggregate Queries

Restructure queries to eliminate non-determinism:

• Use GROUP BY on a column that uniquely identifies rows.
• Use subqueries or window functions to explicitly select the desired c2 value.

Example Fix:

CREATE VIEW v5 AS 
SELECT MIN(c1), c2 
FROM v0 
WHERE c1 IS NULL 
GROUP BY c2;  -- Now deterministic if c2 is unique

2. Use COALESCE or CASE to Handle NULLs

Explicitly convert NULL to a known value:

SELECT * FROM v5 WHERE COALESCE(c2, 0) != 0;

3. Materialize the View

Create a temporary table to "freeze" the view’s content:

CREATE TEMP TABLE v5_materialized AS SELECT * FROM v5;
SELECT * FROM v5_materialized WHERE c2;

4. Use Deterministic Ordering

Add ORDER BY and LIMIT to enforce row selection:

CREATE VIEW v5 AS 
SELECT MIN(c1), c2 
FROM v0 
WHERE c1 IS NULL 
ORDER BY c2 DESC  -- Prefer non-NULL c2
LIMIT 1;

5. Avoid NULLs for Business Logic

Replace NULL with sentinel values (e.g., -1, 0, or 'N/A') if comparisons are needed:

INSERT INTO v0 VALUES (-1, 1);  -- Use -1 instead of NULL

By understanding SQLite’s handling of bare columns, NULL semantics, and view execution, developers can write deterministic queries and avoid unexpected results.