Unexpected WHERE Clause Results with IS TRUE Due to LIKELY() Type Affinity Bug

Root Cause Analysis: LIKELY() Misinterprets Type Affinity in WHERE Clause Optimization

The core issue arises from the interaction between SQLite’s query optimizer, the LIKELY() function, and the IS TRUE operator in WHERE clauses. When LIKELY() is applied to an expression involving a column with a dynamically determined type affinity (e.g., via CAST or a view), older versions of SQLite (pre-3.45.0) incorrectly propagate type affinity during query optimization. This leads to discrepancies in how the WHERE clause evaluates conditions when IS TRUE is explicitly added. The bug was rooted in how the optimizer handled type conversions and affinity assignments for expressions wrapped in LIKELY() or UNLIKELY() hints.

Mechanics of the Bug

1. Type Affinity Propagation:

   • The v0.c2 column in the view is defined as CAST(((t1.c0) IS TRUE) AS TEXT). This forces the column’s value to have TEXT affinity.
   • The i46 index on t1 is created using CAST(((c0) IS TRUE) AS TEXT), which implicitly associates TEXT affinity with the indexed values.
   • When LIKELY(DISTINCT v0.c2) is used in the WHERE clause, the optimizer attempts to infer the type affinity of the expression. In buggy versions, LIKELY() caused the expression to inherit an incorrect affinity (e.g., NUMERIC instead of TEXT).

2. Impact of IS TRUE:

   • Adding IS TRUE to the WHERE clause modifies the parse tree structure. The expression ((t1.c0 IN ()) < LIKELY(v0.c2)) IS TRUE is treated as a boolean comparison, whereas the original WHERE clause evaluates the raw result of the < operator.
   • In buggy versions, the presence of IS TRUE suppressed the flawed affinity propagation caused by LIKELY(), leading to different query results.

3. Query Plan Divergence:

   • The index i46 was not being utilized correctly in the first query due to affinity mismatches. The LIKELY() function caused the optimizer to miscalculate the cost of using the index versus a full table scan.
   • With IS TRUE, the boolean context forced a re-evaluation of type compatibility, bypassing the faulty affinity logic.

Diagnostic Workflow: Identifying Affinity Conflicts in Complex WHERE Clauses

To diagnose similar issues, follow these steps:

Step 1: Simplify the Query

Reduce the query to its minimal reproducible form. For example:

SELECT count(*) FROM t1, v0 
WHERE (t1.c0 IN ()) < LIKELY(v0.c2); -- Incorrect result: 0

SELECT count(*) FROM t1, v0 
WHERE ((t1.c0 IN ()) < LIKELY(v0.c2)) IS TRUE; -- Correct result: 1

This eliminates distractions from views, indexes, and complex joins.

Step 2: Inspect Type Affinities

Use typeof() to debug column affinities:

SELECT typeof(c2) FROM v0; -- Should return 'text'
SELECT typeof(CAST((c0 IS TRUE) AS TEXT)) FROM t1; -- Verify index affinity

Step 3: Analyze Query Plans

Run EXPLAIN QUERY PLAN for both queries:

EXPLAIN QUERY PLAN 
SELECT count(*) FROM t1, v0 WHERE (t1.c0 IN ()) < LIKELY(v0.c2);

Look for discrepancies in index usage or join strategies between the two queries.

Step 4: Test Affinity Overrides

Manually enforce affinities to see if results change:

SELECT count(*) FROM t1, v0 
WHERE CAST((t1.c0 IN ()) < LIKELY(v0.c2) AS BOOLEAN);

Resolution Strategy: Upgrading and Affinity-Aware Query Design

Solution 1: Upgrade to SQLite ≥3.45.0

The fix (commit f5b3eb0f) ensures LIKELY() no longer disrupts type affinity propagation. After upgrading, both queries return 1.

Solution 2: Avoid Ambiguous Affinity Contexts

Rewrite queries to explicitly define affinities:

-- Original
SELECT count(*) FROM t1, v0 
WHERE (t1.c0 IN ()) < LIKELY(v0.c2);

-- Revised
SELECT count(*) FROM t1, v0 
WHERE CAST((t1.c0 IN ()) AS INTEGER) < CAST(LIKELY(v0.c2) AS INTEGER);

Solution 3: Indexing Best Practices

When indexing expressions with CAST, explicitly declare collations or affinities:

CREATE INDEX i46 ON t1(CAST((c0 IS TRUE) AS TEXT) COLLATE BINARY);

Solution 4: Disable Faulty Optimizations

Use + operators to inhibit index usage on problematic expressions:

SELECT count(*) FROM t1, v0 
WHERE (t1.c0 IN ()) < (+LIKELY(v0.c2)); -- + suppresses index use

Technical Deep Dive: How Affinity Propagation Failed in Buggy Versions

Affinity Determination Rules

SQLite assigns type affinity to columns based on their declaration. For expressions, affinity is derived from:

1. The outermost CAST or function.
2. Literal values.
3. Implicit conversions in comparisons.

In the original view definition:

CAST(((t1.c0) IS TRUE) AS TEXT) -- Forces TEXT affinity

The v0.c2 column should inherit TEXT affinity. However, LIKELY(DISTINCT v0.c2) in the WHERE clause caused the optimizer to strip this affinity in buggy versions, treating it as a NUMERIC value.

Impact of IS TRUE

The IS TRUE operator forces the left-hand expression to be evaluated as a boolean (0 or 1). This changes the parse tree structure, which in turn altered how the optimizer resolved affinities. The corrected versions properly preserve affinities through IS TRUE.

Index Selection Pitfalls

The i46 index was built on CAST(((c0) IS TRUE) AS TEXT), which should match the TEXT affinity of v0.c2. However, the optimizer’s flawed affinity handling led it to disregard the index in the first query. The IS TRUE version inadvertently corrected the affinity mismatch, allowing index usage.

Lessons Learned and Proactive Measures

1. Affinity Sanity Checks: Always validate type affinities with typeof() when using complex expressions in indexes or views.
2. Avoid Over-Optimization: Use LIKELY()/UNLIKELY() sparingly in contexts with mixed affinities.
3. Testing with Minimal Cases: Simplify queries to isolate optimizer bugs before reporting issues.

This bug underscores the delicate balance between SQLite’s lightweight optimizations and type affinity rules. Developers working with expression-heavy schemas should prioritize explicit type declarations and rigorous version testing.