Unexpected RIGHT JOIN Results Due to WHERE Clause and NOTNULL Conditions

Issue Overview: RIGHT JOIN Produces Incorrect Rows with WHERE Clause Involving NOTNULL

The core issue revolves around unexpected rows appearing in the result set of a RIGHT JOIN query when a WHERE clause involving NOTNULL conditions is applied. This behavior contradicts logical expectations derived from the structure of the underlying tables and the relationships defined in the JOIN clause. The problem manifests in scenarios where:

1. A RIGHT JOIN is performed between two tables (including virtual tables like rtree).
2. The WHERE clause contains a condition that evaluates the nullness of a column from the left table (e.g., (rt1.c1 NOTNULL) == rt0.c0).
3. The query planner applies optimizations that incorrectly prioritize WHERE clause constraints over JOIN conditions, leading to invalid row combinations.

The discrepancy becomes evident when comparing the results of three related queries:

• A RIGHT JOIN without a WHERE clause returns rows that appear valid at first glance.
• Adding a WHERE clause with NOTNULL logic should logically filter out certain rows, but instead produces counterintuitive results (e.g., a row with NULL values from the left table).
• Reversing the WHERE condition (e.g., != instead of ==) unexpectedly restores a row that should have been excluded.

This behavior is not limited to rtree virtual tables. It also occurs with standard tables, as demonstrated in the PostgreSQL comparison provided. The root cause lies in how SQLite’s query planner handles WHERE clause constraints in the presence of RIGHT JOIN optimizations, particularly when nullness checks are involved.

Possible Causes: Query Planner Optimization Conflicts with Nullness Checks

1. Incorrect Optimization for WHERE Clause Constraints on RIGHT JOINs

SQLite employs optimizations to accelerate queries by leveraging indexes and pushing WHERE clause constraints into the query execution plan. In RIGHT JOIN scenarios, an optimization allows constraints on the right table to be applied early, reducing the intermediate result set size. However, this optimization fails when the WHERE clause includes conditions that test the nullness of columns from the left table (e.g., (rt1.c1 NOTNULL)). The planner incorrectly assumes these conditions can be resolved using indexes on the right table, leading to invalid row combinations.

2. Interaction Between NULL Semantics and Virtual Tables

Virtual tables like rtree have specialized storage and indexing mechanisms. When inserting values like x'' (an empty blob) or 1 into rtree columns, SQLite may implicitly coerce these values into bounding box coordinates (e.g., 0.0 for empty blobs). This coercion interacts unpredictably with NOTNULL checks. For example, rt1.c1 NOTNULL evaluates to false because rt1.c1 is 0.0 (a valid non-null value), but the query planner misinterprets this due to type conversion nuances.

3. Index Creation Timing Affecting Query Plans

The PostgreSQL example highlights how creating an index mid-session alters query results. In SQLite, creating an index after initial data insertion changes the query planner’s optimization choices. The presence of an index on a BOOLEAN column (as in t1(a)) causes the planner to prioritize index scans over full table scans, exacerbating the conflict between WHERE clause constraints and RIGHT JOIN logic.

4. Misplacement of Constraints in WHERE vs. ON Clauses

The SQL standard distinguishes between ON clause conditions (applied during the JOIN) and WHERE clause conditions (applied after the JOIN). Placing a nullness check in the WHERE clause instead of the ON clause allows the query planner to apply it post-join, which may bypass optimizations designed to handle RIGHT JOIN semantics correctly. This misplacement is a common source of errors in queries involving outer joins.

Troubleshooting Steps, Solutions & Fixes: Resolving RIGHT JOIN Anomalies

Step 1: Verify SQLite Version and Apply Patches

The issue was addressed in SQLite version 3.39.0 with commit f28de5b7. Confirm your SQLite version using:

SELECT sqlite_version();

If the version is older than 3.39.0, upgrade to a patched version. The fix disables the problematic optimization when NOTNULL conditions are present in the WHERE clause.

Step 2: Analyze Query Plans with EXPLAIN

Use EXPLAIN or EXPLAIN QUERY PLAN to inspect how SQLite executes the query:

EXPLAIN QUERY PLAN
SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0 WHERE ((rt1.c1) NOTNULL)==rt0.c0;

Look for indications that the query planner is pushing WHERE clause constraints into the JOIN phase. Pre-patch versions may show SEARCH or SCAN operations on the right table (rt0) using an index, which is invalid when left-table nullness checks are involved.

Step 3: Reposition Constraints to the ON Clause

Move nullness checks from the WHERE clause to the ON clause of the RIGHT JOIN:

SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0 AND ((rt1.c1) NOTNULL)==rt0.c0;

This ensures the condition is evaluated during the join process, aligning with the optimizer’s expectations. Note that this changes the semantics of the query slightly, as ON clause conditions filter rows before the outer join is finalized.

Step 4: Avoid Indexes on Boolean Columns Temporarily

If the issue persists after upgrading, consider dropping indexes on BOOLEAN columns during troubleshooting:

DROP INDEX t1a;  -- From the PostgreSQL example

Re-run the query to see if the results stabilize. Recreate the index afterward if needed.

Step 5: Use COALESCE or Explicit Null Checks

Explicitly handle NULL values in the WHERE clause to bypass ambiguous optimizations:

SELECT * FROM rt1 RIGHT OUTER JOIN rt0 ON rt1.c0 
WHERE COALESCE(rt1.c1, 0) = rt0.c0;

This forces the query planner to evaluate the condition without relying on implicit nullness assumptions.

Step 6: Test with Standard Tables to Isolate Virtual Table Issues

Recreate the problem using standard tables instead of rtree virtual tables:

CREATE TABLE t1(c0 BLOB, c1 REAL, c2 REAL);
CREATE TABLE t2(c0 INTEGER, c1 REAL, c2 REAL);
INSERT INTO t1(c0) VALUES (x'');
INSERT INTO t2(c0) VALUES (1);

Run the same RIGHT JOIN queries to determine if the issue is specific to virtual tables or a general SQLite behavior.

Step 7: Compare with Other Databases for Semantic Validation

Execute the query in PostgreSQL or MySQL to validate expected results:

-- PostgreSQL
SELECT * FROM t2 RIGHT JOIN t1 ON t2.c0 WHERE (t2.c1 IS NOT NULL) = t1.c0;

Use cross-database comparisons to confirm whether the issue is SQLite-specific or a broader SQL semantic misunderstanding.

Step 8: Disable Specific Optimizations with PRAGMA Statements

Temporarily disable SQLite’s query planner optimizations using pragmas:

PRAGMA optimizer_flags = 0x00000000;  -- Disable all optimizations

Re-run the query to see if results improve. Gradually re-enable optimizations with:

PRAGMA optimizer_flags = DEFAULT;

Step 9: Rewrite RIGHT JOIN as LEFT JOIN

Convert the RIGHT JOIN to a LEFT JOIN by swapping table positions:

SELECT * FROM rt0 LEFT OUTER JOIN rt1 ON rt0.c0 = rt1.c0 
WHERE ((rt1.c1) NOTNULL)==rt0.c0;

This may yield a more reliable query plan, as LEFT JOIN optimizations are more thoroughly tested in SQLite.

Step 10: Report Edge Cases to SQLite Maintainers

If the issue persists in patched versions, submit a detailed bug report with:

• Full schema definitions
• Query plans
• Version information
• Cross-database comparison results

By systematically applying these steps, developers can resolve unexpected RIGHT JOIN results caused by WHERE clause nullness checks, ensuring queries behave consistently across SQLite versions and database environments.