Schema Integrity & Query Planner Behavior in Join Operations

Inconsistent Data Types Between Joined Columns Leading to Full Table Scans

Root Conflict: Implicit Type Conversion Disabling Index Utilization

The core issue stems from mismatched data types between joined columns across tables, specifically between cleaned_titles.title_key (implicitly TEXT due to schema declaration omission) and cleaned_title_principals.title_key (explicitly INTEGER). SQLite’s query planner avoids using indexes when comparing columns of different types because implicit type conversion invalidates index applicability. This forces sequential scans (SCAN operations) instead of efficient SEARCH operations using indexes.

The cleaned_titles table was originally defined with title_key primary key without an explicit INTEGER type specifier. SQLite defaults to assigning NUMERIC affinity in this context, which behaves unpredictably with strictly typed INTEGER values from cleaned_title_principals.title_key. The primary key index (sqlite_autoindex_cleaned_titles_1) became effectively useless for joins due to this type mismatch.

Diagnostic Triggers: Schema Ambiguity and Implicit Casting Side Effects

1. Schema Ambiguity in Primary Key Definition:
   The absence of an explicit INTEGER type for cleaned_titles.title_key caused SQLite to infer column affinity as NUMERIC, creating a type mismatch with the explicitly INTEGER-typed cleaned_title_principals.title_key.

2. Subquery Materialization with Incorrect Type Inference:
   When materializing title_key values from cleaned_title_principals (explicit INTEGER) into a temporary structure (e.g., CTE or subquery), SQLite sometimes propagates the INTEGER type. However, joining these values against cleaned_titles.title_key (NUMERIC affinity) triggered implicit casting, invalidating index use.

3. Explicit Value Lists Bypassing Type Issues:
   Hardcoding title_key values as literals (e.g., WHERE title_key IN (63886, 69415, ...)) worked because SQLite treated them as INTEGER constants, coercing cleaned_titles.title_key to INTEGER during comparison. This coercion aligned types temporarily, enabling index usage.

Resolution Workflow: Schema Correction and Type Alignment

Step 1: Rectify Table Definitions to Enforce Type Consistency
Recreate the cleaned_titles table with an explicit INTEGER type for title_key:

CREATE TABLE cleaned_titles (
  title_key INTEGER PRIMARY KEY,  -- Explicit INTEGER type
  title_type TEXT,
  primary_name TEXT,
  original_name TEXT,
  is_adult TEXT,
  start_year TEXT,
  end_year TEXT,
  runtime_minutes TEXT,
  genres TEXT
);

This ensures title_key has INTEGER affinity, matching cleaned_title_principals.title_key.

Step 2: Rebuild the Database with Consistent Typing
Use SQLite’s .dump command to export the database schema and data, edit the CREATE TABLE statements to enforce correct types, then reimport:

sqlite3 original.db .dump > backup.sql  
# Edit backup.sql to fix column types  
sqlite3 fixed.db < backup.sql  

This eliminates residual type mismasks and rebuilds indexes with correct affinity.

Step 3: Validate Index Utilization Post-Schema Fix
Re-run the original query with EXPLAIN QUERY PLAN to confirm index usage:

EXPLAIN QUERY PLAN
WITH quentin AS (
  SELECT name_key
  FROM cleaned_names
  WHERE name = 'Quentin Tarantino'
), tarantino_movies AS (
  SELECT title_key
  FROM cleaned_title_principals p
  JOIN quentin q USING(name_key)
  WHERE category = 'director'
)
SELECT r.primary_name, r.original_name, r.start_year
FROM cleaned_titles r
JOIN tarantino_movies t ON t.title_key = r.title_key;

The output should show SEARCH r USING INTEGER PRIMARY KEY (rowid=?), confirming index usage.

Step 4: Address Residual Subquery Type Issues
If subqueries or CTEs introduce type mismatches (e.g., via CAST), explicitly align types:

-- Cast to INTEGER if tarantino_movies.title_key is inconsistently typed
SELECT r.primary_name, r.original_name, r.start_year
FROM cleaned_titles r
WHERE r.title_key IN (
  SELECT CAST(title_key AS INTEGER)  -- Force INTEGER type
  FROM tarantino_movies
);

Avoid CAST in production queries; instead, fix schema definitions.

Step 5: Index Optimization for Composite Filters
Add composite indexes to support filtered joins, e.g., indexing cleaned_title_principals on (name_key, category):

CREATE INDEX principals_nk_category ON cleaned_title_principals(name_key, category);

This allows the WHERE category = 'director' filter to be applied during the index scan, reducing rows early.

Step 6: Verify Statistical Data for Query Planner
Ensure SQLite’s internal statistics (stored in sqlite_stat1) are up-to-date:

ANALYZE;

Outdated statistics can mislead the query planner into choosing suboptimal plans.

Step 7: Query Restructuring for Direct Joins
Simplify the query by eliminating unnecessary CTEs and joining directly:

SELECT r.primary_name, r.original_name, r.start_year
FROM cleaned_titles r
JOIN cleaned_title_principals p ON r.title_key = p.title_key
JOIN cleaned_names n ON p.name_key = n.name_key
WHERE n.name = 'Quentin Tarantino'
  AND p.category = 'director';

This often yields better plans by reducing abstraction layers.

Step 8: Monitor for Implicit Casting in Indexed Columns
Use strict typing in schema definitions and avoid TEXT for columns that store numeric identifiers. Explicitly declare INTEGER for keys to prevent affinity mismatches.

Step 9: Utilize Covering Indexes for Critical Queries
If cleaned_titles queries frequently access primary_name, original_name, and start_year, consider a covering index:

CREATE INDEX titles_covering ON cleaned_titles(title_key, primary_name, original_name, start_year);

This allows the query to retrieve all needed data from the index, avoiding table access.

Step 10: Benchmark and Compare Query Plans
After each schema or query modification, use EXPLAIN QUERY PLAN to verify that:

• SCAN operations are replaced with SEARCH.
• Indexes are used in optimal order (e.g., largest table first).
• Temporary sorting/hashing is minimized.

Final Note: Schema integrity is foundational. Explicit typing, consistent constraints, and periodic ANALYZE maintenance prevent many query planner anomalies. When indexes are ignored, type mismatches and outdated statistics are prime suspects.