Core Issue: WHERE Clause Optimization Triggers Invalid Index Field Reference During Subquery Execution

Structural Analysis of Query Execution Path Leading to Assertion Failure

The fatal assertion p2 < (u32)pC->nField occurs during bytecode execution when attempting to access an index column that does not exist in the current cursor’s field set. This manifests in debug builds when executing queries containing:

1. Compound WHERE clauses with OR-connected terms
2. Correlated subqueries using EXISTS operators
3. Multi-column indexes containing duplicate or redundant column references

Key Execution Context:

• Assertion Location: sqlite3VdbeExec() at cursor field validation
• Faulting Opcode: OP_Column attempting to read beyond actual index columns
• Error Surface Conditions:
  • Index with trailing duplicate columns (e.g., CREATE INDEX i4 ON v0(c3,c1,c2,c2))
  • WHERE clause containing OR-connected equality checks
  • EXISTS subquery correlating via duplicated index column

Reproduction Matrix:

CREATE TABLE t1(x INT, y INT PRIMARY KEY, z);
CREATE INDEX t1zxy ON t1(z,x,y); -- Contains y column redundantly
SELECT y FROM t1 
WHERE (z=222 OR y=111) 
  AND EXISTS(SELECT 1 FROM t0 WHERE t1.y); -- Correlates via indexed y

Debug builds validate cursor field access strictly during bytecode execution. The assertion fires when the generated OP_Column refers to column index 3 (fourth column) in index t1zxy, which only contains columns (z,x,y) – making column 3 invalid as indexes are 0-based.

Root Causes: WHERE Clause Optimization Phases Incorrectly Propagate Virtual Terms

1. Redundant Column Inclusion in Composite Indexes

Index i4 in original test case and t1zxy in simplified case include the primary key column y/c2 twice. While SQLite allows this syntactically, the query optimizer’s handling of such indexes during WHERE clause processing creates hidden vulnerabilities:

• Column Count Miscalculation: Duplicate columns in index definitions cause the index column count (nColumn) to exceed the actual usable columns during bytecode generation
• Virtual Term Expansion: WHERE clause terms get mapped to index columns beyond their physical storage capacity

2. OR Optimization Flaw in WHERE Clause Processing

The WHERE (z=222 OR y=111) clause undergoes these key optimization phases:

1. Term Analysis: Break OR into separate AND-connected terms
2. Index Selection: Attempt to use index t1zxy for covering both z=222 and y=111
3. Virtual Term Creation: Generate synthesized terms for partial index usage

Failure Sequence:

• Optimizer identifies index t1zxy can cover z=222 term via column 0
• OR clause requires handling via OR-by-UNION optimization
• During virtual term generation, the code incorrectly associates y=111 term with index column 2 (y) AND column 3 (non-existent duplicate y)
• Resulting bytecode references column 3 when accessing index cursor

3. Correlated Subquery Interaction with Index Scans

The EXISTS(SELECT 1 FROM t0 WHERE t1.y) subquery introduces:

• Correlation Binding: Outer query’s y column must be available in current cursor
• Late Optimization Binding: Subquery correlation forces index scan rather than full table scan
• Cursor Reuse: Same index cursor used for both outer WHERE clause and subquery correlation

Critical Code Path:

// sqlite3WhereBegin() in wherecode.c
if( pTerm->eOperator & WO_SINGLE ){ // Original check used WO_ALL
  // Generate virtual term for index access
  pExpr = sqlite3ExprDup(db, pExpr, 0);
  pAndExpr = sqlite3ExprAnd(pParse, pAndExpr, pExpr);
}

Using WO_ALL instead of WO_SINGLE caused inclusion of terms not strictly matching index column constraints, leading to over-aggressive virtual term generation referencing non-existent index columns.

Resolution Strategy: WHERE Clause Term Filtering and Index Column Validation

Step 1: Modify WHERE Clause Term Selection Logic

Patch Implementation:

-    if( (pWC->a[iTerm].eOperator & WO_ALL)==0 ) continue;
+    if( (pWC->a[iTerm].eOperator & WO_SINGLE)==0 ) continue;

Technical Rationale:

• WO_SINGLE restricts to terms with exactly one constraint (e.g. z=222)
• WO_ALL allowed compound constraints (e.g. y=111 with multiple representations)
• Prevents creation of virtual terms for constraints that span multiple index columns

Step 2: Add Assertion Guards for Index Column Boundaries

Code Reinforcement:

assert( p2 < pC->nField ); // Existing assertion
// Add new validation during index term analysis:
assert( iColumn < pIndex->nColumn );

Runtime Protection:

• Validate index column references during query planning phase
• Trap invalid column mappings before bytecode generation

Step 3: Index Definition Sanitization

Schema Validation Enhancement:

CREATE INDEX t1zxy ON t1(z,x,y); -- Now generates warning:
-- WARNING: redundant column 'y' in index definition

Implementation:

• Track column hash during index creation
• Flag duplicate columns in schema parser
• Optionally reject indexes with duplicate columns in strict mode

Step 4: Bytecode Generation Safeguards

VDBE Code Generation Check:

// When generating OP_Column for index access:
if( pIdx->aiColumn[i] >= pTab->nCol ){
  sqlite3ErrorMsg(pParse, "Index column %d out of bounds", pIdx->aiColumn[i]);
  return;
}

Preventive Measure:

• Catch invalid column references during code generation
• Provides clearer error messages in release builds

Step 5: Query Planner OR Optimization Revision

OR-by-UNION Reimplementation:

1. Split OR clauses into separate sub-queries
2. Validate index column usage for each sub-query branch
3. Disallow index usage for branches with column overflows

Example Flow:

SELECT y FROM t1 WHERE z=222
UNION
SELECT y FROM t1 WHERE y=111
  AND EXISTS(...) -- Reevaluate subquery with separate index validation

Comprehensive Validation Protocol

1. Index Column Duplication Detection

Test Case:

CREATE TABLE t2(a,b,c);
CREATE INDEX t2idx ON t2(a,b,c,b); -- Duplicate 'b'
EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=5;

Expected Outcome:

• Warning about duplicate column in index
• Query plan shows proper column usage (columns 0 and 1, not 3)

2. OR Clause with Index Boundary Check

Validation Query:

CREATE TABLE t3(x,y,z, PRIMARY KEY(y,z));
INSERT INTO t3 VALUES(1,2,3);
CREATE INDEX t3xyz ON t3(x,y,z,y); -- Redundant y
SELECT * FROM t3 
WHERE (x=1 OR y=2) 
  AND EXISTS(SELECT 1 FROM t3 WHERE t3.y);

Verification Steps:

1. Run in debug build with patched SQLite
2. Confirm no assertion failures
3. EXPLAIN output shows proper index column usage

3. Subquery Correlation Stress Test

Complex Case:

CREATE TABLE t4(a,b,c,d);
CREATE INDEX t4idx ON t4(a,b,c,d,d,d); -- Multiple duplicates
CREATE VIEW v4 AS SELECT * FROM t4 WHERE a=1 OR b=2;

SELECT d FROM v4 
WHERE EXISTS(
  SELECT 1 FROM t4 
  WHERE v4.d=t4.d 
    AND (t4.c=5 OR t4.d=10)
);

Analysis Points:

• Validate index t4idx usage in both outer and inner queries
• Check for proper column truncation in index scans
• Confirm correlation binding uses valid column indices

Long-Term Prevention Measures

1. Enhanced Index Column Analysis

Code Changes:

// In build.c index creation:
for(i=0; i<pIndex->nColumn; i++){
  if( pIndex->aiColumn[i]==pIndex->aiColumn[j] && i>j ){
    sqlite3ErrorMsg(pParse, "Duplicate column in index");
  }
}

2. WHERE Clause Optimization Auditing

New Debug Flags:

./configure --enable-debug --enable-query-plan-verification

Runtime Checks:

• Validate virtual term column mappings against actual index columns
• Log OR optimization decisions to separate debug stream

3. Automated Fuzz Testing Enhancement

SQL Fuzz Profile:

• Generate indexes with random duplicate columns
• Create OR-connected WHERE clauses with EXISTS subqueries
• Validate against both debug and release builds

Sample Fuzz Template:

for i in range(1000):
    cols = [random.choice(['a','b','c']) for _ in range(4)]
    print(f"CREATE INDEX tmp ON t({','.join(cols)});")
    print(f"SELECT {random.choice(cols)} FROM t WHERE ({random.choice(cols)}=1 OR {random.choice(cols)}=2) AND EXISTS (SELECT 1 FROM t);")

Developer Action Plan

1. Immediate Fix Application:

   • Apply trunk check-in 61a1c6dbd089979c
   • Rebuild SQLite with -DSQLITE_DEBUG and -DSQLITE_ENABLE_EXPLAIN_COMMENTS

2. Schema Review Checklist:

   • Identify indexes with duplicate columns
   • Rewrite OR-heavy queries using UNION where appropriate
   • Verify EXISTS subqueries don’t correlate via duplicated columns

3. Monitoring Configuration:

   PRAGMA integrity_check; -- Verify index structure
   EXPLAIN SELECT ...; -- Analyze query plans
   .eqp on -- Enable automatic explain in CLI
   

4. Regression Test Suite:

   • Add test cases with various column duplication patterns
   • Include nested view/subquery combinations
   • Cover both indexed and non-indexed correlation paths

Final Verification Procedure

Step-by-Step Validation:

1. Compile patched SQLite with debug enabled
2. Run original failing query:

   ./sqlite3 :memory: < failing_query.sql
   

3. Confirm clean exit with expected result ‘x’
4. Inspect bytecode using EXPLAIN:

   EXPLAIN SELECT * FROM v5 ...;
   

5. Verify OP_Column operands reference valid column indices
6. Check error logs for index duplication warnings
7. Run ASAN build to detect memory boundary violations
8. Execute comprehensive test suite with new index validation rules

Expected Post-Fix Behavior:

• All assertions remain valid without false positives
• Queries with legitimate column references execute normally
• Invalid index definitions generate warnings during schema creation
• EXPLAIN output shows correct column indices in IndexRangeScan ops

This comprehensive approach addresses both the immediate assertion failure and establishes safeguards against similar query optimization errors. The combination of code fixes, schema validation, and enhanced testing creates defense-in-depth protection against index column boundary violations.