CTE Column Count Mismatch Incorrectly Reports Circular Reference in SQLite 3.35.x

Issue Overview: Column Count Validation Failure Masquerading as Circular Reference in Complex CTE Queries

A critical regression occurred in SQLite versions 3.35.x where Common Table Expression (CTE) queries containing column count mismatches erroneously reported circular reference errors instead of proper column validation failures. This behavior manifested specifically in complex CTE structures involving multiple nested CTE definitions, window functions, and cross-referenced temporary result sets. The core problem arises when a CTE definition provides fewer values than declared columns (e.g., 10 values for 11 columns), which should trigger an explicit "table X has Y values for Z columns" error. However, under certain structural conditions in SQLite 3.35.x, the parser incorrectly interprets this as a circular dependency between CTE components.

The faulty error reporting occurs through specific query patterns involving:

1. A primary CTE (ds0 in the example) with explicit column declarations and insufficient VALUES
2. Secondary CTEs (radygrid and ypos in the example) that reference the primary CTE
3. Final SELECT statements joining multiple CTE-derived tables
4. Window function usage (WINDOW clause in ds CTE definition)
5. Recursive-looking (but non-recursive) CTE dependencies through shared column aliases

Version analysis reveals this was introduced between SQLite 3.34.1 and 3.35.2, with resolution occurring in 3.36.0 via check-in [99812236]. The bug’s stealth nature stems from its dependence on query complexity – simplified versions of the same CTE structure would correctly report column count errors, while elaborated versions with additional joins and derived tables would trigger the false circular reference. This created significant debugging challenges because the reported error (circular reference) pointed to a non-existent problem in the CTE dependency graph, while the actual issue (column/value mismatch) remained obscured.

Possible Causes: Parser Regression in CTE Column Validation and Error Reporting Hierarchy

Three primary factors contributed to this erroneous behavior in SQLite 3.35.x:

1. Altered Order of Semantic Checks in CTE Processing
SQLite’s query parser performs multiple validation passes when processing CTEs, including:

• Column count verification (number of provided VALUES vs declared columns)
• Circular dependency detection (CTEs referencing each other in recursion-prone patterns)
• Name resolution for aliases and window function bindings

In affected versions, a regression likely modified the sequence of these checks, causing circular reference detection to occur before full column count validation. When complex CTE nesting obscured the column count mismatch from early parsing phases, the later circular reference check would misinterpret residual parsing artifacts (left by the incomplete column resolution) as cyclical dependencies.

2. Window Function Binding Interference
The presence of the WINDOW w AS (PARTITION BY m, x ORDER BY n) clause in the ds CTE introduces late-binding name resolution challenges. Window function processing requires complete column metadata from underlying CTEs (ds0 in this case) to validate PARTITION BY and ORDER BY clauses. When ds0 has unresolved column count issues, the window binding process leaves the parser in an inconsistent state where subsequent CTE references (like radygrid and ypos) appear to create circular links through partially resolved column identifiers.

3. Name Shadowing in Nested CTE Scopes
The query’s extensive use of overlapping column names across CTEs (m, n, x, y, etc.) creates ambiguous reference scenarios during parsing. For example:

• ds0 declares columns m, n, x, y, x2, y2, title, size, mark, label, markmode
• Subsequent CTEs like ds and d reuse these names in their SELECT lists
• The final ypos and radygrid CTEs compound this by including columns like radial and pcx

In normal operation, SQLite’s name resolution handles this via scoping rules. However, when combined with the column count defect in ds0, the parser’s symbol table becomes contaminated with incomplete entries. This contamination leads to false-positive circular reference detection when later CTEs attempt to resolve columns that appear in multiple scopes but aren’t fully defined due to the initial column shortage.

Troubleshooting Steps, Solutions & Fixes: Diagnosing CTE Column Mismatches and Version-Specific Workarounds

Step 1: Verify Column-Value Alignment in Suspect CTEs
Begin by isolating the column count mismatch using these techniques:

Technique A: Progressive CTE Elimination

1. Comment out all CTEs except the primary suspect (ds0) and the final SELECT
2. Execute SELECT * FROM ds0 as a standalone query
3. Observe if the column count error appears correctly
4. Gradually uncomment dependent CTEs (ds, d, etc.) until the error changes to "circular reference"

Technique B: Explicit Column Count Validation
Rewrite the problematic CTE with explicit column counts:

-- Original faulty CTE
ds0(m, n, x, y, x2, y2, title, size, mark, label, markmode) AS (
  SELECT 1, 2, 3, 4, 5, 6, 7 , 8, 9, 10
)

-- Validation rewrite
ds0(m, n, x, y, x2, y2, title, size, mark, label, markmode) AS (
  SELECT
    COUNT(*) OVER () AS _col_count,
    1, 2, 3, 4, 5, 6, 7 , 8, 9, 10
)

Executing this modified query will either:

• Fail with a window function error (proving column count is processed)
• Show _col_count = 10 in results (confirming value count)

Step 2: Version-Specific Behavior Confirmation
Check SQLite version and known behavior patterns:

SELECT sqlite_version();

• 3.34.1 and earlier: Correctly report column count mismatch
• 3.35.0 to 3.35.5: Incorrect "circular reference" error
• 3.36.0+: Fixed via [99812236], proper column errors restored

Step 3: Query Simplification for Error Surface Reduction
Temporarily simplify the CTE structure to identify the minimal repro case:

1. Remove all window functions and complex joins
2. Replace WINDOW clauses with simple GROUP BY
3. Eliminate cross-CTE references not essential to the column count test
4. Test incremental complexity additions until the error flips

Example minimal reproducer:

WITH
  ds0(a, b, c) AS (SELECT 1, 2),
  secondary_cte AS (SELECT a FROM ds0)
SELECT * FROM secondary_cte;

In 3.35.x, this should still correctly report "2 values for 3 columns". If it doesn’t, deeper investigation into CTE nesting levels is required.

Step 4: Upgrade to SQLite 3.36.0 or Newer
The definitive fix requires updating to a version containing check-in [99812236]. For environments where upgrading isn’t immediately feasible, employ these workarounds:

Workaround A: Column Count Padding
Add dummy columns to match declared counts:

ds0(...) AS (
  SELECT 1, 2, 3, 4, 5, 6, 7 , 8, 9, 10, NULL AS markmode /* 11th column */
)

Workaround B: CTE Decoupling Through Temporary Tables
Break complex CTE chains using temporary tables:

CREATE TEMP TABLE temp_ds0 AS 
  SELECT 1 AS m, 2 AS n, ..., 10 AS label; /* Explicit AS aliases */

WITH 
  ds0 AS (SELECT * FROM temp_ds0),
  ...other CTEs...

Workaround C: Dynamic SQL Generation
For programmatic query builders, implement version-aware SQL generation:

if sqlite_version < (3, 36):
    generate_column_count_checks()
else:
    rely_on_native_checks()

Step 5: Parser Behavior Analysis Using EXPLAIN
Use SQLite’s EXPLAIN command to observe bytecode differences between versions:

EXPLAIN WITH ds0(...) AS (...) SELECT ...;

Compare the output between 3.35.x and 3.36+ to identify:

• Early column count checks (Opcode: ColumnCount)
• Circular reference verification (Opcode: Once)
• Window function binding (Opcode: OpenEphemeral)

In faulty versions, the ColumnCount check may appear after Once opcodes, allowing circular reference detection to trigger first.

Step 6: Schema Binding Analysis with sqlite3_prepare()
For embedded systems, use the C API to diagnose prepared statement errors:

sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
if (rc == SQLITE_ERROR) {
  const char *err = sqlite3_errmsg(db);
  /* Analyze error string for 'circular' vs 'column' */
}

This helps distinguish between actual parse-time errors (column count) and later semantic errors (circular refs).

Final Resolution:
The permanent solution requires upgrading to SQLite ≥3.36.0. For legacy systems, rigorous column count validation through query linters or pre-processors can mitigate risk. Developers should audit all CTE definitions in version 3.35.x environments using automated checks like:

SELECT 
  cte.name, 
  COUNT(cte.columns) AS declared_columns,
  (SELECT COUNT(*) FROM (cte_definition)) AS actual_values
FROM pragma_compile_options 
WHERE name = 'ENABLE_CTE';

This pseudo-code illustrates the need for manual column counting until version upgrades can be performed.