Assertion Failure in agginfoPersistExprCb During Complex Aggregation Query

Query Structure Triggering Assertion Failure in Aggregation Logic

The core issue involves an assertion failure (Assertion 0′ failed) in SQLite's agginfoPersistExprCb` function when executing a specific query containing nested aggregates, window functions, and correlated subqueries. The failure occurs under these conditions:

1. A multi-table join (v0 AS a0 JOIN v0 AS a1) with grouping (GROUP BY a1.c0).
2. A nested aggregate subquery (SELECT count(a0.c0+a0.c2)) inside an outer count() function.
3. A window function (sum(0) OVER (PARTITION BY 0)) in the ORDER BY clause of the subquery.
4. Use of indexed expressions (CREATE INDEX i ON v0 (c0+c2)), which interact with optimization flags SQLITE_IndexedExpr and SQLITE_CoverIdxScan.

The assertion triggers only when specific optimizations are enabled. Disabling these optimizations (via .testctrl) results in a different error: misuse of aggregate: count(), indicating the query may violate SQL semantics when parsed without optimizations. The failure is reproducible in SQLite versions after November 28, 2022, pinpointed via bisect to commit e3474d79b27298e9.

Root Causes: Aggregation Context Conflicts and Optimization Flag Interactions

The assertion failure stems from conflicting assumptions in SQLite’s query planner during the resolution of aggregate expressions and window functions. Four key factors contribute:

1. Invalid Nesting of Aggregates and Window Functions
The subquery (SELECT count(a0.c0+a0.c2) ...) contains an aggregate (count()) and a window function (sum(0) OVER ...). Window functions are evaluated after aggregates, but placing a window function in the ORDER BY of a subquery that itself contains an aggregate creates ambiguity in execution order. SQLite’s parser allows this syntax under optimizations like SQLITE_IndexedExpr, but the query planner fails to resolve the interdependency between the outer GROUP BY and the inner window function.

2. Indexed Expression Optimization Conflicts
The indexed expression c0+c2 (via CREATE INDEX i) interacts with SQLITE_IndexedExpr, which allows the use of indexed expressions in place of computed columns. When enabled, the optimizer attempts to rewrite a0.c0+a0.c2 as a reference to the precomputed index. However, this rewrite conflicts with the aggregation context in the subquery, where the expression must be recalculated per-row during grouping. The assertion in agginfoPersistExprCb occurs when the function attempts to persist an expression tree that references an indexed column in an invalid context.

3. Window Function Partitioning Semantics
The PARTITION BY 0 clause creates a single partition for all rows. This interacts unexpectedly with the outer GROUP BY a1.c0, as the window function’s scope spans the entire result set, while the outer aggregation groups by a1.c0. The query planner miscomputes the boundaries of aggregation vs. windowing when both are present.

4. Optimizer Flag Dependencies
Disabling SQLITE_IndexedExpr or SQLITE_CoverIdxScan forces the query planner to abandon indexed expression rewrites and cover-index scans. This changes the error to a parse-time misuse of aggregate, revealing that the query’s structure is invalid under stricter parsing rules. The optimizations mask the semantic error but lead to a planner assertion when enabled.

Resolution: Query Rewriting, Optimization Tuning, and Debugging

Step 1: Validate Query Semantics
The misuse of aggregate error when optimizations are disabled indicates the query violates SQL rules. Confirm that:

• The inner count(a0.c0+a0.c2) is not nested within an aggregate context improperly.
• The ORDER BY sum(0) OVER ... in the subquery does not reference outer-scope columns, creating an implicit correlation.
  Rewrite the query to separate aggregation and windowing:

SELECT 
  count(
    (SELECT COUNT(a0.c0 + a0.c2) 
     FROM v0 
     WHERE [explicit correlation condition]
     ORDER BY (SELECT sum(0) OVER (PARTITION BY 0)))
  ) 
FROM v0 AS a0 
JOIN v0 AS a1 ON [explicit join condition] 
GROUP BY a1.c0;

Step 2: Adjust Optimization Flags Temporarily
If immediate execution is required, disable conflicting optimizations:

.testctrl optimizations 0x01000020;  -- Disable SQLITE_IndexedExpr and SQLITE_CoverIdxScan

This forces the query planner to avoid indexed expression rewrites and cover-index scans, though the query may still fail with a parse error.

Step 3: Debug Expression Persistence Logic
Compile SQLite with -DSQLITE_DEBUG and enable tree tracing:

.treertree ON
SELECT ... [trigger query];

Inspect the logged expression trees for the subquery and window function. Look for unresolved AggInfo structures or mislinked Expr nodes referencing indexed expressions.

Step 4: Patch agginfoPersistExprCb
In the failing function (agginfoPersistExprCb), the assertion assert(0) indicates an unhandled case. Modify the code to handle expressions with EP_Indexed flags (from indexed expression optimization) during aggregation persistence. Example:

// In agginfoPersistExprCb:
if( ExprHasProperty(pExpr, EP_Indexed) ){
  // Resolve indexed expression to its base column references
  return WRC_Continue;
}

Step 5: Update SQLite Version
The bisect identifies the regression starting at commit e3474d79b27298e9. Check for fixes in later versions or apply patches from SQLite’s public repository addressing agginfoPersistExprCb assertions.

Step 6: Rewrite Indexed Expression
Avoid indexing expressions used in aggregation subqueries. Replace CREATE INDEX i ON v0 (c0+c2) with a generated column (if using SQLite 3.31+):

CREATE TABLE v0 (c0, c2, c0_plus_c2 GENERATED ALWAYS AS (c0+c2));
CREATE INDEX i ON v0 (c0_plus_c2);

This separates the indexed value from the raw expression, reducing planner conflicts.

Step 7: Isolate Window Function Execution
Move the window function outside the subquery’s ORDER BY clause. Instead, compute it in a CTE:

WITH window_cte AS (
  SELECT sum(0) OVER (PARTITION BY 0) AS w FROM v0
)
SELECT count((SELECT count(a0.c0 + a0.c2) FROM v0 ORDER BY w)) 
FROM v0 AS a0 JOIN v0 AS a1 ...;

Step 8: Report Minimal Test Case
If the issue persists, submit a report to SQLite’s team with this minimized test case:

CREATE TABLE t(x);
SELECT count((SELECT count(x) ORDER BY sum(0) OVER ())) FROM t JOIN t GROUP BY x;

This removes indexed expressions and simplifies the schema while retaining the assertion trigger.

Final Workaround:
Until a fix is released, avoid combining nested aggregates, window functions in ORDER BY, and indexed expressions in the same query. Use explicit JOIN conditions and isolate window functions to outermost queries.