Unexpected JOIN Results When Using Constants vs. Subqueries in ON Clause

Inconsistent COUNT(*) Behavior in Complex JOIN Operations with Conditional Expressions

The core issue revolves around inconsistent results when executing two structurally similar queries involving INNER JOIN, FULL OUTER JOIN, and conditional expressions in the ON clause. The first query uses a constant arithmetic expression (0)/(1) in the ON condition, while the second replaces the denominator with a subquery (SELECT COUNT(v0.c0) AS c0 FROM v0). Both expressions evaluate to 0 (interpreted as FALSE in SQLite’s Boolean context), yet the queries return different counts (0 vs. 1). This discrepancy violates the expectation that equivalent logical conditions should produce identical results. The problem arises from SQLite’s internal handling of constant expressions versus subqueries during query optimization and execution, particularly in multi-join scenarios.

Schema and Query Breakdown

1. Tables and Views:

   • vt0(c0): Base table with one row containing 1.
   • rt1(c0): Base table with one row containing 1.
   • v0(c0): View defined as SELECT ((rt1.c0) NOTNULL) FROM rt1. This view resolves to 1 because rt1.c0 is 1 (non-NULL).

2. Queries:

   • First Query: SELECT COUNT(*) FROM vt0 INNER JOIN v0 ON ((0)/(1)) FULL OUTER JOIN rt1;
     Returns 0.
   • Second Query: SELECT COUNT(*) FROM vt0 INNER JOIN v0 ON ((0)/(SELECT COUNT(v0.c0) FROM v0)) FULL OUTER JOIN rt1;
     Returns 1.

Expected vs. Actual Behavior

Both (0)/(1) and (0)/(SELECT ...) evaluate to 0 (logical FALSE). The INNER JOIN between vt0 and v0 should produce no rows because the ON condition is FALSE. The subsequent FULL OUTER JOIN with rt1 should retain all rows from rt1 (1 row) even if no matches exist from the left side. Thus, both queries should return 1. The unexpected 0 result in the first query indicates a flaw in SQLite’s handling of constant-folded expressions in multi-join operations.

Implicit Optimization Flags and Subquery Evaluation in JOIN Conditions

The root cause lies in SQLite’s query optimizer and its treatment of constant expressions versus subqueries in ON clauses. Specifically, the optimizer applies aggressive constant folding and join reordering for expressions it deems static, which alters the execution plan when subqueries introduce dynamic evaluation.

Key Factors Contributing to the Issue

1. Constant Folding in JOIN Conditions:

   • SQLite precomputes constant expressions like (0)/(1) during parsing, replacing them with a static 0. This allows the optimizer to simplify the INNER JOIN vt0 ON 0 to a no-op, effectively removing the join from the execution plan. The subsequent FULL OUTER JOIN rt1 is then interpreted as a standalone FULL OUTER JOIN between an empty result set and rt1, which SQLite handles inconsistently.

2. Subquery-Induced Dynamic Evaluation:

   • When the denominator is a subquery (SELECT COUNT(v0.c0) FROM v0), SQLite cannot fold the expression at parse time. The ON condition is evaluated dynamically during query execution. This forces the optimizer to process the INNER JOIN as a real operation, preserving the logical structure of the query. The FULL OUTER JOIN then correctly retains rows from rt1.

3. Join Reordering and Empty Intermediate Results:

   • The optimizer’s reordering of joins when faced with a constant-folded condition leads to an incorrect handling of FULL OUTER JOIN. If the left side of the FULL OUTER JOIN is an empty result set (due to the optimized-out INNER JOIN), SQLite fails to account for the right side (rt1) in the final count.

4. Type Handling and Implicit Conversions:

   • The arithmetic expression (0)/(1) returns an integer 0, while the subquery SELECT COUNT(...) returns a numeric value. SQLite’s type affinity rules may treat these differently in edge cases, though this is secondary to the primary issue of optimization flags.

Correcting JOIN Evaluation Logic and Ensuring Consistent Query Optimization

Step 1: Verify SQLite Version and Apply Patches

• Check Version: Confirm the SQLite version using SELECT sqlite_version();. Versions prior to the fix (1783655e) will exhibit the bug.
• Apply Fix: Update to a version containing the fix or apply the patch directly if using a custom build. The fix addresses the optimizer’s over-aggressive constant folding in multi-join queries.

Step 2: Rewrite Queries to Bypass Optimization Pitfalls

• Explicit Join Ordering: Use parentheses to enforce the evaluation order of joins. Example:

  SELECT COUNT(*) 
  FROM (vt0 INNER JOIN v0 ON ((0)/(1))) 
  FULL OUTER JOIN rt1 ON 1;
  

  Adding ON 1 to the FULL OUTER JOIN clarifies the join condition and prevents misinterpretation.

• Avoid Constant-Folded Expressions in JOINs: Replace static arithmetic with a subquery that returns the same value. This forces dynamic evaluation and consistent behavior:

  SELECT COUNT(*) 
  FROM vt0 
  INNER JOIN v0 ON ((0)/(SELECT 1))
  FULL OUTER JOIN rt1;
  

Step 3: Analyze and Compare Execution Plans

• Use EXPLAIN: Run EXPLAIN on both queries to inspect bytecode differences. The faulty query will show a skipped INNER JOIN (no row iterations), while the correct query includes loop operations for the join.

  EXPLAIN SELECT COUNT(*) FROM vt0 INNER JOIN v0 ON ((0)/(1)) FULL OUTER JOIN rt1;
  

  Look for OpenEphemeral or Rewind instructions indicating empty result sets.

Step 4: Test Edge Cases and Validate Fixes

• Empty Table Scenarios: Test with vt0 and rt1 empty to verify FULL OUTER JOIN correctness.
• Type Consistency Checks: Ensure subqueries and constants return the same data type using typeof():

  SELECT typeof((0)/(1)), typeof((0)/(SELECT 1));
  

Step 5: Implement Workarounds for Legacy Versions

• Disable Specific Optimizations: Use PRAGMA optimizer_control to toggle flags (if available in custom builds).
• Materialize Intermediate Results: Use CTEs or temporary tables to force evaluation before joins:

  WITH inner_join AS (
    SELECT * FROM vt0 INNER JOIN v0 ON ((0)/(1))
  )
  SELECT COUNT(*) FROM inner_join FULL OUTER JOIN rt1;
  

Step 6: Monitor Future Query Optimization Changes

• Review Release Notes: Track SQLite changelogs for optimizer-related updates.
• Regression Testing: Incorporate queries with complex joins into test suites to catch regressions early.

Technical Deep Dive: How the Fix Addresses the Issue

The patch 1783655e modifies the optimizer’s handling of constant expressions in join conditions. Specifically, it prevents the premature elimination of joins when constant-folded expressions would otherwise discard valid FULL OUTER JOIN semantics. By deferring the evaluation of constant conditions to the execution phase, SQLite ensures that all join participants are correctly accounted for, even if intermediate results are empty. This aligns the behavior of constant and subquery-based conditions in ON clauses.