Unexpected RIGHT JOIN Results with Partial Indexes in SQLite

Interplay Between RIGHT JOINs and Partial Indexes Leading to Incorrect Evaluations

Issue Overview
The core problem arises when combining RIGHT OUTER JOIN operations with tables that have partial indexes (indexes with WHERE clauses). This interaction can lead to unexpected query results due to how SQLite’s query planner handles the partial index during join processing. Consider the following schema and queries:

CREATE TABLE t1 (c0, c1);
INSERT INTO t1(c0) VALUES (2);  -- t1.c0 = 2
CREATE TABLE t2 (c0);
CREATE INDEX i0 ON t2 (c0) WHERE c0;  -- Partial index: only rows where t2.c0 IS TRUE
CREATE TABLE t3 (c0);
INSERT INTO t3 VALUES (1);

First Query

SELECT * FROM t2 RIGHT OUTER JOIN t3 ON t3.c0 
LEFT OUTER JOIN t1 ON t2.c0 WHERE t1.c0;
-- Result: |1|2|

Second Query

SELECT (t1.c0 IS TRUE) FROM t2 RIGHT OUTER JOIN t3 ON t3.c0 
LEFT OUTER JOIN t1 ON t2.c0;
-- Result: 0 (FALSE)

Observations

1. The first query returns a row where t1.c0 = 2, implying t1.c0 is considered "truthy" in the WHERE clause.
2. The second query evaluates t1.c0 IS TRUE as 0 (FALSE), even though t1.c0 = 2 is non-zero.

This contradiction stems from SQLite’s handling of partial indexes in RIGHT JOIN contexts. The partial index i0 on t2 filters out rows where t2.c0 is 0 or NULL. When combined with a RIGHT JOIN, the optimizer incorrectly assumes that the partial index covers all valid rows, leading to misaligned row combinations during joins. The RIGHT JOIN introduces NULL placeholders for unmatched rows, which then propagate through subsequent LEFT JOIN operations and truthiness evaluations.

Mechanisms Behind the Partial Index and RIGHT JOIN Misalignment

Possible Causes

1. Partial Index Scope in RIGHT JOIN Execution
   Partial indexes exclude rows that do not satisfy their WHERE clause. When a RIGHT JOIN is executed, SQLite’s query planner prioritizes the left table (in this case, t2) for index scans. However, if the left table uses a partial index, the planner assumes that the index contains all relevant rows for the join. This assumption fails in RIGHT JOIN scenarios because unmatched rows from the left table must still generate NULL-padded rows for the right table. The partial index’s filtering inadvertently excludes these rows, causing the RIGHT JOIN to produce incomplete or incorrect results.

2. Truthiness vs. Boolean Evaluations
   SQLite evaluates truthiness differently in WHERE clauses versus explicit boolean checks. The WHERE t1.c0 clause interprets t1.c0 as "truthy" (non-zero and non-NULL), allowing t1.c0 = 2 to pass. In contrast, t1.c0 IS TRUE explicitly checks for the boolean value 1, which 2 does not equal. This discrepancy is exacerbated by the partial index’s impact on row visibility during joins.

3. Query Planner’s Join Order Optimization
   The order in which joins are processed affects how partial indexes are utilized. In the example, the RIGHT JOIN between t2 and t3 is processed before the LEFT JOIN with t1. The partial index i0 on t2 restricts the rows available for the RIGHT JOIN, altering the input to subsequent joins. This creates a chain reaction where t1 is joined against an already-filtered dataset, leading to mismatched evaluations.

4. NULL Propagation in Outer Joins
   The RIGHT JOIN between t2 and t3 produces NULL values for t2.c0 when no match exists. These NULL values are then passed to the LEFT JOIN with t1, which attempts to match NULL against t1’s columns. Since NULL comparisons are inherently ambiguous, this further distorts the final result set.

Correcting Join Behavior with Partial Indexes in SQLite

Troubleshooting Steps, Solutions & Fixes

1. Diagnose Partial Index Usage in Query Plans

Use EXPLAIN QUERY PLAN to determine if the partial index is being used inappropriately:

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

Look for lines like USING INDEX i0 in the output. If the partial index is used for the RIGHT JOIN, it indicates the root cause.

Fix: Disable partial index usage for the problematic join:

SELECT * FROM t2 RIGHT OUTER JOIN t3 ON t3.c0 
LEFT OUTER JOIN t1 INDEXED BY (no_partial_index) ON t2.c0 
WHERE t1.c0;

2. Avoid RIGHT JOIN with Partial Indexes

Rewrite the query using LEFT JOIN instead, which is more predictable in SQLite:

SELECT * FROM t3 LEFT OUTER JOIN t2 ON t3.c0 
LEFT OUTER JOIN t1 ON t2.c0 
WHERE t1.c0;

This eliminates the RIGHT JOIN and aligns the join order with SQLite’s optimization strengths.

3. Modify the Partial Index Condition

Adjust the partial index to include all rows or use a different filter:

DROP INDEX i0;
CREATE INDEX i0_new ON t2 (c0);  -- Full index without WHERE clause

If retaining the filter is necessary, ensure it matches the join conditions:

CREATE INDEX i0_new ON t2 (c0) WHERE c0 IS NOT NULL;

4. Upgrade to SQLite 3.39.0+ with the Official Fix

The bug was resolved in check-in 615c0026119f7870. Ensure your SQLite version includes this fix:

sqlite3 --version  # Should be 3.39.0 or later

5. Explicitly Handle Truthiness in Queries

Replace implicit truthiness checks with explicit comparisons:

-- Original WHERE clause
WHERE t1.c0;

-- Revised to avoid ambiguity
WHERE t1.c0 IS NOT NULL AND t1.c0 <> 0;

For boolean evaluations, use strict checks:

-- Original
SELECT (t1.c0 IS TRUE) FROM ...;

-- Revised to match SQLite's TRUE (1)
SELECT (t1.c0 = 1) FROM ...;

6. Use COALESCE or CASE to Normalize Values

Normalize t1.c0 to handle non-boolean integers:

SELECT (CASE WHEN t1.c0 THEN 1 ELSE 0 END) FROM ...;

This maps any non-zero value to 1, aligning with SQLite’s TRUE semantics.

7. Validate Join Outputs with Subqueries

Isolate the RIGHT JOIN logic in a subquery to debug intermediate results:

SELECT * FROM (
  SELECT t2.c0 AS t2c0, t3.c0 AS t3c0 
  FROM t2 RIGHT OUTER JOIN t3 ON t3.c0
) AS sub
LEFT OUTER JOIN t1 ON sub.t2c0;

This reveals whether the RIGHT JOIN is producing the expected NULL values before the LEFT JOIN with t1.

8. Disable Partial Indexes Temporarily

Force SQLite to ignore partial indexes during testing:

PRAGMA ignore_check_constraints = ON;  -- Not recommended for production

Note: This pragma disables all constraint checks, not just partial indexes. Use with caution.

9. Rebuild the Database Schema

If the issue persists, export and reimport the schema without partial indexes:

sqlite3 original.db .schema > schema.sql
sed 's/CREATE INDEX i0 .*//' schema.sql > new_schema.sql
sqlite3 new.db < new_schema.sql

10. Monitor SQLite’s Query Optimizer Changes

Stay informed about query planner updates in newer SQLite versions. Subscribe to the SQLite changelog and test joins after upgrades.

By addressing the interaction between partial indexes and RIGHT JOIN execution order, developers can resolve inconsistencies in query results. The solutions range from query rewrites and index adjustments to upgrading SQLite itself, ensuring alignment between expected and actual query behavior.