Discrepancies in Column Type Reporting with SQLite’s table_info Pragmas

Column Type Reporting Inconsistencies in Views: Declared Type vs. Type Affinity

Issue Overview: Discrepancies in PRAGMA table_info Output for Aliased vs. Cast Columns

When working with SQLite views that involve column aliasing and explicit type casting, developers may encounter inconsistencies in the reported column types via PRAGMA table_info. For example, consider a table t with a column g of a custom type GENDER. A view foo is created with two columns: g1 (an alias of g) and g2 (an explicit cast of g to GENDER). Running PRAGMA table_info(foo) yields conflicting results: g1 is reported as type GENDER, while g2 is reported as NUM. This contradicts the expectation that both columns should reflect the declared type GENDER.

This discrepancy arises from SQLite’s handling of declared types (the type names specified during column creation) versus type affinity (the internal storage class determined by SQLite). SQLite does not enforce strict type checking; instead, it assigns a type affinity to columns based on declared types. Type affinity governs how values are stored and manipulated but does not inherently validate or restrict data types. The PRAGMA table_info command returns declared types for base tables but behaves differently for views, especially when columns are derived from expressions like CAST.

In views, SQLite attempts to infer column metadata from the underlying query. For aliased columns that directly reference a base table column (e.g., g as g1), the declared type from the base table propagates to the view. However, for columns generated via expressions (e.g., CAST(g as GENDER) as g2), SQLite resolves the type affinity based on the cast’s target type. Since GENDER is not a native SQLite type, it defaults to NUMERIC affinity, which is abbreviated as NUM in PRAGMA table_info output. This mismatch between declared types and inferred affinities creates confusion when validating schemas programmatically, as seen in tools like validate_gpkg.py.

Possible Causes: SQLite’s Type Resolution Logic and Metadata Propagation

1. Type Affinity Inference for Non-Standard Types
   SQLite recognizes five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Type affinity is derived from declared types using pattern matching. For example, a declared type containing "INT" (case-insensitive) maps to INTEGER affinity. Non-standard types like GENDER default to NUMERIC affinity. When a column is cast to a non-standard type, SQLite uses this pattern-matching logic to assign affinity, which may not align with the declared type string.

2. Metadata Handling in Views
   Views in SQLite are virtual tables whose schema is dynamically derived from SELECT statements. For columns directly aliased from base tables (e.g., g as g1), SQLite propagates the original declared type. For computed columns (e.g., CAST(g as GENDER) as g2), SQLite infers the type affinity from the expression. The CAST operation explicitly defines a target type, but since GENDER is non-standard, SQLite resolves it to NUMERIC affinity. The PRAGMA table_info output reflects this inferred affinity rather than the declared type string.

3. CAST Operator Behavior
   The CAST(expr AS type) operator in SQLite converts expr to the storage class associated with type. However, the operator does not preserve the declared type string unless type matches a recognized affinity. For example, CAST(g AS INTEGER) would report INTEGER in PRAGMA table_info, but CAST(g AS GENDER) resolves to NUMERIC affinity, reported as NUM. This leads to inconsistencies when comparing aliased columns to cast columns.

Troubleshooting Steps, Solutions & Fixes: Aligning Expectations with SQLite’s Behavior

1. Understand SQLite’s Type Affinity System
   Before relying on PRAGMA table_info, familiarize yourself with how SQLite assigns type affinities:

   • Declared types containing INT → INTEGER affinity.
   • Declared types containing CHAR, CLOB, or TEXT → TEXT affinity.
   • Declared types containing BLOB → BLOB affinity.
   • Declared types containing REAL, FLOA, or DOUB → REAL affinity.
   • All other types → NUMERIC affinity.

   Non-standard types like GENDER or LINESTRING will always resolve to NUMERIC affinity. Tools expecting specific type strings must account for this behavior.

2. Use Standard Type Names for CAST Operations
   To ensure PRAGMA table_info reports consistent types for cast columns, use standard SQLite type names (e.g., TEXT, INTEGER). For example:

   CREATE VIEW foo AS SELECT 
     g as g1, 
     CAST(g AS TEXT) as g2  -- Use standard type
   FROM t;
   

   This ensures g2 is reported as TEXT in PRAGMA table_info. If non-standard types are required, consider adding a comment or documentation to clarify the expected affinity.

3. Validate Schemas Using Alternative Methods
   The PRAGMA table_info command is insufficient for validating non-standard types. Instead:

   • Query sqlite_master to retrieve the original view or table definition:

     SELECT sql FROM sqlite_master WHERE name = 'foo';
     

     This returns the original CREATE VIEW statement, preserving declared types.

   • Use application-level validation to compare column types against expected strings.

4. Modify GeoPackage Validation Logic
   Tools like validate_gpkg.py that enforce GeoPackage standards (which often require specific geometry types like LINESTRING) must avoid relying solely on PRAGMA table_info. Instead:

   • Parse the original CREATE TABLE or CREATE VIEW statements from sqlite_master.
   • Use regular expressions to extract declared types directly from the schema SQL.
   • For views, analyze the underlying SELECT statements to infer types from aliases and casts.

5. Leverage SQLite’s Extension Framework
   For advanced use cases, develop a SQLite extension that overrides type affinity resolution. This allows custom logic for handling non-standard types. However, this approach requires deep integration with SQLite’s internals and is not portable across environments.

6. Educate Stakeholders on SQLite’s Flexibility
   SQLite’s type system prioritizes flexibility over strictness. Document this behavior for teams expecting rigid type enforcement. Emphasize that PRAGMA table_info reflects SQLite’s internal affinity resolution, not necessarily the original declared types.

By aligning validation workflows with SQLite’s type affinity rules and leveraging schema introspection beyond PRAGMA table_info, developers can mitigate inconsistencies and ensure robust compatibility with tools expecting strict type reporting.