Inconsistent Query Results with INNER JOIN and Automatic Index Optimization in SQLite 3.38.2

Issue Overview: Automatic Index Optimization Incorrectly Applies LEFT JOIN Constraints to INNER JOIN Operations

The core issue reported in the discussion involves a regression in SQLite version 3.38.2 where certain queries combining INNER JOIN and LEFT JOIN operations return inconsistent results. Specifically, a query that worked correctly in SQLite 3.34.0 began returning no results on the first execution in 3.38.2, then returned expected results on subsequent executions. This behavior was traced to an optimization introduced in SQLite’s query planner related to automatic index construction during JOIN operations.

The problematic query joined two views (localized_defineservice_c_closingfolderorgunittp and localized_defineservice_c_closinghierarchynodetp) with an INNER JOIN condition on their primary keys. The database schema revealed that these views were defined with complex underlying structures involving multiple tables. The critical factor was the interaction between automatic index creation for the INNER JOIN and constraints derived from a subsequent LEFT JOIN in the query. In SQLite 3.38.2, the query planner incorrectly used a predicate from the LEFT JOIN (t2.d=1 in the simplified test case) to filter rows during the automatic index construction phase for the INNER JOIN, leading to premature row elimination.

This optimization error caused the automatic index (a transient structure used to accelerate JOIN operations) to exclude valid rows that should have been preserved by the INNER JOIN logic. The transient nature of automatic indexes meant that subsequent query executions might behave differently if the index was not regenerated (due to cached query plans or schema changes), explaining the inconsistent results between first and second runs. The root cause was identified as an over-aggressive attempt to minimize automatic index size by incorporating constraints from outer joins (LEFT JOIN) into inner join optimization logic.

Possible Causes: Query Planner Over-Optimization of Automatic Indexes Using Outer Join Constraints

The regression stemmed from changes made in SQLite commit c1085ea412b5c78d58cad59273d71f44d39843c5, which enhanced the query planner’s ability to leverage constraints from WHERE clauses and JOIN conditions to reduce the size of automatic indexes. While this optimization improved performance in many cases, it introduced a critical flaw when dealing with queries containing both INNER JOIN and LEFT JOIN operations. The query planner began applying constraints from the LEFT JOIN’s ON clause to the automatic index created for the preceding INNER JOIN, violating the logical order of query execution.

Automatic indexes in SQLite are transient indexes created on-the-fly to optimize specific queries. They differ from persistent indexes in that they exist only for the duration of a single query execution. The flawed optimization caused the automatic index for the INNER JOIN between t1 and t2 (in the simplified test case) to incorporate the t2.d=1 constraint from the subsequent LEFT JOIN with t3. This was invalid because the LEFT JOIN’s constraint should only apply to the relationship between t2 and t3, not to the earlier INNER JOIN between t1 and t2. By applying this outer join constraint prematurely, the automatic index excluded rows from t2 where d!=1, even though those rows were valid matches for the INNER JOIN condition t2.c=t1.a.

This misapplication of constraints occurred because the query planner’s logic for identifying "invariant" constraints (conditions that remain true throughout the query) incorrectly classified the LEFT JOIN condition as applicable to the earlier INNER JOIN. The optimization was particularly harmful in scenarios where the LEFT JOIN’s constraint referenced columns from the right-hand table of the INNER JOIN (t2 in the test case), creating a false dependency between the JOIN operations. The transient nature of automatic indexes exacerbated the problem because database statistics and index metadata weren’t preserved between query executions, leading to inconsistent behavior when the same query was run multiple times.

Troubleshooting Steps, Solutions & Fixes: Validating Automatic Index Constraints and Upgrading to Patched Versions

Step 1: Verify SQLite Version and Reproduce the Issue
Confirm the SQLite version using SELECT sqlite_version();. If using 3.38.0 through 3.38.2, attempt to reproduce the issue with the following test case:

CREATE TABLE t1(a INT, b INT);
CREATE TABLE t2(c INT, d INT);
CREATE TABLE t3(e TEXT, f TEXT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 2);
SELECT * FROM t1 JOIN t2 ON (t2.c=t1.a) LEFT JOIN t3 ON (t2.d=1);

In affected versions, this query will return no rows despite valid matches between t1 and t2. The expected result should include the joined t1/t2 row with NULL values for t3.

Step 2: Analyze Query Plan and Automatic Index Usage
Use the EXPLAIN QUERY PLAN command to inspect how SQLite is executing the query. Look for automatic index creation operations:

EXPLAIN QUERY PLAN
SELECT * FROM t1 JOIN t2 ON (t2.c=t1.a) LEFT JOIN t3 ON (t2.d=1);

In problematic versions, the output will show an automatic index on t2 that incorporates the d=1 constraint from the LEFT JOIN. This manifests as USING AUTOMATIC COVERING INDEX with a filter on d=1 applied during the INNER JOIN phase.

Step 3: Apply Immediate Workarounds
Until upgrading to a patched version, consider these mitigations:

1. Disable Automatic Indexing Temporarily:

   PRAGMA automatic_index = OFF;
   

   This forces the query planner to use alternative join strategies, though it may impact performance.

2. Materialize Intermediate Results:
   Rewrite the query to use subqueries or CTEs to isolate the INNER JOIN from the LEFT JOIN:

   WITH inner_join AS (
     SELECT * FROM t1 JOIN t2 ON t2.c = t1.a
   )
   SELECT * FROM inner_join LEFT JOIN t3 ON t3.e = inner_join.d;
   

3. Create Persistent Indexes:
   Add explicit indexes on the JOIN columns to bypass automatic index creation:

   CREATE INDEX idx_t2_c ON t2(c);
   CREATE INDEX idx_t2_d ON t2(d);
   

Step 4: Upgrade to SQLite 3.38.3 or Later
The fix was implemented in check-in 134cfb18ff930e4b and backported to the 3.38 series in version 3.38.3. Upgrade using one of these methods:

• Official Binaries: Download precompiled binaries from sqlite.org/download
• Compile from Source:

  wget https://sqlite.org/2022/sqlite-autoconf-3380300.tar.gz
  tar xzf sqlite-autoconf-3380300.tar.gz
  cd sqlite-autoconf-3380300
  ./configure && make && sudo make install
  

Step 5: Validate the Fix
After upgrading, re-run the test query and verify that:

1. The first execution returns the expected rows
2. The query plan no longer applies LEFT JOIN constraints to INNER JOIN automatic indexes
3. Consistent results are achieved across multiple executions

Step 6: Review Complex Queries for Similar Patterns
Audit existing queries that combine INNER JOIN and OUTER JOIN operations, particularly those joining more than two tables. Look for:

• JOIN conditions that reference columns from earlier tables in later JOIN clauses
• Queries exhibiting non-deterministic behavior across executions
• Automatic indexes appearing in EXPLAIN QUERY PLAN outputs for multi-join queries

Step 7: Implement Regression Testing
Add test cases to your validation suite that specifically exercise multi-join queries with mixed INNER and OUTER JOIN types. Use the following pattern:

-- Test case structure
CREATE TABLE base1 (id INT PRIMARY KEY);
CREATE TABLE base2 (id INT, ref1 INT);
CREATE TABLE base3 (id INT, ref2 INT);

INSERT INTO base1 VALUES (1), (2);
INSERT INTO base2 VALUES (1,1), (2,2);
INSERT INTO base3 VALUES (1,99), (2,100);

-- Problematic query pattern
SELECT * 
FROM base1 
JOIN base2 ON base2.ref1 = base1.id 
LEFT JOIN base3 ON base3.ref2 = base2.id;

-- Expected result: 2 rows with base3 data for ref2=1 and ref2=2

Permanent Solution:
The definitive resolution requires upgrading to SQLite 3.38.3 or later, where the query planner properly segregates constraints between INNER JOIN and subsequent OUTER JOIN operations. The fix involved modifying the constraint analysis logic in src/expr.c to avoid considering outer join constraints when building automatic indexes for inner joins. Developers should also note the corrected typo in the source code comment (changed "double" to "doubt") as an indicator of the patched version when reviewing source installations.

Preventative Measures:

1. Always test complex JOIN queries across multiple SQLite versions when upgrading
2. Use EXPLAIN QUERY PLAN regularly to understand automatic index decisions
3. Consider strategic use of PRAGMA automatic_index = OFF in performance-critical sections that show regressions after upgrades
4. Monitor the SQLite changelog for query planner improvements and regressions

By methodically applying these steps, developers can resolve the immediate issue of missing JOIN results, prevent future regressions through upgraded SQLite versions, and establish robust testing practices to catch similar query planner optimizations that may cross logical boundaries between different JOIN types.