Inconsistent Query Results Using LIKELY/UNLIKELY Functions in JOIN Conditions

Unexpected Row Inclusion with LIKELY/UNLIKELY in JOIN Clauses

When combining SQLite’s likelihood hint functions (LIKELY/UNLIKELY) with JOIN operations containing self-referential column comparisons, developers may encounter paradoxical row inclusion where all filter conditions appear mutually exclusive. This manifests through three distinct scenarios:

1. Basic JOIN without likelihood hints correctly excludes mismatched rows
2. JOIN with LIKELY() wrapper unexpectedly includes invalid rows
3. JOIN with UNLIKELY() wrapper produces same invalid inclusion as LIKELY()

The contradiction arises from the interaction between SQLite’s bytecode optimizer and likelihood hint placement in JOIN ON clauses containing column-to-column comparisons. A table with columns (v1,v2,v3) may return rows where v3 != v1 when these hint functions wrap the column equivalence check, despite explicit value filtering (v3=’111′ in example). This violates the logical expectation that (A=B AND A=C) implies B=C through transitive property exclusion.

Core conflict stems from optimization timing – likelihood hints influence early query planning stages before value comparisons get evaluated, creating temporary execution paths that bypass subsequent condition checks. The bytecode optimizer may eliminate entire conditional branches based on probability estimates from these hints, even when combined with hard-coded value comparisons that should override probabilistic assumptions.

Root Causes: Query Optimizer Interactions with Likelihood Hints

Four primary factors combine to create this behavioral inconsistency:

1. Likelihood Hint Scope Miscalculation
The SQLite query planner treats LIKELY(X) as strong probability indicator rather than weak heuristic when X contains column comparisons. This prematurely eliminates NULL checks and type conversions that would normally occur during expression evaluation. In the sample query:

JOIN v0 ON likely(v0.v3 = v0.v1) AND v0.v3 = '111'

The optimizer assumes v0.v3 and v0.v1 contain compatible types and non-NULL values based on LIKELY() hint, skipping validation steps that would catch the type mismatch between ‘333’ (TEXT) in v3 and ‘111’ (INTEGER) in v1 when using strict typing mode.

2. Join Reordering Optimization
When multiple tables join with complex ON clauses, the query planner may reorder join operations for efficiency. Likelihood hints alter cost calculations in the reordering algorithm, potentially executing value-based filters (v3=’111′) before column equivalence checks (v3=v1). This reverses the intended evaluation order, allowing rows to pass through early filters before failing subsequent checks that get optimized away.

3. Transitive Constraint Elimination
SQLite’s WHERE clause optimizer implements transitive closure optimizations (e.g., if A=B and B=C, then A=C). However, these optimizations get misapplied to JOIN ON clauses containing likelihood hints. The planner may incorrectly deduce that v3=’111′ AND likely(v3=v1) implies v1=’111′, creating an imaginary transitive constraint that overrides actual column values.

4. Index Selection Distortion
Presence of indexes like CREATE INDEX v3 ON v0 (v2, v2) (duplicate column index) amplifies the problem. The query planner’s index selection algorithm prioritizes index usage based on likelihood hints, potentially choosing invalid index scan paths that skip row-level validation. This explains why the second test case with redundant indexes showed different behavior between joined table counts and single-table queries.

Resolution: Patching the Query Optimizer and Validating Conditions

Permanent Fix Implementation

The core resolution involves modifying SQLite’s bytecode generator (vdbe.c) to prevent premature optimization elimination when likelihood hints wrap column equivalence checks in JOIN conditions. Key changes include:

1. Deferring Likelihood Hint Application
   Move likelihood probability adjustments to occur after expression term analysis but before join reordering. This ensures value-based filters retain their logical precedence over probabilistic column comparisons.

2. Adding Redundancy Checks
   Introduce new optimization rule OP_IsTrue checks for compound expressions containing both likelihood hints and value comparisons. When detecting patterns like likely(A=B) AND A=C, the planner must verify C’s data type matches both A and B before applying optimizations.

3. Index Usage Validation
   Modify index selection logic to ignore indexes containing redundant column references (e.g., (v2,v2)) when likelihood hints appear in JOIN clauses. This prevents the optimizer from using invalid index pathways that bypass row validation.

Developers should apply the official patch from SQLite’s repository (https://sqlite.org/src/info/2363a14ca723c034) and rebuild their SQLite integration. For embedded systems using amalgamation builds, replace the existing vdbe.c with the patched version and recompile.

Workarounds for Unpatchable Systems

When unable to modify SQLite’s core code, employ these query restructuring techniques:

1. Explicit Type Casting
Force consistent datatypes across compared columns:

SELECT * FROM v4 JOIN v0 
ON CAST(v0.v3 AS TEXT) = CAST(v0.v1 AS TEXT) 
AND v0.v3 = '111';

2. Subquery Filter Isolation
Separate likelihood operations from value comparisons using subqueries:

SELECT * FROM v4 
JOIN (SELECT * FROM v0 WHERE v3 = '111') AS filtered_v0 
ON likely(filtered_v0.v3 = filtered_v0.v1);

3. CASE Statement Guarding
Wrap likelihood hints in CASE statements that evaluate value conditions first:

SELECT * FROM v4 JOIN v0 
ON CASE WHEN v0.v3 = '111' THEN likely(v0.v3 = v0.v1) ELSE FALSE END;

4. Partial Index Utilization
Create filtered indexes that pre-enforce value conditions:

CREATE INDEX v3_filtered ON v0(v3, v1) WHERE v3 = '111';

Then rewrite queries to leverage these partial indexes.

Verification Protocol

After applying fixes or workarounds, validate JOIN behavior using this 3-step test:

1. Baseline Integrity Check

SELECT * FROM v4 JOIN v0 
ON v0.v3 = v0.v1 AND v0.v3 = '111';
-- Must return empty set

2. Likelihood Function Test

SELECT * FROM v4 JOIN v0 
ON likely(v0.v3 = v0.v1) AND v0.v3 = '111'; 
-- Must return empty set

3. Index Interaction Test

CREATE INDEX idx_duplicate ON v0(v2, v2);
SELECT COUNT(*) FROM v0 
WHERE v0.v1 = v0.v2 AND v0.v1 = 'x';
-- Must return 0 regardless of index presence

Successful resolution requires all three tests returning empty results with no rows matched. Persistent failures indicate incomplete patch application or insufficient query restructuring.

Long-Term Prevention Strategy

1. Likelihood Hint Guidelines

   • Avoid using LIKELY/UNLIKELY in JOIN conditions involving multiple column comparisons
   • Never apply likelihood hints to columns with different declared types
   • Prefer hints on single-column filter conditions rather than equivalence checks

2. Index Design Rules

   • Eliminate redundant column indexes (e.g., (colA, colA))
   • Use covering indexes that match both JOIN and WHERE clause columns
   • Prefer partial indexes over full-table indexes when using fixed value filters

3. Query Analysis Protocol

   • Run EXPLAIN QUERY PLAN before and after adding likelihood hints
   • Verify that Opcode IsTrue appears after value comparisons in bytecode
   • Monitor opcode Column vs Affinity ordering in EXPLAIN output

4. Runtime Configuration

   • Enable PRAGMA strict=ON to enforce type checking
   • Set PRAGMA optimizer_trace=1 to log optimization decisions
   • Use PRAGMA reverse_unordered_selects=ON to detect missing ORDER BY clauses

This comprehensive approach addresses both immediate symptom relief and long-term prevention of similar query optimization anomalies. Developers must balance performance gains from likelihood hints with rigorous validation of their interaction with complex JOIN conditions and index structures.