Unexpected Row Count with WHERE Clause in RIGHT JOIN Query

Issue Overview: RIGHT JOIN with WHERE Clause Yields More Rows Than Expected

The core issue revolves around a SQLite query that uses a RIGHT OUTER JOIN combined with an INNER JOIN in the ON clause. The query returns an empty result set when executed without a WHERE clause but produces a non-empty result (specifically, a row containing |1|||) when a WHERE c1 IS NULL condition is added. This behavior is counterintuitive, as WHERE clauses typically filter rows, not add them.

The schema involved includes three entities:

• Table t0 with columns c0, c1, c2 (empty, as no data is inserted).
• Table t1 with a single column c0 containing the value '1'.
• View v0 defined as SELECT 0, which implicitly creates a single-row result with a column (unnamed) containing 0.

The first query:

SELECT * FROM v0 RIGHT OUTER JOIN t1 ON t1.c0 INNER JOIN t0 ON t0.c2;  

returns an empty result. The second query:

SELECT * FROM v0 RIGHT OUTER JOIN t1 ON t1.c0 INNER JOIN t0 ON t0.c2 WHERE c1 IS NULL;  

returns |1|||.

The inconsistency arises from the interaction between RIGHT OUTER JOIN, INNER JOIN within the ON clause, and the WHERE clause. The RIGHT OUTER JOIN prioritizes rows from t1 (the right table), but the INNER JOIN within the ON clause introduces dependencies on t0, which is empty. The WHERE clause alters the evaluation logic, leading to unexpected row retention.

Possible Causes: Ambiguous JOIN Logic and NULL Handling

1. JOIN Precedence and Associativity

SQLite processes JOIN operations from left to right unless parentheses dictate otherwise. In the query, v0 RIGHT OUTER JOIN t1 ON ... is executed first, with the ON clause containing an INNER JOIN t0 ON t0.c2. This structure creates ambiguity:

• Is the INNER JOIN t0 part of the ON condition for the RIGHT OUTER JOIN?
• Or is it a separate join operation?

The ON clause in a JOIN typically defines conditions for combining rows between the immediate left and right tables. However, embedding an INNER JOIN within the ON clause of a RIGHT OUTER JOIN creates a nested join condition. This can lead to unintended cross-table dependencies.

2. NULL Propagation in OUTER JOINs

The RIGHT OUTER JOIN preserves all rows from t1, even if no matching rows exist in v0. Since t0 is empty, the INNER JOIN t0 ON t0.c2 within the ON clause forces the entire join to depend on non-existent data. This results in implicit NULL assignments for columns from v0 and t0.

The WHERE c1 IS NULL clause then filters rows based on the NULL state of c1, which originates from t0.c1 (since t0 is empty). However, the interaction between RIGHT OUTER JOIN and WHERE can override the expected NULL handling logic.

3. Column Ambiguity in Star Queries

The use of SELECT * introduces ambiguity when multiple tables/views in the FROM clause have columns with the same name. In this case, v0 has an unnamed column (defaults to '' in some clients), t1 has c0, and t0 has c0, c1, c2. The lack of explicit column aliasing may cause the query engine to misattribute column references in the WHERE clause.

Troubleshooting Steps, Solutions & Fixes

Step 1: Clarify JOIN Logic with Explicit Parentheses

Rewrite the query to eliminate ambiguity in join order and associativity. Use parentheses to isolate join groups:

SELECT *  
FROM v0  
RIGHT OUTER JOIN (  
  t1 INNER JOIN t0 ON t0.c2  
) ON t1.c0;  

This explicitly groups t1 INNER JOIN t0 as a sub-join, separating it from the RIGHT OUTER JOIN with v0.

Result Analysis:

• If t0 is empty, the INNER JOIN t0 produces no rows, making the entire sub-join empty.
• The RIGHT OUTER JOIN with v0 would then retain rows from v0 (if any) and NULL for t1/t0 columns.

This structure aligns with the original query’s intent and exposes whether the nested INNER JOIN is causing unexpected row exclusions.

Step 2: Replace Implicit WHERE Conditions with Explicit Column References

The WHERE c1 IS NULL clause may inadvertently reference the wrong c1 column due to name collisions. Use explicit table aliases:

SELECT *  
FROM v0  
RIGHT OUTER JOIN t1 ON t1.c0  
INNER JOIN t0 ON t0.c2  
WHERE t0.c1 IS NULL;  

Key Changes:

• Added explicit t0.c1 reference in the WHERE clause.
• Separated INNER JOIN t0 from the ON clause of the RIGHT OUTER JOIN.

Outcome:

• If t0 is empty, t0.c1 IS NULL evaluates to NULL, which the WHERE clause treats as FALSE, resulting in no rows.
• This contradicts the original behavior, suggesting that the original query’s WHERE clause was referencing a different c1 (possibly from v0).

Step 3: Analyze the View’s Structure

The view v0 is defined as SELECT 0, which creates a single row with an unnamed column. In SQLite, this column is assigned a default name (often ''). When joined with t1, the lack of explicit column names causes ambiguity.

Rewrite v0 with an explicit column alias:

CREATE VIEW v0 AS SELECT 0 AS v0_c;  

Then adjust the query:

SELECT *  
FROM v0  
RIGHT OUTER JOIN t1 ON t1.c0  
INNER JOIN t0 ON t0.c2  
WHERE t0.c1 IS NULL;  

Impact:

• Eliminates column name ambiguity.
• Exposes whether the WHERE clause was erroneously referencing v0’s implicit column instead of t0.c1.

Step 4: Test with Simplified Data

Populate t0 with sample data to observe how joins behave:

INSERT INTO t0 VALUES (NULL, NULL, NULL);  

Re-run the original queries.

Expected Behavior:

• The INNER JOIN t0 ON t0.c2 now succeeds (since t0 has a row).
• The RIGHT OUTER JOIN retains the row from t1, combined with v0 and t0.

Observation:

• If the WHERE c1 IS NULL clause now filters rows based on t0.c1, the result set will depend on the actual data in t0.

Step 5: Use EXPLAIN to Inspect Query Execution Plans

Run EXPLAIN on both queries to compare their execution plans:

EXPLAIN SELECT * FROM v0 RIGHT OUTER JOIN t1 ON t1.c0 INNER JOIN t0 ON t0.c2;  
EXPLAIN SELECT * FROM v0 RIGHT OUTER JOIN t1 ON t1.c0 INNER JOIN t0 ON t0.c2 WHERE c1 IS NULL;  

Focus Areas:

• Join order and algorithm (e.g., nested loop, hash join).
• Filter placement (whether the WHERE clause is applied before or after joins).

Interpretation:

• If the WHERE clause is pushed down into the join processing, it may alter NULL handling logic.
• Differences in bytecode steps between the two queries reveal how SQLite optimizes or misoptimizes the filter condition.

Step 6: Avoid Mixing JOIN Types in ON Clauses

Refactor the query to use explicit LEFT/RIGHT joins instead of embedding an INNER JOIN within an ON clause:

SELECT *  
FROM v0  
RIGHT OUTER JOIN t1 ON t1.c0  
INNER JOIN t0 ON t0.c2;  

Rationale:

• Separates the RIGHT OUTER JOIN from the INNER JOIN, making the logical flow clearer.
• Exposes whether the nested INNER JOIN was causing unintended cross-join behavior.

Step 7: Upgrade to SQLite 3.39.0+

The original discussion notes the issue was fixed in trunk (later releases). Confirm the behavior persists in the current environment. If so, upgrading SQLite may resolve the bug.

Verification:

• Test both queries post-upgrade.
• If the anomaly disappears, the root cause was a query optimizer bug in prior versions.

This guide systematically addresses the ambiguities and edge cases that lead to the unexpected row count discrepancy. By isolating join logic, enforcing explicit column references, and validating against simplified data, developers can diagnose similar issues arising from complex joins and NULL handling in SQLite.