Unexpected RIGHT JOIN Behavior with Row-Value Comparisons in SQLite

Unexpected NULL Evaluations in RIGHT JOIN Queries with Row-Value Comparisons

The core issue involves unexpected results when using row-value comparisons (e.g., (col1, col2) = (val1, val2)) in the WHERE clause of a RIGHT JOIN query. This manifests in two distinct but related scenarios:

1. Inconsistent NULL Handling: A WHERE clause comparing a row-value expression to a tuple containing NULL and other values may return rows even when the comparison evaluates to NULL (which is treated as "false" in SQLite’s filtering logic).
2. Discrepancy Between Projection and Filtering: When the same row-value comparison is used in the SELECT list versus the WHERE clause, the former returns NULL (as expected), while the latter incorrectly includes rows that should be excluded.

Key Observations from Test Cases

• Test Case 1 (RTree Virtual Table):

  CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2);
  CREATE TABLE t1(c0);
  INSERT INTO rt0(c1, c2) VALUES (x'01', x'02');
  -- Returns 1 row with (1, 0.0, 0.0)
  SELECT * FROM t1 RIGHT OUTER JOIN rt0;
  -- Returns NULL
  SELECT ((null, false)==(rt0.c0, rt0.c2)) FROM t1 RIGHT OUTER JOIN rt0;
  -- Incorrectly returns 1 row despite WHERE clause evaluating to NULL
  SELECT * FROM t1 RIGHT OUTER JOIN rt0 WHERE ((null, false)==(rt0.c0, rt0.c2));
  

  The third query returns a row even though the WHERE clause should filter it out.

• Test Case 2 (Standard Tables):

  CREATE TABLE t1(id INTEGER PRIMARY KEY, x INT);
  CREATE TABLE t2(y INT);
  INSERT INTO t1(id,x) VALUES(1, 0);
  -- Returns no rows (correct)
  SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE (+id,x)=(99,0);
  -- Returns 1 row (incorrect)
  SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE (id,x)=(99,0);
  

  The second SELECT incorrectly returns a row when using a direct row-value comparison.

Critical Context: SQLite’s Three-Valued Logic

SQLite uses three-valued logic (TRUE, FALSE, NULL). In WHERE clauses, only rows where the condition evaluates to TRUE are included. However, this behavior breaks down in the presence of row-value comparisons and RIGHT JOIN due to internal optimizations and type handling.

Root Causes of Incorrect Row-Value Handling in RIGHT JOIN WHERE Clauses

1. Type Affinity Mismatch in Row-Value Comparisons

SQLite’s rtree virtual tables store data with specific type affinities (e.g., c0, c1, c2 in rt0 are stored as REAL). When comparing values of different types (e.g., BLOB literals like x'01' vs. REAL columns), implicit type conversions occur. However, NULL in row-value comparisons bypasses these checks, leading to incorrect evaluations.

In the first test case:

• rt0.c1 and rt0.c2 are REAL columns.
• The inserted values x'01' and x'02' are BLOB literals.
• The comparison (null, false)==(rt0.c0, rt0.c2) mixes NULL (unknown type) with REAL and BOOLEAN values, causing the entire expression to evaluate to NULL.

2. RIGHT JOIN Semantics and NULL Propagation

A RIGHT JOIN preserves all rows from the right table, filling in NULL for unmatched left table columns. When the left table (t1 or t2 in the examples) is empty or has no matching rows, all columns from the left table are NULL. This interacts poorly with row-value comparisons:

• Optimizer Short-Circuiting: SQLite’s query optimizer may incorrectly short-circuit row-value comparisons when one operand is a NULL literal, treating the entire comparison as NULL without evaluating all components.
• Lazy Evaluation in WHERE Clauses: The WHERE clause is evaluated after constructing the JOIN result. If the row-value comparison involves NULL literals or columns from the left table (which are NULL in a RIGHT JOIN), the comparison may be misoptimized.

3. Internal Representation of Row-Values

SQLite represents row-values as ephemeral BLOB structures during query execution. When a row-value comparison includes a NULL literal, the internal type-checking logic fails to propagate the NULL correctly, especially when the other operand is a column from a virtual table (e.g., rtree).

4. Version-Specific Bugs in Query Optimization

The bug was present in SQLite 3.39.0 (commit f6d6f969) and earlier. The optimizer’s handling of row-value comparisons in outer joins had a flaw where:

• Comparisons involving NULL literals and non-NULL columns were not fully evaluated.
• The WHERE clause filter was applied before finalizing the JOIN result in some cases.

Resolving RIGHT JOIN and Row-Value Comparison Anomalies in SQLite

Step 1: Verify SQLite Version and Patches

• Check Version:

  SELECT sqlite_version();
  

  If the result is 3.39.0 or earlier, the bug exists.

• Upgrade to Fixed Versions: The fix was implemented in SQLite trunk after commit f6d6f969. Use a nightly build or version 3.39.1+.

Step 2: Rewrite Row-Value Comparisons for Type Consistency

Avoid mixing NULL literals with columns of different type affinities. Instead of:

SELECT * FROM t1 RIGHT JOIN rt0 WHERE (NULL, false) = (rt0.c0, rt0.c2);

Use explicit type casts or COALESCE:

SELECT * FROM t1 RIGHT JOIN rt0 
WHERE (CAST(NULL AS REAL), false) = (rt0.c0, rt0.c2);

Step 3: Use Explicit IS NOT DISTINCT FROM for NULL-Safe Comparisons

SQLite does not natively support IS NOT DISTINCT FROM, but you can emulate it:

SELECT * FROM t2 RIGHT JOIN t1 ON true 
WHERE (id = 99 AND x = 0) OR (id IS NULL AND x IS NULL);

This forces a component-wise comparison, avoiding incorrect NULL handling.

Step 4: Avoid Row-Value Comparisons in RIGHT JOIN WHERE Clauses

Refactor queries to move row-value logic to ON clauses or use subqueries:

SELECT * FROM t2 RIGHT JOIN t1 ON (id, x) = (99, 0);

Step 5: Disable Faulty Optimizations with PRAGMA Settings

Temporarily disable the query optimizer’s short-circuiting:

PRAGMA optimize = 0x0000;

Note: This is a diagnostic step, not a permanent solution.

Step 6: Apply Workarounds for rtree Virtual Tables

For rtree-specific issues, materialize the virtual table’s data into a temporary table:

CREATE TEMP TABLE rt0_temp AS SELECT * FROM rt0;
SELECT * FROM t1 RIGHT JOIN rt0_temp WHERE (NULL, false) = (c0, c2);

Step 7: Use UNION ALL to Isolate JOIN Logic

Break the query into components that handle NULL and non-NULL cases separately:

-- Rows where left table is NOT NULL
SELECT * FROM t2 INNER JOIN t1 ON true WHERE (id, x) = (99, 0)
UNION ALL
-- Rows where left table is NULL (RIGHT JOIN)
SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE t2.y IS NULL;

Step 8: Monitor Query Plans with EXPLAIN

Use EXPLAIN to detect if the optimizer is mishandling row-value comparisons:

EXPLAIN SELECT * FROM t2 RIGHT JOIN t1 ON true WHERE (id, x) = (99, 0);

Look for opcodes like Affinity or IsNull that may indicate incorrect type handling.

Step 9: Report and Track Regressions

If the issue persists after upgrading, file a bug report at SQLite’s GitHub with:

• A minimal reproducible test case.
• Output of sqlite3_errmsg().
• Query plans from EXPLAIN.

Final Solution: Patch or Upgrade SQLite

The root cause was a bug in the optimizer’s handling of row-value comparisons in outer joins. This was fixed in SQLite trunk. To apply the fix:

• Download the Latest Trunk:

  fossil clone https://www.sqlite.org/src sqlite.fossil
  mkdir sqlite && cd sqlite
  fossil open ../sqlite.fossil
  

• Recompile with Correct Configuration:

  ./configure --enable-all
  make
  

By addressing type affinity mismatches, avoiding ambiguous row-value comparisons, and ensuring the latest patches are applied, developers can resolve this class of anomalies in SQLite.