Assertion Failure in sqlite3TableColumnAffinity Due to Invalid Column Index

Understanding the Assertion Failure in Column Affinity Resolution

The assertion failure assert( iCol < pTab->nCol ) in the sqlite3TableColumnAffinity function occurs when SQLite attempts to access a column index (iCol) that exceeds the number of columns (nCol) in the target table (pTab). This function retrieves the type affinity (e.g., INTEGER, TEXT) of a specific column in a table during query compilation. The assertion enforces that the column index must always be less than the total columns in the table, as column indices are zero-based. When violated, it indicates a logical error in how column indices are resolved during query parsing, optimization, or code generation. The error manifests in debug builds with assertions enabled (--enable-debug), while release builds may fail with a generic "column mismatch" error during query preparation.

The test case triggering this issue involves a LEFT JOIN between a base table and a derived subquery, combined with a complex WHERE clause containing a tuple comparison against a subquery with a UNION and window function. The interplay of these elements exposes a flaw in how SQLite resolves column references across nested subqueries, leading to an invalid column index being passed to sqlite3TableColumnAffinity. This flaw was introduced in a specific code change (check-in 0d11d777c8) and later resolved in a subsequent update (check-in 0f9fc6b6073365d5).

Root Causes of Invalid Column Index Resolution

1. Incorrect Column Index Mapping in Derived Tables or Aliases

When a subquery is aliased (e.g., (SELECT a AS b FROM t0) AS t1), SQLite must map external references to the derived table’s columns correctly. If the query planner misassigns column indices for aliased columns in derived tables, subsequent operations (e.g., resolving b=4 in the WHERE clause) may reference out-of-bounds indices. This is exacerbated when joins or nested subqueries introduce multiple layers of column indirection.

2. Window Functions and Aggregates in Subquery Projections

The subquery (SELECT 2,2 UNION SELECT total(b), max(b) OVER(...)) combines a UNION of constant values and a window function. Window functions and aggregates (total(b)) alter the way columns are projected in intermediate results. If the query planner fails to account for these differences when resolving column counts or indices, it may generate an invalid column index during affinity resolution.

3. Tuple Comparisons with Mismatched Column Counts

The WHERE clause compares (a,1) against a subquery that projects two columns. While the tuple sizes match, the subquery’s ORDER BY 2 DESC introduces a dependency on the second column’s value. If the planner incorrectly associates the tuple comparison’s columns with the underlying tables, it may corrupt the column index mapping for subsequent conditions (e.g., b=4).

Resolving the Assertion Failure and Column Mismatch Errors

Step 1: Validate Column Index Resolution in Derived Tables

• Problem: The derived table t1 (aliased from SELECT a AS b FROM t0) has one column (b). References to b in the outer query must map to t1’s column index 0.
• Fix: Ensure the query planner correctly maps aliased columns in derived tables to their internal indices. In the faulty code, b might have been assigned an index exceeding t1’s nCol due to improper handling of join contexts. Review the code responsible for resolving column names in FROM clause subqueries.

Step 2: Correct Handling of Window Functions and Aggregates

• Problem: The SELECT total(b), max(b) OVER(...) subquery combines an aggregate and a window function. These constructs require distinct handling during column projection.
• Fix: Modify the code that processes UNION subqueries to reconcile column counts and types when mixing aggregates, window functions, and literals. Ensure the planner accounts for window function outputs when assigning column indices.

Step 3: Enforce Tuple Comparison Column Consistency

• Problem: The (a,1)=(...) comparison expects two columns, but the subquery’s ORDER BY 2 DESC might interfere with column resolution.
• Fix: Verify that tuple comparisons resolve columns unambiguously, especially when subqueries include ordering or window functions. Adjust the code to prevent column index leakage across subquery boundaries.

Step 4: Update to the Patched SQLite Version

• The fix in check-in 0f9fc6b6073365d5 addresses these issues by refining column index resolution logic. Developers should update to a version containing this commit.

By addressing these root causes and applying the fixes, the assertion failure and column mismatch errors are resolved, ensuring robust handling of complex queries involving joins, subqueries, and window functions.