Regression in UNION View Column Type Affinity After SQLite 3.41.0 Update

Understanding Type Affinity Resolution in UNION-Based Views

The core issue revolves around changes in how SQLite determines column type affinity for views constructed using the UNION or UNION ALL operators. Prior to version 3.41.0, SQLite inconsistently propagated user-defined type names (such as DATE) through UNION operations in view definitions. Starting with version 3.41.0, a critical bug fix altered this behavior to adhere strictly to SQLite’s type affinity rules, resulting in columns being assigned BLOB affinity when their constituent UNION subqueries return incompatible affinities. This change impacts tools, ORMs, or application logic that rely on PRAGMA table_info metadata to infer column types.

SQLite’s type system differs from traditional RDBMS implementations. Columns have a "type affinity" rather than rigid data types. The five affinities – TEXT, NUMERIC, INTEGER, REAL, and BLOB – influence how values are stored and converted. User-defined type names like DATE are mapped to these affinities. For example, DATE maps to NUMERIC affinity. When a view combines columns via UNION, SQLite must resolve conflicting affinities from the subqueries. The 3.41.0 update corrected flawed logic that previously allowed user-defined type names to "override" affinity resolution, prioritizing correctness over backward compatibility.

Root Causes of BLOB Affinity in UNION Views with Mixed Type Origins

Three interrelated factors explain why column b in the test_v view now reports BLOB affinity:

1. SQLite’s Type Affinity Hierarchy:
   The UNION operator requires columns from all subqueries to share compatible affinities. When affinities conflict, SQLite uses precedence rules: BLOB has the lowest precedence, followed by TEXT, NUMERIC, INTEGER, and REAL. If any subquery column has BLOB affinity, the result is BLOB. Otherwise, SQLite selects the highest precedence affinity present. In the example, the first subquery (SELECT a, b FROM test) assigns NUMERIC affinity (from DATE), while the second subquery (SELECT 1 a, '2023-09-01T00:00:00' b) assigns TEXT affinity (from the string literal). Since NUMERIC and TEXT are incompatible, SQLite falls back to BLOB.

2. View Metadata Generation Logic:
   PRAGMA table_info retrieves column details from the sqlite_schema table, which stores the original view definition. However, the reported "type" is not guaranteed to match the original table’s schema. For views, SQLite attempts to infer a type name by examining the first subquery’s columns. Pre-3.41.0, this inference erroneously propagated user-defined type names even when subsequent subqueries introduced conflicting affinities. Post-3.41.0, the resolution strictly follows affinity precedence, discarding user-defined labels if affinities conflict.

3. Implicit Casting in UNION Operations:
   SQLite performs implicit casting to reconcile differing affinities in UNION queries. A NUMERIC value (e.g., Julian day numbers) and a TEXT date representation cannot be implicitly cast to a common type without data loss. Assigning BLOB affinity forces applications to handle value conversions explicitly, avoiding silent data corruption. This behavior aligns with SQLite’s "dynamic typing" philosophy but contradicts developers’ expectations from strictly typed systems.

Resolving and Preventing BLOB Affinity in Views with Type Conflicts

Step 1: Diagnose Affinity Conflicts in View Definitions

Identify all subqueries contributing to the view’s columns. For each column:

-- For each subquery in the view, run:
SELECT typeof(b) FROM (<subquery>) LIMIT 1;

Compare results. If outputs include multiple affinities (e.g., text and real), a conflict exists. Use PRAGMA table_info to confirm the view column’s resolved affinity.

Step 2: Standardize Affinities Across UNION Subqueries

Modify subqueries to return consistent affinities using CAST or type-specific functions:

CREATE VIEW test_v AS 
  SELECT a, b FROM test 
  UNION 
  SELECT 1 a, CAST('2023-09-01T00:00:00' AS NUMERIC) b;

This forces both subqueries to use NUMERIC affinity. PRAGMA table_info will now report DATE (or NUMERIC if using explicit casts).

Step 3: Leverage Strict Typing with SQLite’s STRICT Tables

If using SQLite 3.37.0+, define tables with STRICT mode to enforce column types:

CREATE TABLE test (a INTEGER PRIMARY KEY, b DATE) STRICT;

While DATE remains an alias for NUMERIC, STRICT mode ensures values adhere to declared types. Views built on strict tables may still require explicit casts in UNION subqueries.

Step 4: Use Deterministic Type Names in Views

For compatibility with ORMs expecting specific type strings, use CAST in view definitions:

CREATE VIEW test_v AS 
  SELECT a, CAST(b AS TEXT) AS b FROM test 
  UNION 
  SELECT 1 a, '2023-09-01T00:00:00' b;

This ensures both subqueries return TEXT affinity, making PRAGMA table_info report TEXT.

Step 5: Adopt Application-Side Type Handling

When modifying views is impractical, handle type conversions in application code:

# Python example using sqlite3
cursor.execute("SELECT a, b FROM test_v")
for row in cursor:
    date_val = datetime.fromisoformat(row[1]) if isinstance(row[1], str) else julian_to_date(row[1])

This approach accommodates multiple storage formats but increases code complexity.

Final Recommendation

The reported behavior is not a regression but a correction to SQLite’s type system. To maintain consistency across versions, avoid relying on PRAGMA table_info for type checking in views involving UNION. Instead, explicitly cast columns to the desired affinity or use application-level validation. For date/time values, standardize on either TEXT (ISO-8601) or NUMERIC (Julian day) formats throughout the schema.