Issue Overview: Column Affinity Mismatch in UNION ALL Views Leading to Unexpected Comparison Results

The core issue arises from the interaction between SQLite’s column affinity rules and the structure of a view that combines multiple SELECT statements using UNION ALL. When a view is defined with a compound SELECT (e.g., UNION ALL), the affinity of columns in the view is determined by the union of affinities from all component SELECT statements. This creates ambiguity in type handling when different component SELECTs assign conflicting affinities to the same view column.

In the example provided, the v0.c1 column exhibits this behavior because its values originate from two different SELECT clauses with different type conversion rules. The first SELECT clause casts rt0.c1 as REAL, while the second SELECT clause casts rt0.c0 as REAL. When combined via UNION ALL, the view column v0.c1 inherits a "soft" REAL affinity but contains values that may retain their original storage classes (integer, text, blob). This discrepancy becomes critical when comparing values using operators like BETWEEN, where SQLite’s comparison logic depends on both operand types and the column’s declared affinity.

The problem manifests in two similar queries returning contradictory results. The COUNT(*) query evaluates the comparison expression (('1674794530' BETWEEN v0.c1 AND x'') / (+v0.c1)) as false for all rows (returning 0), while the SUM(count) query using IS TRUE evaluates it as true for one row (returning 1). This inconsistency stems from SQLite applying different type conversion rules during expression evaluation, depending on whether the comparison result is coerced to a boolean explicitly via IS TRUE.

Possible Causes: Affinity-Driven Type Conversion and Expression Evaluation Ambiguity

1. Divergent Column Affinities in UNION ALL Components

• The CREATE VIEW v0 statement combines two SELECT clauses with different type conversions for c1:
  • First SELECT: rt0.c1 BETWEEN rt0.c1 AND rt0.c0 (implicit REAL affinity from rt0.c1)
  • Second SELECT: CAST(rt0.c0 AS REAL) (explicit REAL affinity)
• While both components nominally assign REAL affinity to v0.c1, the actual storage classes of values may differ (e.g., integer, text, blob). SQLite’s "manifest typing" allows values to retain their original storage class even when assigned to a column with a specific affinity.

2. Type Precedence in Comparison Operations

• The BETWEEN operator performs type conversions based on the affinities of its operands. When comparing a text literal ('1674794530') with a view column (v0.c1) that has REAL affinity but contains blob or integer values, SQLite may:
  • Convert '1674794530' to REAL (yielding 1674794530.0)
  • Convert blob values (e.g., x'7acfa3c4') to TEXT or INTEGER, depending on context
• These conversions are non-deterministic when operands have mixed storage classes, leading to inconsistent results.

3. Implicit Boolean Conversion in Aggregate Contexts

• In the COUNT(*) query, the expression (...) / (+v0.c1) evaluates to 0 (false) if either the numerator is 0 or the denominator is 0. However, in SQLite, division by zero returns NULL, which is treated as false in a WHERE clause.
• The SUM(count) query uses IS TRUE, forcing the expression result to be cast to a boolean. This bypasses NULL handling quirks and directly checks for truthiness, revealing a row where the comparison evaluates as true under different type conversion rules.

Troubleshooting Steps, Solutions & Fixes: Resolving Affinity Conflicts and Ensuring Consistent Type Handling

Step 1: Diagnose Affinity Mismatches in View Columns

• Inspect View Definition: Identify all component SELECT statements contributing to the view’s columns. Look for explicit casts (CAST(... AS ...)), implicit casts (via operators like BETWEEN), and collation clauses.
• Check Storage Classes: Use typeof(v0.c1) in test queries to determine the actual storage class of values in the view column:

  SELECT DISTINCT typeof(c1) FROM v0;
  

• Example Findings:
  • rt0.c1 (from the first SELECT) might store BLOB due to the 0x7acfa3c4 insertion
  • CAST(rt0.c0 AS REAL) (from the second SELECT) stores REAL

Step 2: Standardize Affinities Across UNION ALL Components

• Apply Uniform Casting: Ensure all components of the view’s UNION ALL use the same explicit casts for columns involved in comparisons:

  CREATE VIEW v0(c1) AS
    SELECT CAST(rt0.c1 AS REAL) FROM rt0  -- First SELECT
    UNION ALL
    SELECT CAST(rt0.c0 AS REAL) FROM rt0; -- Second SELECT
  

• Use COALESCE for Defaults: Handle NULLs and ambiguous types by providing fallback values:

  CREATE VIEW v0(c1) AS
    SELECT COALESCE(CAST(rt0.c1 AS REAL), 0.0) FROM rt0
    UNION ALL
    SELECT COALESCE(CAST(rt0.c0 AS REAL), 0.0) FROM rt0;
  

Step 3: Normalize Comparison Operands

• Explicitly Cast Literals: Convert literals to match the expected affinity of the view column:

  -- Original: '1674794530' (TEXT) compared with v0.c1 (REAL/BLOB)
  SELECT COUNT(*) FROM v0 
  WHERE CAST('1674794530' AS REAL) BETWEEN v0.c1 AND x'';
  

• Avoid Mixed-Type Comparisons: Use CAST or HEX() to ensure operands have compatible types:

  SELECT COUNT(*) FROM v0 
  WHERE HEX('1674794530') BETWEEN HEX(v0.c1) AND HEX(x'');
  

Step 4: Address Division and Null Handling Ambiguities

• Prevent Division by Zero: Use NULLIF to avoid divisions by zero:

  SELECT COUNT(*) FROM v0 
  WHERE ((...) / NULLIF(+v0.c1, 0)) IS NOT NULL;
  

• Explicit Boolean Checks: Replace implicit boolean conversions with IS TRUE or IS FALSE:

  SELECT COUNT(*) FROM v0 
  WHERE (((...) / (+v0.c1)) IS TRUE);
  

Step 5: Verify Query Consistency with Affinity Adjustments

• Test Simplified Queries: After modifying the view, rerun both original queries to verify consistent results:

  -- Modified COUNT(*) query
  SELECT COUNT(*) FROM v0 
  WHERE ((('1674794530' BETWEEN v0.c1 AND x''))/(+v0.c1)) IS TRUE;
  
  -- Modified SUM(count) query
  SELECT SUM(count) FROM (
    SELECT ((('1674794530' BETWEEN v0.c1 AND x''))/(+v0.c1)) IS TRUE AS count 
    FROM v0
  );
  

• Expected Outcome: Both queries should now return the same result, as the view’s affinity mismatches have been resolved.

Step 6: Consider Schema Redesign for Critical Views

• Materialize Views: Replace virtual views with temporary tables populated via INSERT INTO ... SELECT to "freeze" storage classes:

  CREATE TEMP TABLE v0_temp AS 
  SELECT * FROM v0;
  SELECT COUNT(*) FROM v0_temp WHERE ...;
  

• Avoid UNION ALL for Mixed-Type Data: Split the view into multiple single-affinity views if UNION ALL components cannot be standardized.

Step 7: Monitor SQLite Version-Specific Behavior

• Check Release Notes: Affinity handling and comparison logic may change between versions. Test queries across SQLite 3.46.0, 3.45.1, etc.
• Enable Debugging Pragmas: Use PRAGMA vdbe_debug = 1; to inspect bytecode and verify type conversions during query execution.

By systematically addressing affinity mismatches, normalizing comparison operands, and enforcing explicit type handling, developers can eliminate inconsistencies arising from SQLite’s flexible type system in compound views.