Issue Overview: Subquery-Based Conditions Bypass Short-Circuit Optimization

When filtering results using compound logical conditions in SQLite, developers often rely on short-circuit evaluation to optimize performance. This optimization allows the database engine to skip evaluating expensive secondary conditions when a primary condition already determines the final truth value. The core issue arises when equivalent logical conditions are expressed through subqueries versus direct column references, leading to radically different bytecode generation that bypasses short-circuit evaluation.

In the provided scenario, two queries filtering a 100K-row user table demonstrate this divergence:

1. Direct condition: WHERE (name LIKE '%john%' AND regexp_like(name, '%john%')) executes in ~15ms due to short-circuit-enabled bytecode using IfNot opcodes
2. Subquery-wrapped condition: Equivalent logic wrapped in a subquery executes in ~150ms due to bytecode that evaluates both conditions unconditionally

The critical difference lies in SQLite’s bytecode generation strategy. The first query uses sequential IfNot instructions (addresses 5 and 8 in EXPLAIN output) that test each condition’s result and exit early when any condition fails. The subquery variant instead generates code (addresses 15-19) that calculates both conditions’ results first before combining them with an And opcode, forcing full evaluation of both expressions regardless of their individual outcomes.

Possible Causes: Query Structure Dictates Bytecode Optimization Boundaries

Three primary factors contribute to the observed performance discrepancy:

1. Subquery Materialization Overhead
The __atk4_reuse_tmp__ CTE materializes column references as temporary values, converting what should be direct column accesses into derived table scans. This forces SQLite to:

• Instantiate a coroutine (opcodes 4-9) for subquery execution
• Store intermediate results in registers 5-6 via Copy operations
• Process conditions through function calls rather than direct column tests

Materialization breaks the query planner’s ability to recognize the conditions as optimizable through short-circuit evaluation, as they’re now treated as separate expressions within a subquery context.

2. AND Operator Context Sensitivity
SQLite applies different optimization rules to AND operators depending on their syntactic context:

• Top-level WHERE clause: Conditions are evaluated left-to-right with short-circuiting
• Subquery/SELECT list: Conditions are treated as independent expressions whose evaluation order isn’t guaranteed

The bytecode’s And opcode at address 19 combines pre-computed results from both conditions, indicating the optimizer lost visibility into the relationship between the LIKE and regexp_like calls when they were embedded in a subquery.

3. Coroutine Execution Model Limitations
Subqueries in SQLite often execute through coroutines (visible via InitCoroutine and Yield opcodes), which implement a form of cooperative multitasking. This execution model:

• Requires complete evaluation of subquery expressions before returning control
• Creates register pressure by storing intermediate results
• Inhibits interleaved condition evaluation across parent/child query scopes

The coroutine’s need to fully materialize its result set (addresses 4-22) prevents early termination of condition evaluation, even when the first condition would logically make further processing unnecessary.

Troubleshooting Steps, Solutions & Fixes: Enforcing Evaluation Order Across Query Structures

Step 1: Analyze Bytecode Patterns for Short-Circuit Indicators
Use EXPLAIN to verify presence of conditional jump opcodes (IfNot, IfNull, etc.) between condition evaluations. Valid short-circuit patterns show:

5,IfNot,1,12,1,,0,  -- Jump to address 12 if LIKE fails
...
8,IfNot,1,12,1,,0,  -- Jump to address 12 if regexp_like fails

Absence of these jumps between conditions indicates lost optimization.

Step 2: Restructure Subqueries Using CASE-Based Short-Circuiting
Rewrite subquery conditions to explicitly enforce evaluation order:

SELECT id, name
FROM user
WHERE (
  SELECT CASE
    WHEN __atk4_reuse_left__ LIKE __atk4_reuse_right__
    THEN regexp_like(__atk4_reuse_left__, __atk4_reuse_right__)
    ELSE 0
  END
  FROM (SELECT name __atk4_reuse_left__, '%john%' __atk4_reuse_right__)
)

This forces SQLite to evaluate the regexp_like UDF only when the LIKE condition succeeds.

Step 3: Flatten Query Structure Where Possible
Eliminate unnecessary subqueries by directly incorporating their logic:

SELECT id, name
FROM user
WHERE name LIKE '%john%' 
  AND regexp_like(name, '%john%')

If subquery wrapping is unavoidable for abstraction purposes, consider using LATERAL joins in SQLite 3.30+:

SELECT u.id, u.name
FROM user u
JOIN LATERAL (
  SELECT u.name LIKE '%john%' AS cond1,
         regexp_like(u.name, '%john%') AS cond2
) l ON l.cond1 AND l.cond2

Step 4: Utilize Partial Indexes for Fixed Patterns
For frequently searched patterns like ‘%john%’, create indexes that precompute matches:

CREATE INDEX user_name_john_idx ON user(name)
WHERE name LIKE '%john%'

This allows the regexp_like UDF to execute only against pre-filtered rows.

Step 5: Implement UDF Short-Circuit Hooks
Modify the regexp_like UDF to immediately return false when passed already-failing inputs:

void regexp_like(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
) {
  if(sqlite3_value_type(argv[0]) == SQLITE_NULL) {
    sqlite3_result_int(ctx, 0);
    return;
  }
  // Existing implementation...
}

This provides some protection against unnecessary processing even when SQL-level short-circuiting fails.

Step 6: Leverage STAT4 Extended Statistics
Help the query planner better estimate condition selectivity:

ANALYZE;
PRAGMA analysis_limit=100000;

Improved statistics may help the optimizer recognize when subquery flattening could restore short-circuit behavior.

Step 7: Use CTE Materialization Hints
Control subquery materialization with MATERIALIZED/NOT MATERIALIZED hints (SQLite 3.34+):

WITH __atk4_reuse_tmp__ (__atk4_reuse_left__, __atk4_reuse_right__) AS (
  SELECT name, '%john%' FROM user
) NOT MATERIALIZED
SELECT id, name 
FROM user
WHERE (SELECT ... FROM __atk4_reuse_tmp__)

Preventing materialization helps maintain condition evaluation order.

Step 8: Monitor Optimization Flags
Check if short-circuit disabling relates to specific query planner switches:

PRAGMA optimizer_trace=1;
-- Run query
SELECT * FROM sqlite_optimizer_trace;

Look for traces containing disableShortCircuit and consider overriding with:

SELECT id, name
FROM user
WHERE +(__atk4_reuse_left__ LIKE __atk4_reuse_right__ AND ...)

The unary + operator disables some optimizations that might interfere with short-circuiting.

Final Solution: Hybrid Approach Combining Structural Simplicity and UDF Optimization
For maximum reliability:

1. Avoid wrapping conditions in subqueries unless absolutely necessary
2. Use explicit CASE-based evaluation order in subqueries
3. Augment UDFs with early-exit checks
4. Create supporting indexes for left-side conditions

Example implementation:

CREATE INDEX user_name_like_prefix ON user(substr(name, instr(name, 'john'), 4));

SELECT id, name
FROM user
WHERE CASE 
  WHEN name LIKE '%john%' THEN regexp_like(name, '%john%')
  ELSE 0 
END;

This combines index-assisted pattern matching with explicit short-circuit control while keeping the query structure flat enough for the optimizer to apply bytecode improvements.