Optimizing CTE Behavior in SQLite: Preventing Unwanted Query Inlining

Understanding CTE Optimization Boundaries and Materialization Control in SQLite

Issue Overview: Uncontrolled CTE Inlining and Materialization Side Effects

Common Table Expressions (CTEs) in SQLite are powerful tools for organizing complex queries. However, their interaction with the query optimizer introduces challenges when developers need precise control over execution plans. The core issue revolves around unwanted inlining of CTE definitions into outer queries, particularly when CTEs involve non-deterministic functions (e.g., random()), external data sources, or when specific execution order matters.

In SQLite’s current implementation (as of version 3.41.2), CTEs default to being treated as optimization-friendly syntactic sugar unless they meet specific criteria for materialization. This creates two problematic scenarios:

1. Non-Recursive CTE Inlining: When a CTE isn’t recursive and doesn’t contain volatile functions, the optimizer may substitute CTE references directly into parent queries, effectively rewriting:

   WITH cte AS (SELECT ...) SELECT * FROM cte
   

   as

   SELECT * FROM (SELECT ...)
   

   This destroys CTE boundary integrity when needed.

2. Materialization Paradox: While the MATERIALIZED hint forces CTE evaluation as a separate step, it creates full materialization that may be unnecessary or inefficient. The NOT MATERIALIZED hint currently acts as a null operation in most non-recursive cases, providing no optimization boundary protection.

Key Symptom Example:

WITH randomvalues(value) AS (
  SELECT random() 
  FROM generate_series(1,1000)
)
SELECT value, value+1 FROM randomvalues LIMIT 10;

Here, random() gets evaluated once per overall query execution rather than once per row due to inlining, producing identical values across all rows instead of unique random numbers. This violates developer expectations established through recursive CTE behavior where evaluation happens row-by-row.

Execution Plan Analysis:
A non-recursive CTE’s query plan shows direct table scans without co-routine creation:

QUERY PLAN
`--SCAN generate_series VIRTUAL TABLE INDEX 3:

Contrast with the recursive workaround’s plan showing proper co-routine handling:

QUERY PLAN
|--CO-ROUTINE randomvalues
| |--SETUP
| | `--SCAN generate_series VIRTUAL TABLE INDEX 3:
| `--RECURSIVE STEP
|   `--SCAN randomvalues
`--SCAN randomvalues

This discrepancy highlights SQLite’s current inability to declare non-recursive CTE boundaries that prevent optimization-driven inlining while avoiding full materialization.

Technical Roots: SQLite’s CTE Optimization Philosophy

Three fundamental factors contribute to this behavior:

1. CTE Classification Hierarchy
SQLite categorizes CTEs into three optimization classes:

The absence of explicit co-routine designation for non-recursive CTEs creates an optimization "blind spot" where valuable boundary protections only exist for recursive CTEs.

2. Hint Semantics vs Reality
Documentation states that MATERIALIZED/NOT MATERIALIZED are hints, but observed behavior reveals:

• MATERIALIZED acts as a directive forcing full materialization
• NOT MATERIALIZED functions as a null operation in non-recursive contexts
• Recursive CTEs implicitly gain co-routine status regardless of hints

This creates a semantic gap between documented behavior and actual implementation.

3. Volatile Function Handling
SQLite’s optimizer lacks special handling for volatile functions (e.g., random(), datetime()) in CTEs. When CTEs get inlined, volatile functions execute according to parent query structure rather than CTE definition context. This contrasts with recursive CTE handling where volatile functions re-evaluate per row due to co-routine execution.

Resolution Strategies: Controlling CTE Evaluation Without Query Planner Modifications

Until SQLite implements formal optimization boundary controls, use these workarounds:

1. Co-routine Forcing via Pseudo-Recursion
Add a vacuous recursive term to non-recursive CTEs:

WITH cte AS (
  SELECT base_query
  UNION ALL
  SELECT recursive_query WHERE 0
)

Example Implementation:

WITH randomvalues(value) AS (
  SELECT random()
  FROM generate_series(1,1000)
  UNION ALL
  SELECT value FROM randomvalues WHERE FALSE
)
SELECT value, value+1 FROM randomvalues LIMIT 10;

Mechanics:

• The UNION ALL triggers recursive CTE classification
• WHERE FALSE prevents actual recursion
• Forces co-routine execution with row-by-row evaluation
• Adds negligible overhead (recursive step exists in plan but never executes)

Execution Plan Verification:
Check for CO-ROUTINE in plan:

|--CO-ROUTINE randomvalues
| |--SETUP
| | `--SCAN generate_series
| `--RECURSIVE STEP
|   `--SCAN randomvalues

2. Strategic Materialization with Side Effect Isolation
Force materialization when CTE contains volatile functions or external dependencies:

WITH cte AS MATERIALIZED (
  SELECT random() AS value
  FROM generate_series(1,1000)
)
SELECT value, value+1 FROM cte LIMIT 10;

Tradeoffs:

• ✅ Guarantees single CTE evaluation
• ✅ Prevents volatile function reordering
• ❌ Creates temporary table storage
• ❌ May increase memory usage for large datasets

3. Query Structure Modifications
Restructure queries to discourage inlining:

SELECT value, value+1 
FROM (
  SELECT random() AS value 
  FROM generate_series(1,1000)
) AS randomvalues
LIMIT 10;

Wrap CTE-like logic in subqueries with LIMIT clauses or WHERE conditions that encourage co-routine behavior.

4. Volatile Function Wrapping
Create deterministic-looking expressions that hide volatility:

WITH cte AS (
  SELECT (SELECT random()) AS value
  FROM generate_series(1,1000)
)
SELECT value, value+1 FROM cte LIMIT 10;

The scalar subquery forces per-row evaluation even when CTE gets inlined.

5. Schema-Based Encapsulation
Use temporary views for critical CTE-like structures:

CREATE TEMP VIEW temp_random AS 
SELECT random() AS value 
FROM generate_series(1,1000);

SELECT value, value+1 FROM temp_random LIMIT 10;

Views provide stronger optimization boundaries than CTEs in some SQLite versions.

6. Version-Specific Optimizations
For SQLite 3.34.0+, use MATERIALIZED hint explicitly:

WITH cte(value) AS MATERIALIZED (
  SELECT random()
  FROM generate_series(1,1000)
)
SELECT value, value+1 FROM cte LIMIT 10;

Verify version compatibility using sqlite3_exec() or PRAGMA user_version.

Long-Term Considerations and Best Practices

Monitoring Query Plans
Regularly analyze plans using EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
WITH cte AS (...)
SELECT ...;

Key indicators:

• CO-ROUTINE: Desired co-routine execution
• SCAN: Potential inlining
• MATERIALIZE: Full materialization

Volatile Function Management
Assume volatile functions in CTEs will inline until proven otherwise. Prefer pseudo-recursive patterns when row-wise evaluation is critical.

Materialization Cost Analysis
Evaluate tradeoffs using runtime measurements:

.timer ON
-- Compare materialized vs non-materialized versions

Community Advocacy
Contribute to SQLite’s enhancement requests for:

• Formal BOUNDARY modifier in CTE definitions
• True NOT MATERIALIZED optimization fence behavior
• Volatile function recognition in CTE optimization decisions

Cross-Database Compatibility
When porting PostgreSQL queries:

• Replace NOT MATERIALIZED with pseudo-recursive patterns
• Use MATERIALIZED in place of WITH MATERIALIZED
• Implement explicit volatile function handling

By combining immediate workarounds with systematic query analysis practices, developers can achieve precise control over CTE execution behavior in SQLite. The solutions outlined here provide robust alternatives while awaiting potential enhancements to SQLite’s CTE optimization framework.