Column Affinity Change in UNION ALL Queries After SQLite Package Update

Understanding Column Type Shifts from REAL to NUMERIC in UNION ALL Operations

Issue Overview: Column Affinity Determination in UNION ALL Contexts

When utilizing UNION ALL operations in SQLite to combine results from multiple SELECT statements, the resulting column’s data type affinity may shift unexpectedly depending on the SQLite version or driver implementation. This behavior becomes evident when migrating between versions of the SQLite engine or client libraries, such as the System.Data.SQLite.Core NuGet package. In the observed scenario, a table created via CREATE TABLE AS SELECT (CTAS) with UNION ALL transitions from exhibiting REAL affinity for the unified column to NUMERIC affinity after updating the SQLite package. This shift impacts how values are stored, retrieved, and processed, particularly when interacting with floating-point numbers or integers that require precise type handling.

The core challenge lies in SQLite’s dynamic type system, where column affinity—not rigid data types—governs how values are stored and coerced. Affinity is derived from the column’s declared type during table creation. For UNION ALL operations, SQLite determines the unified column’s affinity by analyzing the affinities of the contributing columns from each SELECT statement. A mismatch between expected and actual affinity can lead to unintended storage optimizations (e.g., converting small floating-point values to integers) or discrepancies in application-level data handling.

Possible Causes: Drivers, Core Engine Updates, and Affinity Inference Rules

1. SQLite Core Engine Version Changes:
   The System.Data.SQLite.Core package bundles specific versions of the native SQLite library. Updates to this library may introduce modifications to affinity inference rules for UNION ALL operations. For example, SQLite 3.35.0 or later might enforce stricter affinity resolution, prioritizing NUMERIC over REAL when multiple affinities could apply, even if individual SELECT statements explicitly cast to REAL.

2. CAST Expression Handling in UNION Contexts:
   While CAST(ColumnName AS REAL) explicitly sets the affinity of a column in a single SELECT statement, UNION ALL operations may override this affinity if the underlying engine determines that a broader affinity (e.g., NUMERIC) is necessary to accommodate potential data variations across UNION-ed queries. This behavior could be exacerbated if the engine optimizes away redundant casts or prioritizes storage efficiency over declared types.

3. Driver-Level Affinity Reporting Discrepancies:
   The .NET SQLite provider might alter how it retrieves or interprets column affinity information via PRAGMA table_info. A driver update could introduce changes in type name normalization (e.g., truncating "REAL" to "NUM" for NUMERIC affinity) or affinity-to-type mapping logic, creating the illusion of a type shift without an actual change in storage semantics.

Troubleshooting Steps, Solutions & Fixes: Diagnosing and Enforcing Column Affinity

Step 1: Isolate the Issue to SQLite Core or Driver Implementation
Execute the provided schema script directly against the SQLite command-line shell, using the same core version as the problematic .NET package. Compare the output of PRAGMA table_info(ResultTable) between environments. If the declared type remains REAL in the shell but appears as NUMERIC in the .NET application, the driver is likely altering type reporting. If both environments show NUMERIC, the issue stems from SQLite’s core behavior.

Step 2: Explicitly Define Column Affinity During Table Creation
Avoid relying on affinity inference during CTAS operations. Instead, predefine the table schema with the desired affinity before inserting data:

CREATE TABLE ResultTable (ResultColumn REAL);  
INSERT INTO ResultTable  
SELECT ColumnName FROM SourceTable1  
UNION ALL  
SELECT ColumnName FROM SourceTable2;  

This approach bypasses affinity resolution during UNION ALL by anchoring the column’s affinity to the predefined schema.

Step 3: Leverage Outer Casts to Override Affinity Inference
Wrap the UNION ALL query in an outer SELECT that reapplies the CAST operation, forcing the CTAS to adopt the specified affinity:

CREATE TABLE ResultTable AS  
SELECT CAST(CombinedColumn AS REAL) AS ResultColumn FROM (  
  SELECT ColumnName AS CombinedColumn FROM SourceTable1  
  UNION ALL  
  SELECT ColumnName FROM SourceTable2  
);  

This nested structure ensures the outermost SELECT’s CAST dictates the column’s declared type, overriding any intermediate affinity deductions.

Step 4: Monitor SQLite Core Updates and Driver Release Notes
Review changelogs for the SQLite core library and the System.Data.SQLite.Core package to identify intentional affinity handling changes. For example, SQLite might optimize CAST operations in UNION contexts if redundant, necessitating manual affinity enforcement.

Step 5: Validate Data Storage Semantics
Confirm that the affinity shift does not corrupt data by inserting test values (e.g., integers, floats with fractional components) and verifying their storage format using HEX() or typeof(). For NUMERIC affinity, integers may be stored as INTEGER to save space, but REAL affinity ensures they remain as floats.

Final Fix: Schema-Explicit Design Over Inference
Prioritize explicit column affinity declarations in CREATE TABLE statements over reliance on SELECT-driven schema inference. This practice eliminates ambiguity and ensures consistent behavior across SQLite versions and drivers. When UNION ALL is unavoidable, encapsulate it within subqueries that apply definitive CAST operations at the outermost layer to anchor affinity expectations.