Unexpected Empty Result in Complex Join and Subquery SQLite Query

Issue Overview: Complex Multi-Table Join with Subqueries Yields Contradictory Filtered Results

The core issue revolves around a SQLite query involving multiple joins (INNER JOIN, RIGHT JOIN), subqueries, and a view definition that produces contradictory results when an additional WHERE clause is applied. The first query returns a row with t1.c0 = 1, while the second query—which adds a WHERE clause filtering on the same non-zero t1.c0—returns an empty result set. This behavior is unexpected because a non-zero value should logically satisfy the WHERE condition. The problem manifests in SQLite versions after 3.40 (specifically trunk versions prior to Dec 9th, 2024) due to an optimizer bug resolved in commit a350ea7c6b89725b.

The schema includes two base tables (t0, t1), a view (v0), and two subqueries in the main SELECT statements. The view v0 is defined as a NATURAL LEFT JOIN between t0 and t1, which introduces implicit join conditions based on column names. The main query combines v0, subqueries, and explicit joins with t1, creating a complex interaction between join order, column visibility, and predicate evaluation. The contradiction arises because the WHERE clause in the second query filters out a row that the first query demonstrates should exist.

Key components contributing to the issue include:

• View Definition with NATURAL LEFT JOIN: This creates implicit equality conditions for columns with the same name (c0, c1).
• Mixed Join Types (INNER JOIN, RIGHT JOIN): These alter the visibility of columns from joined tables.
• Subquery Column Aliases: The subqueries alias their outputs (e.g., sub0.col0), which may interact unexpectedly with outer query columns.
• WHERE Clause vs. JOIN Conditions: The placement of t1.c0 in the WHERE clause versus the SELECT list affects result set filtering.

Possible Causes: Join Reordering, Column Visibility, and Predicate Pushdown Optimizations

1. NATURAL LEFT JOIN in View Definition Causing Column Conflicts

The view v0 is defined as SELECT 'a' FROM t0 NATURAL LEFT JOIN t1. The NATURAL LEFT JOIN implicitly joins t0 and t1 on columns with matching names (c0, c1). However, t0 initially contains no rows when v0 is created. After inserting a row into t0, the view’s NATURAL LEFT JOIN attempts to match t0.c0 and t1.c0, but since t0.c0 is NULL (not explicitly inserted), the join produces a row with t1.c0 = 1 due to the LEFT JOIN logic. This creates ambiguity in column references when v0 is used in larger queries, as the implicit join columns may conflict with outer query columns.

2. RIGHT JOIN Alters Column Precedence and Nullability

The RIGHT JOIN between t1 and sub1 ensures all rows from sub1 are retained, but t1 columns may contain NULLs if there’s no match. In the first query, t1.c0 is 1 because the RIGHT JOIN’s ON true clause preserves all rows. However, the WHERE clause in the second query filters on t1.c0, which may evaluate to NULL in some contexts due to join reordering or predicate pushdown optimizations. If the optimizer incorrectly pushes the WHERE condition into the RIGHT JOIN, it could filter out rows prematurely.

3. Optimizer Bug in Join Ordering or Predicate Handling

SQLite’s query optimizer may reorder joins or push predicates into subqueries to improve performance. In versions prior to commit a350ea7c6b89725b, the optimizer might have mishandled the interaction between the RIGHT JOIN, INNER JOIN, and WHERE clause, leading to incorrect NULL handling or column visibility. For example, if the WHERE clause is applied before the RIGHT JOIN completes, t1.c0 could appear NULL in the intermediate result set, causing the row to be filtered out.

Troubleshooting Steps, Solutions & Fixes: Diagnosing Join Interactions and Applying Patches

Step 1: Analyze the View Output and Implicit Join Conditions

Begin by isolating the output of the view v0 after the INSERT operation:

INSERT INTO t0 (c1) VALUES (0);  
SELECT * FROM v0;  

The NATURAL LEFT JOIN between t0 and t1 will attempt to match on c0 and c1. Since t0.c0 is NULL (not set during INSERT) and t0.c1 = 0, and t1 has c0 = 1, c1 = '0', the join produces one row where all columns from t1 are included (due to LEFT JOIN). However, the view explicitly selects 'a' as its output, masking the underlying column values. This design may cause confusion when v0 is joined with other tables, as the original columns from t0 and t1 are not directly accessible.

Step 2: Simplify the Query to Isolate Join Effects

Break down the original query into smaller components to observe how each join contributes to the result set:

Component 1: Evaluate the INNER JOIN between v0 and sub0

SELECT * FROM v0  
INNER JOIN (SELECT '?' AS col0 FROM v0) AS sub0 ON (1|sub0.col0);  

The INNER JOIN condition (1|sub0.col0) evaluates to 1|'?'. Since '?' is a string, SQLite attempts to cast it to an integer, resulting in 0. Thus, 1|0 = 1, which is true, so the join produces one row.

Component 2: Evaluate the RIGHT JOIN between t1 and sub1

SELECT * FROM t1  
RIGHT JOIN (SELECT 0 AS col0 FROM v0) AS sub1 ON true;  

The RIGHT JOIN retains all rows from sub1 (one row with col0 = 0) and includes matching rows from t1. Since there’s no explicit join condition, t1 is cross-joined with sub1, producing one row with t1.c0 = 1 and sub1.col0 = 0.

Combined Query Without WHERE Clause
When combining these components with a comma join, the result is a Cartesian product between the INNER JOIN result and the RIGHT JOIN result, yielding one row with t1.c0 = 1.

Adding the WHERE Clause
The WHERE clause WHERE t1.c0 filters out rows where t1.c0 is 0 or NULL. However, if the optimizer incorrectly handles column visibility during join reordering, t1.c0 may resolve to NULL in the filtered result set.

Step 3: Upgrade to SQLite Version with Commit a350ea7c6b89725b

The root cause was an optimizer bug in join reordering or predicate pushdown that was fixed in the specified commit. To resolve the issue:

1. Download the latest SQLite trunk version after Dec 9th, 2024.
2. Recompile your application with the updated SQLite library.
3. Verify the fix by rerunning the original queries.

Step 4: Rewrite the Query to Avoid Ambiguous Joins

If upgrading is not feasible, restructure the query to use explicit JOIN syntax and avoid NATURAL joins:

SELECT t1.c0  
FROM v0  
INNER JOIN (SELECT '?' AS col0 FROM v0) AS sub0 ON (1|CAST(sub0.col0 AS INTEGER))  
CROSS JOIN t1  
RIGHT JOIN (SELECT 0 AS col0 FROM v0) AS sub1 ON true  
WHERE t1.c0 IS NOT NULL;  

This rewrite eliminates implicit column matching and ensures sub0.col0 is explicitly cast to an integer.

Step 5: Use EXPLAIN to Compare Query Plans Across Versions

Run EXPLAIN on both queries in SQLite 3.40 and the broken trunk version to identify differences in join order or predicate placement:

EXPLAIN QUERY PLAN  
SELECT t1.c0 FROM ... ;  

Look for operations like SCAN, SEARCH, or USE TEMP B-TREE that indicate join reordering or temporary result sets. In the broken version, the WHERE clause might be applied earlier in the plan, leading to premature filtering.

Final Solution Summary

The unexpected empty result is caused by an optimizer bug in specific SQLite versions, resolved in commit a350ea7c6b89725b. To fix the issue:

• Upgrade SQLite to a version containing the fix.
• Rewrite the query to use explicit joins and casts.
• Use EXPLAIN to diagnose join order issues in complex queries.

By understanding the interaction between joins, views, and subqueries, developers can avoid similar issues and ensure consistent results across SQLite versions.