Assertion Failure in sqlite3ExprSkipCollateAndLikely During Indexed Expression Optimization

Understanding the Expression Parsing Failure in Collation-Sensitive Indexed Queries

Issue Overview
This guide addresses an assertion failure triggered during SQLite query execution involving collation-aware indexed expressions. The failure occurs in the sqlite3ExprSkipCollateAndLikely function when the parser encounters an expression node that does not match the expected TK_COLLATE operation type. The problem manifests specifically in scenarios where:

1. An index combines a column with a collation-modified expression (e.g., +c0 COLLATE NOCASE).
2. A subquery references the outer table’s aliased column with collation in a predicate.
3. The SQLITE_IndexedExpr optimization is active, which attempts to substitute expressions with indexed equivalents for performance gains.

The assertion failure (pExpr->op==TK_COLLATE) indicates the SQLite parser expected a collation operator node in the abstract syntax tree (AST) but encountered a different node type. This discrepancy arises from improper handling of collation modifiers during expression substitution in optimization phases. The bug was introduced in SQLite version 3.41.0 (commit b9190d3da70c4171) and resolved in commit cf6454ce26983b9c.

Root Causes of Collation-Aware Expression Misvalidation

1. Indexed Expression Substitution Logic Flaw
The SQLITE_IndexedExpr optimization replaces column references in WHERE clauses with equivalent indexed expressions to bypass table scans. When an indexed expression contains a collation modifier (e.g., +c0 COLLATE NOCASE), the substitution logic fails to preserve the collation context. The optimizer incorrectly strips the TK_COLLATE node from the AST during substitution, leaving an expression of type TK_PLUS (from +c0) where TK_COLLATE was expected.

2. Collation Propagation in Correlated Subqueries
The outer query’s alias a0 is referenced in a correlated subquery with a collation modifier (+a0.c0 COLLATE NOCASE). During query flattening or subquery materialization, the collation attribute is not propagated to the substituted expression. This creates a mismatch between the parsed expression structure and the runtime validation logic in sqlite3ExprSkipCollateAndLikely.

3. Assertion Sensitivity in Debug Builds
The assertion failure is exposed in debug builds with -DSQLITE_DEBUG due to rigorous consistency checks. Production builds without debugging flags might silently ignore the mismatch, leading to undefined behavior such as incorrect query results or memory corruption.

4. GROUP BY Clause with Large Integer Literal
While the GROUP BY 10000000000 clause is syntactically valid, it forces the query planner to process the grouping as an expression rather than a column index. This interacts unexpectedly with the indexed expression substitution, exacerbating the collation node omission.

Resolving Collation-Related Assertion Failures in Indexed Queries

Step 1: Validate Index Definitions for Collation Consistency
Review all indexes involving collation modifiers or expression-based columns. Ensure that expressions in indexes explicitly define collation where required. For the given example:

CREATE INDEX i ON v0 (c0, +c0 COLLATE NOCASE);

Verify that the +c0 COLLATE NOCASE expression is semantically valid and does not rely on implicit collation inheritance. Recreate indexes with unambiguous collation specifications if necessary.

Step 2: Disable SQLITE_IndexedExpr Optimization Temporarily
To confirm the optimization is the culprit, disable it at runtime:

.testctrl optimizations 0x01000000;

If the assertion disappears, proceed to adjust the query or upgrade SQLite. Note that this optimization improves query performance, so permanent disabling should be a last resort.

Step 3: Upgrade to SQLite Version Containing Commit cf6454ce26983b9c
The fix ensures collation modifiers are preserved during expression substitution. For users unable to upgrade immediately, backport the following changes:

• Modification to sqlite3Expr.cpp: Ensure sqlite3ExprSkipCollate recursively skips only collation nodes without altering the underlying expression type.
• Adjustment in wherecode.c: Prevent the query planner from stripping collation nodes during indexed expression substitution.

Step 4: Rewrite Queries to Avoid Ambiguous Collation Contexts
Refactor the problematic query to decouple collation from arithmetic operators:

SELECT 1 FROM v0 AS a0 
WHERE (SELECT count(CASE WHEN a0.c0 = (+a0.c0 COLLATE NOCASE) THEN 1 END) 
       FROM v0 
       GROUP BY c0 HAVING c0 = 10000000000) 
ORDER BY a0.c0;

This rewrite isolates the collation operation from the + operator, reducing parser ambiguity.

Step 5: Audit Compilation Flags for Debugging Overheads
Avoid combining -DSQLITE_DEBUG with -O0 in production builds, as this exposes internal assertions not present in release-mode binaries. Use -DSQLITE_DEBUG only during testing and pair it with -O1 or higher to mimic release optimization paths.

Step 6: Utilize EXPLAIN to Inspect Expression Substitution
Run EXPLAIN on the original query to visualize how the optimizer processes collation nodes:

EXPLAIN SELECT 1 FROM v0 AS a0 WHERE (SELECT count(...)) ...;

Look for OP_Collate or OP_Column opcodes in the bytecode. Missing OP_Collate indicates improper collation handling during substitution.

Step 7: Implement Regression Tests for Collation-Index Interactions
Add test cases that combine:

• Expression-based indexes with collation.
• Correlated subqueries referencing outer table aliases.
• Aggregate functions with complex grouping criteria.
  This ensures future updates do not reintroduce the assertion failure.

Step 8: Monitor Query Planner Decisions with TREETRACE and WHERETRACE
Leverage the compile-time flags -DSQLITE_ENABLE_TREETRACE and -DSQLITE_ENABLE_WHERETRACE to log the optimizer’s decision-making process. Inspect logs for collation-aware expression substitution steps.

Final Note: This issue underscores the importance of rigorous collation management in expression-heavy schemas. Developers working with regionalized data or case-insensitive text comparisons should prioritize testing index interactions with collation settings across SQLite versions.