Resolving Incorrect NULLs When Comparing Table Columns via pragma_table_info and FULL OUTER JOIN

Issue Overview: Unexpected NULLs in Column Comparison Using pragma_table_info and FULL OUTER JOIN

A common task in database management involves comparing the column structures of two tables to identify differences. In SQLite, the pragma_table_info function is frequently used to retrieve metadata about a table’s columns. However, when attempting to compare columns between two tables using a FULL OUTER JOIN directly on pragma_table_info results, users may encounter unexpected NULL values in the output. This occurs even when columns exist in both tables, leading to incorrect comparisons.

For example, consider two tables, t3 and t4, with overlapping columns:

CREATE TABLE t3 ("a" TEXT, "b" TEXT);
CREATE TABLE t4 ("a" TEXT, "b" TEXT, "c" TEXT);

A query to compare their columns using pragma_table_info and FULL OUTER JOIN:

SELECT t4.name AS t4_name, t3.name AS t3_name 
FROM pragma_table_info('t4') t4 
FULL OUTER JOIN pragma_table_info('t3') t3 USING(name);

Expected Result:

t4_name | t3_name
--------|--------
a       | a
b       | b
c       | NULL

Actual Result:

t4_name | t3_name
--------|--------
a       | a
b       | b
c       | NULL
NULL    | b

The unexpected fourth row (NULL | b) indicates a discrepancy where column b from t3 appears unpaired, despite existing in both tables. This anomaly arises due to how pragma_table_info interacts with FULL OUTER JOIN when used inline. The issue becomes more pronounced when comparing tables with partially overlapping columns, such as t4 ("a", "b", "c") and t5 ("b", "c", "d"), leading to a "long tail" of NULL values that misrepresent the actual column differences.

Possible Causes: Pragma Function Behavior and Join Evaluation Order

The root cause of this issue lies in the interaction between SQLite’s pragma_table_info table-valued function and the query optimizer’s handling of FULL OUTER JOIN.

1. Pragma Functions as Correlated Subqueries

pragma_table_info is not a standard table; it is a virtual table that dynamically generates rows based on the schema of the specified table. When used directly in a JOIN clause, SQLite may treat each occurrence of pragma_table_info as a correlated subquery. This means the function is re-evaluated for each row processed by the join, leading to unexpected interactions with the join logic.

2. Join Type and NULL Handling

A FULL OUTER JOIN is designed to return all rows from both tables, pairing matching rows and filling in NULLs where no match exists. However, when the source of the rows (in this case, pragma_table_info) is re-evaluated during the join process, the result set becomes unstable. For instance, the pragma_table_info for t3 might be re-executed in a context where the join condition (USING(name)) inadvertently filters or duplicates rows.

3. Query Optimization and Materialization

SQLite’s query optimizer may choose not to materialize (store in temporary storage) the results of pragma_table_info when used inline in a join. Instead, it processes the function dynamically, which can lead to inconsistencies if the function’s output is expected to remain static during the join operation. This is particularly problematic when comparing two pragma_table_info results, as their evaluations interfere with each other.

Troubleshooting Steps, Solutions & Fixes

Step 1: Use CTEs to Materialize pragma_table_info Results

The most reliable workaround is to materialize the results of pragma_table_info in Common Table Expressions (CTEs) before performing the join. This forces SQLite to evaluate the pragma function once per table and store the results, ensuring stability during the join.

Example:

WITH 
  t4 AS (SELECT name FROM pragma_table_info('t4')),
  t3 AS (SELECT name FROM pragma_table_info('t3'))
SELECT 
  t4.name AS t4_name, 
  t3.name AS t3_name 
FROM t4 
FULL OUTER JOIN t3 USING(name);

Result:

t4_name | t3_name
--------|--------
a       | a
b       | b
c       | NULL

By materializing the column lists upfront, the FULL OUTER JOIN operates on static datasets, eliminating the erroneous NULL entries.

Step 2: Upgrade to SQLite 3.45.4 or Later

The SQLite development team addressed this issue in commit 6838bf3a. Upgrading to SQLite version 3.45.4 (or newer) resolves the problem by ensuring that pragma_table_info results are correctly handled in FULL OUTER JOIN operations without requiring CTEs.

Verification:
After upgrading, re-run the original query:

SELECT t4.name AS t4_name, t3.name AS t3_name 
FROM pragma_table_info('t4') t4 
FULL OUTER JOIN pragma_table_info('t3') t3 USING(name);

The output should now match the expected result.

Step 3: Use LEFT JOIN for Subset Comparisons

If one table’s columns are a strict subset of the other’s, a LEFT JOIN can be used instead of a FULL OUTER JOIN. This avoids the issue entirely in such cases.

Example:

SELECT t4.name AS t4_name, t3.name AS t3_name 
FROM pragma_table_info('t4') t4 
LEFT JOIN pragma_table_info('t3') t3 USING(name);

Result:

t4_name | t3_name
--------|--------
a       | a
b       | b
c       | NULL

Step 4: Validate Column Names and Aliases

Ensure that column names are consistently referenced and aliased. Misaligned aliases or references can introduce NULLs unrelated to the core issue.

Example of Pitfall:

-- Incorrect: Mixing aliases in SELECT and JOIN
SELECT t4.name AS t4_name, t3.name AS t3_name 
FROM pragma_table_info('t4') AS t4 
FULL OUTER JOIN pragma_table_info('t3') AS t3 ON t4.name = t3.colname; -- Hypothetical typo

Always use explicit aliases and verify join conditions.

Step 5: Cross-Validate with Alternative Methods

If uncertainty persists, cross-validate the results using alternative methods such as querying the sqlite_schema table or exporting the schema to SQL.

Example Using sqlite_schema:

SELECT sql 
FROM sqlite_schema 
WHERE name IN ('t3', 't4');

This returns the CREATE TABLE statements, allowing manual comparison of column lists.

Step 6: Check for Edge Cases in Column Names

Column names with special characters or case sensitivity issues can cause mismatches. Use COLLATE NOCASE in join conditions if case insensitivity is desired.

Example:

WITH 
  t4 AS (SELECT name FROM pragma_table_info('t4')),
  t3 AS (SELECT name FROM pragma_table_info('t3'))
SELECT 
  t4.name AS t4_name, 
  t3.name AS t3_name 
FROM t4 
FULL OUTER JOIN t3 ON t4.name COLLATE NOCASE = t3.name COLLATE NOCASE;

Step 7: Profile Query Execution

Use SQLite’s EXPLAIN command to analyze the query plan and identify whether pragma_table_info is being re-evaluated multiple times.

Example:

EXPLAIN 
SELECT t4.name AS t4_name, t3.name AS t3_name 
FROM pragma_table_info('t4') t4 
FULL OUTER JOIN pragma_table_info('t3') t3 USING(name);

Look for repeated scans or subqueries indicating redundant evaluations.

Final Recommendation

For stable results across all SQLite versions, always materialize pragma_table_info results in CTEs before joining. This approach is immune to optimizer quirks and ensures accurate column comparisons. If upgrading SQLite is feasible, version 3.45.4+ provides a native fix for the issue.