Unexpected Rows in RIGHT JOIN with Boolean Conditions in SQLite

Understanding RIGHT JOIN Boolean Filter Mismatches in Complex Queries

RIGHT JOIN Returns Rows Despite Boolean WHERE Clause Filtering

The core issue involves a SQLite query that uses a RIGHT OUTER JOIN (or RIGHT JOIN) combined with a WHERE clause filtering on a boolean column. The problem arises when the WHERE condition (e.g., t0.c1 IS TRUE) evaluates to FALSE for all rows, yet the query returns a row with NULL values instead of an empty result. This contradicts logical expectations because the WHERE clause should filter out all rows where the boolean condition is not met.

The scenario involves three tables: t0, t1, and t2. The t0 table has a column c1 added via ALTER TABLE, which introduces a boolean-like value. The t1 table contains a NULL value in its sole column. The t2 table is empty or irrelevant to the final result in this specific case. The critical query structure is:

SELECT * 
FROM t0 
CROSS JOIN t2 ON t0.c1 IN (1) 
RIGHT OUTER JOIN t1 
WHERE (t0.c1 IS TRUE);

Despite the WHERE clause explicitly filtering for t0.c1 IS TRUE, the query returns a row with NULL values. A simplified version of the problem, as restated by SQLite’s maintainer, involves similar table structures and a RIGHT JOIN that erroneously includes a row where the boolean condition in the WHERE clause should exclude it. The root cause is tied to the interaction between RIGHT JOIN semantics, boolean evaluations, and the query optimizer’s handling of redundant constraints in the ON and WHERE clauses.

This issue is specific to SQLite versions that include the unreleased RIGHT JOIN implementation (prior to official support). Released versions of SQLite do not include RIGHT JOIN natively and instead require rewriting such joins using LEFT JOIN. The bug was resolved in a specific check-in (cab9b4cccd13bf0a) but remains a critical case study for understanding edge cases in join logic and boolean evaluations.

Incorrect Boolean Handling in RIGHT JOIN ON Clauses and WHERE Filters

The unexpected result stems from three interrelated factors:

1. Ambiguous Boolean Type Affinity in Altered Tables
   The t0.c1 column is added via ALTER TABLE, which in SQLite does not enforce strict type affinity. When a column is added with ALTER TABLE, its type affinity is determined by the declared type in the ADD COLUMN clause. In the original example, ALTER TABLE t0 ADD c1 c2 declares c1 with a type name c2, which SQLite treats as NUMERIC affinity. However, if the column were created with BOOLEAN affinity (e.g., ALTER TABLE t0 ADD c1 BOOLEAN), SQLite would more strictly interpret TRUE/FALSE literals. The mismatch between declared type affinity and boolean comparisons introduces ambiguity in how t0.c1 IS TRUE is evaluated, especially when combined with RIGHT JOIN.

2. RIGHT JOIN and NULL Propagation Logic
   A RIGHT JOIN returns all rows from the right table (t1 in this case), even if there are no matching rows in the left table (t0). When no match exists, columns from the left table are filled with NULL. The WHERE clause t0.c1 IS TRUE attempts to filter out rows where t0.c1 is not TRUE. However, in the case of a RIGHT JOIN, if the left table (t0) has no matching rows, t0.c1 is NULL, and NULL IS TRUE evaluates to FALSE. The query should thus return no rows. The bug occurs because the query optimizer incorrectly applies the WHERE clause filter during the join process, failing to account for the NULL values introduced by the RIGHT JOIN.

3. Redundant Constraints in ON and WHERE Clauses
   The original query includes a CROSS JOIN t2 ON t0.c1 IN (1) clause, which introduces a redundant constraint. The ON t0.c1 IN (1) condition is logically similar to t0.c1 = 1, but when combined with the RIGHT JOIN, the optimizer may incorrectly merge or ignore these constraints. This redundancy confuses the query planner, leading it to miscalculate whether rows from the right table (t1) should be included even when the WHERE clause should exclude them.

Resolving RIGHT JOIN Boolean Mismatches and Applying Workarounds

Step 1: Validate the SQLite Version and RIGHT JOIN Support

Confirm whether the SQLite version includes the unreleased RIGHT JOIN implementation. Versions prior to 3.39.0 do not natively support RIGHT JOIN, and the keyword is translated into a LEFT JOIN during parsing. Execute SELECT sqlite_version(); to check the version. If the version is 3.39.0 or newer and includes the RIGHT JOIN implementation, proceed to test the query with the fix applied (check-in cab9b4cccd13bf0a or later).

Step 2: Simplify the Query to Isolate the Issue

Break down the complex join into smaller components. For example, remove the CROSS JOIN t2 and test the RIGHT JOIN in isolation:

SELECT * 
FROM t0 
RIGHT JOIN t1 ON TRUE 
WHERE t0.c1 IS TRUE;

If this simplified query still returns unexpected rows, the issue is likely due to the boolean evaluation in the WHERE clause. If it returns no rows, the problem lies in the interaction with the CROSS JOIN.

Step 3: Use Explicit Boolean Comparisons

SQLite represents TRUE as 1 and FALSE as 0. Replace IS TRUE with = 1 to avoid ambiguity:

SELECT * 
FROM t0 
RIGHT JOIN t1 ON TRUE 
WHERE t0.c1 = 1;

This forces a numeric comparison, which is less prone to misinterpretation in columns with weak type affinity.

Step 4: Rewrite RIGHT JOIN Using LEFT JOIN

Until RIGHT JOIN is officially stabilized, rewrite the query using LEFT JOIN:

SELECT * 
FROM t1 
LEFT JOIN t0 ON t0.c1 = 1 
LEFT JOIN t2 ON TRUE 
WHERE t0.c1 IS NOT NULL;

This reverses the join order and achieves the same logical result as a RIGHT JOIN.

Step 5: Avoid Mixing JOIN Types with Redundant ON Clauses

The original query includes a CROSS JOIN t2 ON t0.c1 IN (1), which is functionally a INNER JOIN with a condition. Replace this with an explicit INNER JOIN:

SELECT * 
FROM t0 
INNER JOIN t2 ON t0.c1 IN (1) 
RIGHT JOIN t1 ON TRUE 
WHERE t0.c1 IS TRUE;

This clarifies the join logic and helps the optimizer process the conditions correctly.

Step 6: Update to a Patched SQLite Version

If using a pre-release build with RIGHT JOIN support, apply the fix from check-in cab9b4cccd13bf0a or later. This resolves the optimizer’s mishandling of WHERE clauses in RIGHT JOIN queries with boolean conditions.

Step 7: Use COALESCE to Handle NULLs Explicitly

Modify the WHERE clause to account for NULL values introduced by the RIGHT JOIN:

SELECT * 
FROM t0 
RIGHT JOIN t1 ON TRUE 
WHERE COALESCE(t0.c1, 0) = 1;

This converts NULL to 0, ensuring the WHERE clause correctly filters out non-matching rows.

Step 8: Test Boolean Column Affinity

Ensure boolean columns are created with explicit affinity. For example:

CREATE TABLE t0(c0, c1 BOOLEAN);

This enforces stricter type checking and avoids ambiguity in boolean comparisons.

Final Workaround: Avoid RIGHT JOIN Until Official Release

Until RIGHT JOIN is fully supported, use LEFT JOIN with reversed table order and adjust the WHERE clause accordingly. This bypasses the optimizer bug entirely and ensures reliable results.