PRAGMA table_info Reports Incorrect Data Types for View Columns in SQLite 3.41

Understanding the Discrepancy in Column Type Reporting Between Tables and Views

Issue Overview

A critical regression was identified in SQLite version 3.41 involving the PRAGMA table_info command when used to introspect column data types of views. Specifically, columns in views derived from tables with non-native type declarations (e.g., datetime) were incorrectly reported as NUM instead of retaining the original declared type. This behavior deviated from prior SQLite versions (e.g., 3.39.0, 3.40.x), where the declared type was preserved in views.

Example Scenario:

1. A table foo is created with columns a TEXT and b DATETIME.
2. A view foo_view is defined as SELECT a, b FROM foo ORDER BY a.
3. PRAGMA table_info('foo') correctly reports b as datetime.
4. PRAGMA table_info('foo_view') in SQLite 3.41 erroneously reports b as NUM, whereas earlier versions retained datetime.

Impact:

• Introspection tools or ORM libraries relying on PRAGMA table_info to infer schema details for views broke unexpectedly.
• Applications expecting consistent type metadata between tables and views faced runtime errors or misconfigurations.

Root Observations:

• SQLite does not enforce strict data types; instead, it uses type affinity (TEXT, NUMERIC, INTEGER, REAL, BLOB). Declared types (e.g., datetime) are hints for affinity determination.
• The regression in 3.41 altered how views propagate declared types via PRAGMA table_info, prioritizing affinity over the original declaration.

Investigating the Causes of Type Misreporting in Views

Possible Causes

1. Changes in Type Affinity Resolution Logic

   • SQLite 3.41 introduced modifications to how column types are inferred for views. Previously, views propagated the declared type of the underlying table’s columns. The new logic instead derived the type affinity from the column’s expression in the view definition, discarding the original declared type.
   • Example: datetime (affinity: NUMERIC) in the table was reported as NUM in the view due to affinity derivation, whereas earlier versions preserved datetime as a "user-defined" type string.

2. Inconsistent Handling of Explicit Casts

   • Explicit casts in view definitions (e.g., CAST(b AS datetime)) were ignored in type reporting. Even when developers attempted to enforce the type via CAST, PRAGMA table_info continued to report the affinity (NUM) instead of the cast type.
   • This suggested a disconnect between SQLite’s internal type resolution for expressions and the metadata exposed by PRAGMA table_info.

3. Divergence Between table_info and sqlite3_column_decltype

   • The sqlite3_column_decltype API (used by tools like APSW) correctly reported the original declared type (datetime) for view columns, while PRAGMA table_info did not. This inconsistency indicated that the pragma’s metadata extraction logic was not aligned with other introspection mechanisms.

4. Version-Specific Regression

   • The issue was traced to specific changes in SQLite’s trunk around January 2023 (e.g., check-in [27655c9353620aa5]). These changes inadvertently altered how the CREATE VIEW command stored column type information in the schema, prioritizing affinity over declared types.

Resolving Column Type Misreporting in Views

Troubleshooting Steps, Solutions & Fixes

Step 1: Verify SQLite Version and Apply Patches

• Confirm the SQLite version using sqlite3 --version or runtime APIs.
• If using a version between 3.41.0 (initial release) and check-in [497a98363fd1ed07], upgrade to a fixed version (3.41.0+ with the patch).
• Fix Confirmation: After applying the patch, PRAGMA table_info for views will report the original declared type (e.g., datetime) instead of affinity (NUM).

Step 2: Use sqlite3_column_decltype for Accurate Type Metadata

• For programmatic introspection, prefer sqlite3_column_decltype over PRAGMA table_info when querying views.

  sqlite3_stmt *stmt;
  sqlite3_prepare_v2(db, "SELECT * FROM foo_view", -1, &stmt, NULL);
  const char *decltype = sqlite3_column_decltype(stmt, 1);  // Returns "datetime"
  

• Advantage: This API reflects the declared type from the underlying table or explicit casts, bypassing the pragma’s affinity-based reporting.

Step 3: Modify View Definitions to Enforce Declared Types

• Use explicit CAST expressions in views to force type propagation. While this did not resolve the issue pre-patch, post-patch it ensures compatibility:

  CREATE VIEW foo_view2 AS SELECT a, CAST(b AS datetime) AS b FROM foo;  
  

• Post-Patch Behavior: PRAGMA table_info('foo_view2') will report b as datetime.

Step 4: Understand SQLite’s Type Affinity System

• Native Affinities: TEXT, NUMERIC, INTEGER, REAL, BLOB.
• Declared Types: SQLite parses column declarations to determine affinity (e.g., DATETIME → NUMERIC). Tools may still need to map affinity to application-specific types.
• Best Practice: Design schemas using native affinity keywords (e.g., TEXT instead of VARCHAR) to avoid ambiguity.

Step 5: Update Introspection Logic for Views

• Prefer querying sqlite_schema or using SELECT * FROM pragma_table_info('view_name') (requires SQLite 3.16+) for programmatic access.
• Example using sqlite_schema:

  SELECT sql FROM sqlite_schema WHERE name = 'foo_view';  
  -- Parsing the view definition may reveal original column types
  

Step 6: Regression Testing for Future SQLite Upgrades

• Implement unit tests that validate PRAGMA table_info output for critical views after upgrades.
• Example test case:

  def test_view_column_types():
      conn = sqlite3.connect(':memory:')
      conn.execute('CREATE TABLE foo (a TEXT, b DATETIME)')
      conn.execute('CREATE VIEW foo_view AS SELECT a, b FROM foo')
      cursor = conn.execute("PRAGMA table_info('foo_view')")
      columns = {row[1]: row[2] for row in cursor.fetchall()}
      assert columns['b'] == 'datetime', f"Unexpected type: {columns['b']}"
  

Step 7: Community and Version-Specific Workarounds

• For environments where upgrading SQLite is not immediately feasible:
  • Use a combination of PRAGMA table_info and sqlite3_column_decltype to cross-validate column types.
  • Override type mappings in application code (e.g., treat NUM as datetime if the column name is b).

Conclusion:
The regression in SQLite 3.41 highlighted the delicate balance between type affinity resolution and metadata reporting. By understanding the interplay of declared types, affinity derivation, and introspection APIs, developers can ensure robust schema management across SQLite versions. Always validate metadata strategies against the latest SQLite releases and prefer APIs that align with your type-reporting requirements.