Unexpected Query Results Due to Ambiguous Column Affinity in UNION-Based Views

Issue Overview: Inconsistent Filtering Behavior in Views with Mixed-Type UNION Columns

A developer encountered unexpected results when querying a view created via a UNION operation between a table column and a CAST(NULL AS NUMERIC) expression. The view v0 returns two NULL rows in a basic SELECT, but when used in a complex WHERE clause involving boolean logic and range comparisons, the query returns contradictory results between filtered and unfiltered contexts.

The core problem revolves around SQLite’s handling of column affinity in compound views (views created via UNION, INTERSECT, or EXCEPT). The view v0 combines:

1. A column c0 from table t0 containing an empty string (''), which has no declared type (defaulting to BLOB affinity)
2. A CAST(NULL AS NUMERIC) expression, which explicitly assigns NUMERIC affinity to the NULL value

When filtering this view using the predicate NOT (v0.c0 AND NULL) IN ('1' BETWEEN 1 AND v0.c0), the query returns no rows despite the unfiltered version of this expression evaluating to 1 (TRUE) for both rows. This inconsistency stems from SQLite’s indeterminate column affinity resolution in compound views, where the database engine may choose different affinities for the same column depending on query optimization decisions.

Key technical components involved:

• Type Affinity Rules: SQLite’s flexible typing system assigns "affinities" (TEXT, NUMERIC, INTEGER, REAL, BLOB) to columns, influencing how values are compared and stored
• Compound View Ambiguity: When UNION combines columns with different affinities, the resulting view column’s affinity becomes undefined per SQLite documentation
• NULL Handling: CAST(NULL AS NUMERIC) creates a typed NULL that interacts differently with comparison operators than untyped NULLs
• Three-Valued Logic: The combination of AND/NOT operators with NULL values creates truth tables that vary based on type coercion

Possible Causes: Type Affinity Conflicts and Comparison Semantics

1. Undefined Column Affinity in UNION-Based Views

SQLite determines column affinity for compound views using the following rules:

• If all columns in a UNION position have the same affinity, that affinity is used
• If affinities differ, the column uses BLOB affinity if any component is BLOB
• Otherwise, it uses NUMERIC affinity if any component is NUMERIC
• Remaining cases default to TEXT

In the test case:

• The first UNION branch (t0.c0) has BLOB affinity (empty string in typeless column)
• The second branch (CAST(NULL AS NUMERIC)) has NUMERIC affinity
• Per rules, the view column should default to NUMERIC affinity. However, implementation details and query optimization paths can override this, creating situational affinity choices.

2. Affinity-Dependent Comparison Operators

The BETWEEN operator and IN clause exhibit different behaviors based on the affinity of v0.c0:

• NUMERIC affinity: '1' BETWEEN 1 AND v0.c0 becomes 1 BETWEEN 1 AND <numeric_value>
  • NULL values propagate through the expression
  • Comparisons use numeric coercion
• BLOB affinity: The same expression becomes string comparisons
  • '1' (string) vs 1 (integer) vs v0.c0 (empty string as BLOB)
  • String comparison rules apply (ASCII values, lexicographical order)

When affinity is indeterminate, SQLite may choose different comparison modes:

1. Optimizer-Driven Affinity Selection: The query planner might favor the affinity of the first UNION branch during index analysis
2. Expression Context Bias: Arithmetic operators (like AND used as a bitwise operator here) can temporarily change affinity
3. NULL Casting Side Effects: CAST(NULL AS NUMERIC) introduces a persistent type hint that conflicts with the untyped NULL from t0.c0

3. Three-Valued Logic Interactions

The complex predicate NOT (v0.c0 AND NULL) IN (...) combines multiple NULL-handling scenarios:

• v0.c0 AND NULL evaluates to NULL regardless of v0.c0‘s value (NULL AND anything = NULL)
• IN (...) with a NULL left operand returns NULL (not TRUE/FALSE)
• The IS TRUE suffix converts NULL to FALSE
• The NOT operator inverts TRUE/FALSE but leaves NULL unchanged

However, the actual result depends on:

• Whether the IN clause’s right-hand expression ('1' BETWEEN 1 AND v0.c0) evaluates to NULL or a concrete value
• How affinity choices affect the BETWEEN comparison’s outcome

Troubleshooting Steps, Solutions & Fixes: Resolving Affinity Ambiguity

1. Eliminate Affinity Conflicts in View Definitions

Solution: Standardize column affinities across all UNION branches

-- Explicitly cast both branches to NUMERIC
CREATE VIEW v0(c0) AS 
  SELECT CAST(t0.c0 AS NUMERIC) FROM t0 
  UNION 
  SELECT CAST(NULL AS NUMERIC) FROM t0;

-- Alternative: Use consistent affinity via COALESCE
CREATE VIEW v0(c0) AS
  SELECT COALESCE(t0.c0, 0.0) FROM t0  -- Forces REAL affinity
  UNION
  SELECT CAST(NULL AS REAL) FROM t0;

Rationale: By ensuring all UNION branches share the same affinity, SQLite will consistently apply type handling rules. CAST operations override the natural affinity of source columns.

Verification:

-- Check view column affinity
SELECT affinity FROM pragma_table_info('v0');
-- Should return 'numeric' or 'real' consistently

2. Use Explicit Type Conversions in Queries

Solution: Bypass view’s indeterminate affinity by forcing conversions in WHERE clauses

SELECT * FROM v0 
WHERE NOT (CAST(v0.c0 AS NUMERIC) AND NULL) 
      IN (CAST('1' AS NUMERIC) BETWEEN 1 AND CAST(v0.c0 AS NUMERIC));

Rationale: Explicit CASTs in the predicate ensure numeric comparisons regardless of the view’s inherent affinity. This overrides any optimizer-driven affinity choices.

Performance Consideration: Adds computational overhead but guarantees consistent results. Use sparingly on large datasets.

3. Analyze Query Plans for Affinity Selection

Diagnostic Step: Use EXPLAIN to detect affinity-related optimizations

EXPLAIN SELECT * FROM v0 WHERE ...;

Look For:

• Affinity annotations in opcodes
• Type conversion operations (CAST, etc.)
• Comparison operator types (Eq, Lt, Gt) indicating numeric vs string compares

Example Analysis:

addr  opcode        p1    p2    p3    p4             
---   -----------   ----  ----  ----  --------------
...   
5     Affinity      1     0     B     NUMERIC        
6     Le            3     2     -     (BINARY) 

Indicates NUMERIC affinity applied before comparison (Le = Less than or equal).

4. Implement Affinity Testing Probes

Diagnostic Query: Determine effective affinity at runtime

-- Test numeric behavior
SELECT c0 + 0 FROM v0; 
-- If returns numbers or NULLs: numeric affinity active

-- Test string behavior
SELECT c0 || '' FROM v0;  
-- If returns concatenated strings: TEXT affinity

-- Test blob behavior
SELECT typeof(c0) FROM v0;
-- Returns 'blob' for BLOB affinity

Interpretation: Run these probes under different query scenarios to detect affinity variations. Consistent results across executions indicate stable affinity resolution.

5. Restructure Views to Avoid UNION Ambiguity

Alternative Design: Replace UNION views with explicit joins or subqueries

-- Using COALESCE with LEFT JOIN
CREATE VIEW v0(c0) AS
SELECT COALESCE(t0.c0, CAST(NULL AS NUMERIC)) 
FROM t0 
LEFT JOIN (SELECT CAST(NULL AS NUMERIC) AS c0 FROM t0) AS sub
  ON 1=1;

Advantage: Avoids UNION-induced affinity ambiguity by using a single source of column definition. COALESCE ensures uniform affinity.

6. Leverage Strict Typing Mode (SQLite 3.37+)

Solution: Enable STRICT tables to enforce type consistency

CREATE TABLE t0 (c0 NUMERIC STRICT); 
INSERT INTO t0 VALUES (''); -- Fails: type mismatch

Benefit: Prevents mixing of types at the table level, making UNION conflicts impossible. Requires schema modifications and valid data typing.

Limitation: Doesn’t resolve existing views with mixed-type UNIONs but prevents future occurrences.

7. Use Deterministic Functions in Predicates

Workaround: Replace affinity-sensitive operators with type-agnostic functions

SELECT * FROM v0 
WHERE NOT (v0.c0 IS NULL AND NULL) 
      IN (IIF('1' BETWEEN 1 AND v0.c0, 1, 0));

Rationale: IIF/COALESCE/NVL functions handle NULLs predictably, reducing reliance on implicit type conversions.

8. Monitor Version-Specific Behavior

Consideration: Test across SQLite versions, as affinity resolution details change:

• 3.40.0 (original test case): May have different UNION affinity rules
• 3.37.0 (STRICT tables introduced): Offers stricter type control
• 3.35.0 (BLOB literals): Alters empty string handling

Action: Replicate the issue in multiple versions using the same schema/data to identify version-dependent behaviors.

9. Implement Comprehensive Type Sanitization

Preventive Measure: Add affinity checks to CI/CD pipelines

-- Check for affinity consistency in views
SELECT 
  name AS view_name,
  COUNT(DISTINCT affinity) AS affinity_types
FROM 
  pragma_table_info('<view_name>') 
  JOIN (
    SELECT 
      sm.name, 
      ti.name AS col_name,
      ti.affinity
    FROM 
      sqlite_schema AS sm
      JOIN pragma_table_info(sm.name) AS ti
    WHERE 
      sm.type = 'view'
  ) AS view_cols 
GROUP BY 
  view_name
HAVING 
  affinity_types > 1;

Outcome: Flags views with mixed affinities during development, preventing deployment of ambiguous schemas.

10. Utilize CAST in View Definitions

Best Practice: Explicitly define output types for view columns

CREATE VIEW v0(c0 NUMERIC) AS 
  SELECT CAST(t0.c0 AS NUMERIC) FROM t0 
  UNION 
  SELECT CAST(NULL AS NUMERIC) FROM t0;

Effect: The c0 NUMERIC syntax in the view definition explicitly sets column affinity, overriding automatic detection. Note: SQLite ignores this syntax in standard releases but may honor it in forks or future versions.

Workaround for Current Versions:

CREATE VIEW v0(c0) AS 
  SELECT CAST(col AS NUMERIC) FROM (
    SELECT t0.c0 AS col FROM t0 
    UNION 
    SELECT NULL FROM t0
  );

This nested SELECT with an intermediate CAST enforces uniform affinity before the view’s final output.