Incorrect LEFT JOIN Results in SQLite Due to Aggressive Optimization

Understanding the Mismatch Between Expected and Observed LEFT JOIN Behavior

The core issue revolves around unexpected results when using LEFT JOIN in SQLite versions 3.35.0 through 3.37.0. Queries that logically should return all rows from the left table, with NULL values for the right table when the join condition is unmet, instead return fewer rows—behaving as if an INNER JOIN were used. This discrepancy arises under specific structural conditions in the query, particularly when the ON clause of the LEFT JOIN references only columns from the left table. The problem was introduced in SQLite version 3.35.0 due to an optimization intended to improve query performance but inadvertently caused incorrect join behavior in edge cases. Affected queries include those involving subqueries with UNION ALL, derived tables, or standalone SELECT statements joined to the left table.

Example Scenario
Consider the following minimal test case:

CREATE TABLE onerow(x INT);
INSERT INTO onerow(x) VALUES(0);

SELECT *
FROM (
  SELECT NULL AS aaa FROM onerow
  UNION ALL
  SELECT 'missing' AS aaa FROM onerow
) AS a
LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;

Expected Result: Two rows (one with aaa = NULL and bbb = 1, another with aaa = 'missing' and bbb = NULL).
Observed Result: Only one row (with aaa = NULL and bbb = 1).

This behavior contradicts the fundamental definition of LEFT JOIN, which guarantees that all rows from the left table are retained, regardless of whether the ON condition evaluates to true. The issue is reproducible in SQLite versions 3.35.0–3.37.0 but works correctly in version 3.34.0 and earlier.

Root Cause: Overeager LEFT JOIN-to-INNER JOIN Conversion

The problem stems from an optimization introduced in SQLite 3.35.0 to improve query performance by converting LEFT JOIN operations into INNER JOIN when certain conditions are met. The optimization logic, documented in the SQLite Optimization Overview, states:

A LEFT JOIN can be demoted to an ordinary JOIN if there are terms in the WHERE clause that guarantee all rows of the right table would be excluded when the LEFT JOIN result is filtered by the WHERE clause.

However, the implementation incorrectly applied this optimization in cases where the ON clause of the LEFT JOIN references only columns from the left table. In such scenarios, the optimizer mistakenly concluded that the join could be safely converted to an INNER JOIN because the ON clause did not depend on the right table. This assumption is flawed because the LEFT JOIN should still return all rows from the left table, even if the ON clause evaluates to false or NULL.

Why the Optimization Backfired
In the example query:

LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL

The ON clause a.aaa IS NULL does not reference any column from the right table (b). The optimizer incorrectly determined that the presence of this condition allowed the LEFT JOIN to be replaced with an INNER JOIN. This led to the exclusion of rows from the left table where a.aaa was not NULL (e.g., 'missing'), as the INNER JOIN would filter them out.

Trigger Conditions
The issue manifests when:

1. The ON clause of the LEFT JOIN references only columns from the left table.
2. The right table is a subquery, derived table, or a table with no rows.
3. The query structure involves operations like UNION ALL or subqueries that prevent the optimizer from accurately assessing join dependencies.

Resolving the Issue: Validation, Fixes, and Workarounds

Step 1: Confirm the SQLite Version and Reproduce the Issue

1. Check the SQLite Version:
   Run SELECT sqlite_version();. If the result is between 3.35.0 and 3.37.0, the issue may affect your queries.

2. Run a Simplified Test Case:
   Execute the following query:

   SELECT * 
   FROM (SELECT 'missing' AS aaa FROM (SELECT 1)) AS a
   LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;
   

   Expected: One row with aaa = 'missing', bbb = NULL.
   Observed in Affected Versions: Zero rows.

Step 2: Apply the Official Patch or Upgrade SQLite

The problem was resolved in SQLite check-in 1f6796044008e6f3. To fix the issue:

1. Upgrade to SQLite 3.37.0 or Later: Versions including the patch correctly handle LEFT JOIN optimizations.
2. Recompile from Source: If using a custom build, integrate the fix by updating to a code revision after the check-in.

Step 3: Implement Workarounds for Unpatched Environments

If upgrading is not immediately feasible, modify the query to bypass the optimization:

Workaround 1: Reference the Right Table in the ON Clause
Add a tautological condition that references the right table:

LEFT JOIN (SELECT 1 AS bbb) AS b 
  ON a.aaa IS NULL AND b.bbb IS NOT NULL;

This forces the optimizer to recognize the dependency on the right table, preventing the demotion to INNER JOIN.

Workaround 2: Use UNION Instead of UNION ALL
In queries with UNION ALL, replace it with UNION to alter the query structure:

SELECT *, sqlite_version()
FROM (
  SELECT NULL val FROM (SELECT 1)
  UNION  -- Instead of UNION ALL
  SELECT 'missing' FROM (SELECT 1)
) a
LEFT JOIN (SELECT 1)
  ON a.val IS NULL;

The UNION operator removes duplicates, which changes how the optimizer evaluates the subquery, avoiding the problematic optimization.

Workaround 3: Use a Dummy WHERE Clause
Add a redundant condition to the outer query:

SELECT * 
FROM (...)
LEFT JOIN (...) ON a.aaa IS NULL
WHERE 1=1;

This disrupts the optimizer’s ability to apply the incorrect transformation.

Workaround 4: Materialize the Right Table
Force the right table to be materialized as a Common Table Expression (CTE):

WITH b AS (SELECT 1 AS bbb)
SELECT *
FROM a
LEFT JOIN b ON a.aaa IS NULL;

This changes the optimization path, preventing the demotion.

Step 4: Review and Test All LEFT JOIN Queries

Audit existing queries for patterns matching the trigger conditions:

• LEFT JOIN with ON clauses referencing only left-table columns.
• Joins to subqueries or derived tables.
• Use of UNION ALL in subqueries.

Test these queries with the patched SQLite version or applied workarounds to ensure correctness.

Conclusion

The incorrect LEFT JOIN behavior in SQLite 3.35.0–3.37.0 was caused by an overeager optimization that failed to account for edge cases where the ON clause exclusively referenced the left table. By upgrading to a fixed version, applying targeted workarounds, or restructuring queries to avoid triggering the optimization, users can restore the expected join semantics. This issue underscores the importance of rigorous testing when leveraging database optimizations, especially in complex query scenarios.