Assertion Failure: Invalid Btree Cursor During Min/Max Optimization with FTS5 and Window Function

Issue Overview: Assertion Failure in sqlite3VdbeExec During Query Execution

The core issue involves an assertion failure triggered during the execution of a query that combines a MAX() aggregation, a view containing a correlated subquery with a window function (LAG), and a virtual FTS5 table. The assertion sqlite3BtreeCursorIsValid(pCur) fails in the SQLite virtual machine (specifically in sqlite3VdbeExec), indicating that a B-tree cursor used to traverse database structures is invalid or uninitialized. This occurs when the SQLITE_MinMaxOpt optimization is enabled, which attempts to optimize MIN()/MAX() queries by leveraging indexes. The failure disappears when this optimization is disabled via .testctrl optimizations 0x00010000.

The problematic query structure includes:

1. A virtual FTS5 table (v5) that is emptied (DELETE FROM main.v5).
2. A view (v7) with a subquery containing LAG(0) OVER (PARTITION BY 0) referencing the empty FTS5 table.
3. A SELECT max(c1) FROM v7 that triggers the assertion failure.

The interaction between the empty FTS5 table, the window function in the view’s subquery, and the Min/Max optimization creates a scenario where SQLite’s query planner incorrectly assumes the validity of a cursor during execution. The assertion failure occurs because the cursor is either closed prematurely, not properly initialized, or reused in an invalid state.

Possible Causes: Invalid Cursor State Under Min/Max Optimization

1. Incorrect Cursor Handling in Min/Max Optimization with Correlated Subqueries

The SQLITE_MinMaxOpt optimization rewrites MAX()/MIN() queries to use index seeks instead of full scans. However, when the query involves a correlated subquery (as in the view v7), the optimization may fail to account for dependencies between the outer query and the subquery. The LAG window function in the subquery introduces additional statefulness, complicating cursor management. If the FTS5 table is empty, the subquery might return no rows, causing the cursor to be invalidated prematurely.

2. FTS5 Virtual Table Interactions with Window Functions

Virtual tables like FTS5 have different cursor semantics compared to ordinary tables. The DELETE FROM main.v5 operation leaves the FTS5 table in a state where cursors opened on it might not behave as expected. When combined with the LAG window function (which requires maintaining a partition context), SQLite may mishandle cursor initialization or iteration, especially when the optimization assumes a valid cursor exists.

3. Race Condition During Query Planning and Execution

The query planner might generate a plan that assumes the FTS5 table’s cursor is valid for the entire duration of the MAX() aggregation. However, the subquery’s window function could invalidate or close the cursor during execution, leading to a mismatch between the planner’s assumptions and the runtime state. This is exacerbated by the empty FTS5 table, which skips steps that would otherwise initialize the cursor correctly.

Troubleshooting Steps, Solutions & Fixes: Resolving Cursor Validity and Optimization Conflicts

1. Diagnose Query Plan Differences with SQLITE_MinMaxOpt Enabled/Disabled

Use EXPLAIN and EXPLAIN QUERY PLAN to compare execution plans with and without the optimization:

.testctrl optimizations 0x00010000  -- Disable MinMaxOpt
EXPLAIN QUERY PLAN SELECT max(c1) FROM v7;

.testctrl optimizations 0x00000000  -- Enable all optimizations
EXPLAIN QUERY PLAN SELECT max(c1) FROM v7;

Look for differences in how the MAX() aggregation is resolved. With MinMaxOpt enabled, the plan should attempt to use an index on v0.c1 directly. If the subquery or FTS5 table is ignored in the optimized plan, this indicates a planner oversight.

2. Isolate the Impact of the FTS5 Table and Window Function

Test variations of the schema and query to identify the minimal components triggering the failure:

• Remove the FTS5 table from the subquery.
• Replace LAG(0) with a non-window function.
• Populate the FTS5 table with dummy data.

If the assertion only occurs when all three components (FTS5, window function, empty table) are present, the issue is likely a boundary condition in cursor initialization for empty virtual tables.

3. Patch or Workaround for Cursor Initialization

Temporary Workaround: Disable the MinMaxOpt optimization globally or per-query:

PRAGMA optimization_control=minmax_opt,0;  -- Disable for all queries
SELECT max(c1) FROM v7;

-- Or use a hint for a specific query:
SELECT max(c1) FROM v7 /*+ no_minmax_opt() */;

Code Fix: Modify the MinMaxOpt optimization to check cursor validity when correlated subqueries or virtual tables are involved. In sqlite3WhereBegin, skip the optimization if the query contains:

• Subqueries with window functions.
• Virtual tables that have been modified in the same transaction.

4. Adjust FTS5 Cursor Semantics for Empty Tables

The FTS5 module should ensure cursors are marked as invalid explicitly when the table is empty. In fts5CursorFirst, add a check for pTab->pIndex->nEmpty==0 and set pCsr->ePlan = FTS5_PLAN_NONE to prevent invalid cursor states from propagating.

5. Enhance Assertion Checks in Debug Builds

In debug builds, augment sqlite3BtreeCursorIsValid to log cursor states and query contexts when assertions fail. This helps identify whether the invalid cursor originates from the FTS5 table, the view’s subquery, or the main v0 table.

By systematically isolating the interaction between the Min/Max optimization, virtual tables, and window functions, developers can identify the root cause of the invalid cursor assertion. The fixes involve both query planner adjustments and cursor lifecycle management enhancements to handle edge cases with empty virtual tables and correlated subqueries.