Unexpected NULL Results in SQLite FULL OUTER JOIN Without ON Clause

Understanding FULL OUTER JOIN Behavior with Empty Tables and Missing ON Clauses

Issue Overview: Discrepancy in Results Between Two FULL OUTER JOIN Queries

The core issue involves unexpected results from two SELECT statements using FULL OUTER JOIN in SQLite. The first query returns an empty result set, while the second query returns a single NULL value. This discrepancy occurs despite both queries joining the same tables (t1 and t0) with similar structures and data.

Table Definitions and Data:

• t1 is an empty table with a single column c0.
• t0 contains one row with a NULL value in its c0 column.

Queries in Question:

1. SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0;
   Expected: A single NULL value (since t1 is empty and t0 has one row).
   Actual: Empty result set.

2. SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0 WHERE (t0.c0 IS NULL);
   Result: A single NULL value.

The addition of the WHERE (t0.c0 IS NULL) clause in the second query causes SQLite to return the expected NULL value, whereas the first query omits this result entirely. This inconsistency highlights a flaw in how SQLite processes FULL OUTER JOIN operations when one table is empty and no explicit ON clause is provided.

Key Observations:

• A FULL OUTER JOIN without an ON clause behaves differently than one with a WHERE filter.
• The presence of a WHERE clause alters the query execution plan, bypassing the bug observed in the first query.
• The issue is specific to SQLite versions prior to the fix in commit f23a429d4153518d.

Possible Causes: Misalignment in JOIN Handling and Filter Application

1. Missing ON Clause in FULL OUTER JOIN
   SQLite’s FULL OUTER JOIN requires an ON clause to define the relationship between tables. When omitted, the query parser may incorrectly interpret the join, leading to incomplete result sets. The absence of an ON clause in the first query triggers undefined behavior in older SQLite versions, causing the join to exclude rows from the non-empty table (t0).

2. Empty Table Participation in Joins
   When one table in a FULL OUTER JOIN is empty, SQLite must still return all rows from the non-empty table, combined with NULL values for columns from the empty table. A bug in older versions fails to preserve rows from the non-empty table when no ON clause is present.

3. Interaction Between DISTINCT and NULL Values
   The DISTINCT keyword in the first query operates on the result set after the join. If the join itself omits the NULL row from t0, the DISTINCT clause has no data to process, resulting in an empty output. The WHERE clause in the second query forces SQLite to retain the NULL row during the join phase, allowing DISTINCT to process it.

4. Query Optimizer Edge Case
   The SQLite query optimizer may generate different execution plans for joins with and without WHERE clauses. In the first query, the optimizer might prematurely discard the NULL row from t0 due to incorrect assumptions about the join’s semantics.

Troubleshooting Steps, Solutions & Fixes: Resolving JOIN Inconsistencies

Step 1: Validate SQLite Version and Apply the Fix
The issue is resolved in SQLite versions containing commit f23a429d4153518d. Confirm your SQLite version and update if necessary:

SELECT sqlite_version();  

If the version is older than 3.39.0 with the fix, upgrade to a newer build.

Step 2: Explicitly Define JOIN Conditions
Always include an ON clause in FULL OUTER JOIN operations to avoid undefined behavior. Rewrite the first query to explicitly define the join condition, even if it’s a tautology (e.g., ON 1=1):

SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0 ON 1=1;  

This forces SQLite to process the join correctly, ensuring rows from t0 are preserved.

Step 3: Use COALESCE to Handle NULLs in Joins
When dealing with NULL values in join results, use COALESCE to standardize output:

SELECT DISTINCT COALESCE(t1.c0, t0.c0) FROM t1 FULL OUTER JOIN t0;  

This ensures NULL values from either table are explicitly represented in the result.

Step 4: Avoid Implicit Cross Joins
A FULL OUTER JOIN without an ON clause is not standard SQL and may be interpreted as a CROSS JOIN. Use explicit syntax to clarify intent:

-- Explicit CROSS JOIN (not recommended for empty tables)
SELECT DISTINCT t1.c0 FROM t1 CROSS JOIN t0;  

Step 5: Test with Alternative JOIN Types
If FULL OUTER JOIN behaves unpredictably, simulate it using LEFT JOIN and UNION ALL:

SELECT t1.c0 FROM t1 LEFT JOIN t0 ON 1=1  
UNION ALL  
SELECT NULL FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1);  

This manually combines rows from both tables, ensuring the NULL from t0 is included.

Step 6: Analyze Query Execution Plans
Use SQLite’s EXPLAIN command to inspect how the engine processes the join:

EXPLAIN SELECT DISTINCT t1.c0 FROM t1 FULL OUTER JOIN t0;  

Compare the output with the execution plan of the working query (with the WHERE clause) to identify optimization discrepancies.

Step 7: Report Edge Cases to SQLite Maintainers
If updating SQLite doesn’t resolve the issue, provide a reproducible test case to the SQLite team, including table definitions, sample data, and queries.

Final Solution Summary:
The root cause is a historical bug in SQLite’s handling of FULL OUTER JOIN without ON clauses when one table is empty. The fix involves updating SQLite or rewriting queries to include explicit join conditions and using standard patterns to handle NULL values. Always validate join semantics and test edge cases involving empty tables or NULL data.