Unexpected Empty Result with RIGHT JOIN and NOT NULL Column in WHERE Clause

Issue Overview: Complex Join Logic Interacting with NOT NULL Constraints

The core problem revolves around a discrepancy between expected and actual query results when combining multiple joins (LEFT OUTER JOIN, INNER JOIN, RIGHT OUTER JOIN) with NOT NULL constraints and IS NULL checks in SQLite. A user reported that two logically similar SELECT statements produced different outputs despite expectations of equivalence. The first query returns a row containing the value "y" from table t1, while the second query (which adds a WHERE (((t2.c1) ISNULL)) clause) returns an empty result. A third query demonstrates that (((t2.c1) ISNULL) IS TRUE) evaluates to 1 (TRUE) for the same join configuration, creating an apparent contradiction.

Key components of the issue include:

1. Schema Design:
   • t2 has a c1 column declared as NOT NULL.
   • All other tables (t0, t1, t3) have no explicit NOT NULL constraints.
2. Join Configuration:
   • The query uses a mix of LEFT OUTER JOIN, INNER JOIN, and RIGHT OUTER JOIN operations.
   • The RIGHT OUTER JOIN between t1 and t2 introduces ambiguity about the nullability of t2 columns despite t2.c1 being NOT NULL.
3. Optimizer Behavior:
   • SQLite attempts to simplify t2.c1 IS NULL to FALSE due to the NOT NULL constraint on t2.c1.
   • This optimization interacts incorrectly with join reordering and outer join flattening, leading to premature elimination of rows.

The contradiction arises because the third query confirms that t2.c1 IS NULL evaluates to TRUE for the joined result, yet the WHERE clause in the second query filters out all rows. This indicates a failure in SQLite’s query planner to account for the nullable side of a RIGHT JOIN when applying NOT NULL optimizations.

Possible Causes: Join Optimization Conflicts with Constraint Inference

1. Incorrect IS NULL-to-FALSE Optimization

SQLite employs an optimization that converts expressions like x IS NULL to FALSE if x is a NOT NULL column. This works under the assumption that x cannot contain NULLs. However, when x resides on the nullable side of an outer join (e.g., the right side of a RIGHT JOIN), the column may effectively become nullable due to the join’s semantics. The optimizer fails to recognize this interaction, leading to incorrect query results.

2. Join Reordering and Flattening

SQLite’s query planner may convert a LEFT JOIN to an INNER JOIN if it determines that the WHERE clause or ON conditions make the outer join unnecessary. In this case, the RIGHT JOIN between t1 and t2 complicates the nullability analysis. The flattening of joins alters the perceived nullability of t2.c1, causing the optimizer to misapply the NOT NULL constraint.

3. Interaction Between Multiple Join Types

The combination of LEFT OUTER JOIN, INNER JOIN, and RIGHT OUTER JOIN creates a complex dependency chain:

• The LEFT OUTER JOIN between t3 and t2 preserves all rows from t3.
• The INNER JOIN between t2 and t0 requires matching rows in t0.
• The RIGHT OUTER JOIN between t1 and t2 preserves all rows from t1, potentially introducing NULLs in t2 columns.

The optimizer struggles to reconcile these conflicting join types with the NOT NULL constraint on t2.c1, leading to incorrect nullability assumptions.

4. Right Join Implementation Edge Cases

The RIGHT JOIN feature is relatively new in SQLite (as of version 3.39.0), and its integration with existing optimizations may not account for all edge cases. The bug fix mentioned in the discussion (check-in b1be2259e2e08ec2) specifically addresses scenarios where RIGHT JOIN nullability conflicts with NOT NULL constraint optimizations.

Troubleshooting Steps, Solutions & Fixes: Resolving Join-Induced Constraint Violations

Step 1: Diagnose Optimization Misapplication

Action: Use EXPLAIN or EXPLAIN QUERY PLAN to analyze how SQLite processes the joins and WHERE clause:

EXPLAIN QUERY PLAN
SELECT * FROM t3 LEFT OUTER JOIN t2 INNER JOIN t0 ON t2.c1 RIGHT OUTER JOIN t1 ON t2.c0;

Look for indications of join flattening or conversions (e.g., LEFT JOIN to INNER JOIN). If the RIGHT JOIN is improperly flattened, it may disable nullability tracking for t2.c1.

Outcome: The query plan may reveal that SQLite reordered joins or applied optimizations that invalidate the NOT NULL constraint logic.

Step 2: Disable Problematic Optimizations (Temporary Workaround)

Action: Use pragmas or query hints to disable specific optimizations. For example:

PRAGMA optimize=OFF;

Limitation: This is a blunt instrument and may degrade performance. Use only for testing.

Alternative: Rewrite the query to prevent join flattening. Use subqueries or CROSS JOIN syntax to enforce join order:

SELECT * FROM t3
LEFT OUTER JOIN (
  SELECT * FROM t2 
  INNER JOIN t0 ON t2.c1 
  RIGHT OUTER JOIN t1 ON t2.c0
) AS subq;

Step 3: Update to a Fixed SQLite Version

Action: Upgrade to SQLite 3.39.0 or later with the check-in b1be2259e2e08ec2 applied. Verify the version with:

SELECT sqlite_version();

Outcome: The corrected version disables the IS NULL-to-FALSE optimization only when columns are on the nullable side of an outer join, resolving the contradiction.

Step 4: Rewrite Query to Avoid Ambiguous Joins

Action: Simplify the join structure to minimize optimizer conflicts:

SELECT * FROM t1
RIGHT OUTER JOIN t2 ON t2.c0 = t1.c0
INNER JOIN t0 ON t2.c1 = t0.c0
LEFT OUTER JOIN t3 ON 1=1;

Rationale: Explicitly defining join conditions and order reduces ambiguity for the query planner.

Step 5: Use COALESCE or Explicit NULL Checks

Action: Replace implicit NULL checks with explicit logic:

SELECT * FROM t3 
LEFT OUTER JOIN t2 INNER JOIN t0 ON t2.c1 RIGHT OUTER JOIN t1 ON t2.c0
WHERE COALESCE(t2.c1, 'NULL') = 'NULL';

Rationale: Bypasses the faulty optimization by making the NULL check explicit.

Step 6: Validate NOT NULL Constraint Interactions

Action: Test the nullability of columns in isolation:

SELECT t2.c1 IS NULL FROM t2 RIGHT OUTER JOIN t1 ON t2.c0;

Outcome: If this returns 1 (TRUE) for some rows, it confirms that the RIGHT JOIN introduces NULLs despite t2.c1 being NOT NULL, justifying the need for cautious optimization.

Step 7: Leverage CHECK Constraints for Clarity

Action: Add CHECK constraints to document assumptions:

CREATE TABLE t2(c0, c1 NOT NULL CHECK (c1 IS NOT NULL));

Rationale: While not directly fixing the issue, this clarifies design intent and may help future query analysis.

Step 8: Monitor Future SQLite Releases

Action: Track SQLite’s changelog for fixes related to RIGHT JOIN and NOT NULL optimizations. Subscribe to mailing lists or forums for updates.

Final Solution Summary

The root cause lies in SQLite’s premature optimization of NOT NULL columns within complex join structures. The definitive fix involves updating to a patched SQLite version. For immediate workarounds, rewrite queries to avoid ambiguous join flattening or use explicit NULL checks. Always validate join nullability when mixing outer joins with NOT NULL constraints.