Inconsistent Column Affinity Determination in SQLite: API Gaps and Workarounds

Understanding Column Affinity Mismatches in Schema Definitions and Queries

Issue Overview

Column affinity in SQLite governs how values are stored, compared, and sorted within a database. Unlike rigid data types in other database systems, SQLite uses dynamic typing with affinity hints derived from column declarations. The core issue discussed revolves around the inability to programmatically determine column affinity with reliability equivalent to SQLite’s internal mechanisms, particularly when using CREATE TABLE AS SELECT (CTAS) or interacting with virtual tables. Three critical pain points emerge:

1. Ambiguity in Affinity Determination Rules:
   SQLite’s type affinity rules rely on pattern matching of declared column types. For example, a column declared as ANY may exhibit different affinities across SQLite versions or configurations. In non-STRICT tables, ANY historically defaulted to NUMERIC affinity but shifted to BLOB in recent versions. This shift is undocumented, leaving developers unaware of version-specific behaviors.

2. Parser Idiosyncrasies and Type Name Interpretation:
   The parser’s handling of non-standard type names (e.g., ANY FLOATING POINT) introduces inconsistencies. Consider these declarations:

   CREATE TABLE t1 (a ANY FLOATING POINT); -- Resolves to INTEGER affinity
   CREATE TABLE t2 (a '' ANY FLOATING POINT); -- Resolves to NUMERIC affinity
   CREATE TABLE t3 (a [] ANY FLOATING POINT); -- Resolves to REAL affinity
   

   The presence of non-identifier tokens (string literals, brackets) disrupts affinity determination. Developers cannot replicate this logic externally due to undocumented tokenizer behavior.

3. CTAS vs. Explicit Schema Definitions:
   When materializing a view or query via CREATE TABLE AS SELECT, SQLite assigns affinity based on expression evaluation rather than declared types. For example:

   CREATE VIEW v1 AS SELECT CAST(x AS REAL) FROM tbl;
   CREATE TABLE ctas_tbl AS SELECT * FROM v1; -- Column affinity: REAL
   CREATE TABLE manual_tbl (x REAL); -- Column affinity: REAL (same as CTAS)
   

   While this appears consistent, virtual table wrappers or proxy layers cannot replicate CTAS behavior because they lack access to expression-derived affinity metadata. Existing APIs like sqlite3_column_decltype() fail here because they return the declared type (e.g., CAST(x AS REAL)) rather than the resolved affinity.

The absence of a dedicated API to retrieve expression-derived affinity forces developers to reverse-engineer SQLite’s internal logic—a fragile approach given undocumented parser behaviors and version-specific quirks.

Root Causes of Affinity Determination Instability and Ambiguity

Possible Causes

1. Evolution of Type Affinity Rules Without Backward Compatibility:
   SQLite’s type affinity rules have evolved to accommodate features like STRICT tables. In STRICT mode, ANY is treated as a valid type with BLOB affinity, whereas non-STRICT tables historically assigned NUMERIC affinity. Documentation gaps exacerbate confusion, as developers assume affinity determination is static across table types.

2. Undocumented Tokenizer and Parser Behaviors:
   The parser’s handling of type names involves tokenization rules that are not fully exposed. For instance:

   CREATE TABLE t (a "ANY" FLOATING POINT); -- Type name parsed as "ANY" (literal)
   CREATE TABLE t (a ANY FLOATING POINT); -- Type name parsed as "ANY"
   

   The first declaration treats "ANY" as a string literal, bypassing affinity rules, while the second treats ANY as an identifier. Developers cannot predict this without access to tokenizer internals.

3. Reliance on Unstable Declared Type Strings:
   The sqlite3_column_decltype() function returns the original declaration text, which may include non-standard syntax (e.g., CAST(x AS REAL)). This string cannot be reliably parsed to infer affinity due to:

   • Expression complexity (e.g., nested functions, arithmetic).
   • Version-specific parsing (e.g., handling of ANY in SQLite 3.37 vs. 3.38).
   • Ambiguities in quoted vs. unquoted type names.

4. Undocumented Internal Affinity Assignment Logic:
   SQLite’s internal sqlite3AffinityType() function applies heuristics not fully described in documentation. For example, ANY in non-STRICT tables now maps to BLOB affinity, but this change was not explicitly noted in release notes, leading to silent breaks in applications.

Resolving Affinity Ambiguities: Testing Strategies, Workarounds, and Feature Requests

Troubleshooting Steps, Solutions & Fixes

Step 1: Verify Affinity Using SQLite’s Undocumented affinity() Function

SQLite includes a hidden affinity() function for internal testing. While unsupported, it can temporarily aid diagnostics:

.testctrl internal_functions 1  -- Enable internal functions in CLI
CREATE TABLE t (a ANY);
INSERT INTO t VALUES ('test');
SELECT affinity(a) FROM t;  -- Returns 'blob' in SQLite ≥3.37 with STRICT tables

Caveats:

• This function is unavailable in public API builds.
• Its behavior may change without notice.

Step 2: Replicate CTAS Behavior for Virtual Tables

To mirror CREATE TABLE AS SELECT affinity in virtual tables:

1. Create a shadow table via CTAS and inspect its schema:

   CREATE TABLE proxy_table AS SELECT * FROM target_table WHERE 0;
   PRAGMA table_info(proxy_table);
   

   Extract the inferred affinity from the type field.

2. Use sqlite3_declare_vtab() to define the virtual table schema dynamically.

Step 3: Handle STRICT Table Affinity Changes

For STRICT tables, affinity is fixed to declared types. Migrate non-STRICT schemas by explicitly specifying affinity:

-- Before (non-STRICT)
CREATE TABLE t (a ANY); -- Affinity: BLOB (SQLite ≥3.37)
-- After (STRICT)
CREATE TABLE t (a BLOB) STRICT; -- Explicit affinity

Step 4: Use Pragma Queries to Infer Affinity

The PRAGMA table_xinfo(table) command provides a type field reflecting the declared type. While imperfect, it can be parsed using the following heuristics:

1. Rule 1: If the type contains INT, affinity is INTEGER.
2. Rule 2: If the type contains CHAR, CLOB, or TEXT, affinity is TEXT.
3. Rule 3: If the type contains BLOB or is empty, affinity is BLOB.
4. Rule 4: If the type contains REAL, FLOA, or DOUB, affinity is REAL.
5. Rule 5: Otherwise, affinity is NUMERIC.

Limitation: This does not account for expression-derived affinities (e.g., CTAS).

Step 5: Advocate for a Public sqlite3_column_affinity() API

File a feature request with the SQLite team citing:

• Use cases involving virtual table proxying.
• Inability to replicate CTAS behavior programmatically.
• Risks of reverse-engineering parser logic.

Step 6: Monitor Documentation and Version-Specific Changes

Subscribe to SQLite’s changelog and review commits affecting sqlite3AffinityType(). For example, the bbba322a094b19d9 commit clarified STRICT table behavior.

Step 7: Implement a Custom Affinity Inference Layer

For applications requiring stable affinity detection, implement a parsing layer that:

• Tokenizes sqlite3_column_decltype() output.
• Applies SQLite’s affinity rules while accounting for version differences.
  Example (pseudo-code):

def infer_affinity(decl_type, sqlite_version):
    decl_type = decl_type.upper()
    if 'INT' in decl_type:
        return 'INTEGER'
    elif any(t in decl_type for t in ['CHAR', 'TEXT', 'CLOB']):
        return 'TEXT'
    elif 'BLOB' in decl_type or not decl_type:
        return 'BLOB'
    elif any(t in decl_type for t in ['REAL', 'FLOA', 'DOUB']):
        return 'REAL'
    elif sqlite_version >= (3, 37) and 'ANY' in decl_type:
        return 'BLOB'  # Post-3.37 behavior
    else:
        return 'NUMERIC'

Final Note: Until an official API is available, combining pragma queries, version-specific logic, and CTAS shadow tables offers the most reliable workaround.