PRAGMA table_info Column Type Case Sensitivity Change After SQLite 3.36 to 3.44 Upgrade

Behavioral Shift in PRAGMA table_info Output for Column Types

The upgrade from SQLite 3.36 to 3.44 introduced a non-backward-compatible change in the behavior of the PRAGMA table_info command. Specifically, the case sensitivity of column type names returned by this pragma shifted for standardized SQL data types. Prior to SQLite 3.37.0, the PRAGMA table_info output returned column type names exactly as declared in the CREATE TABLE statement. Starting with SQLite 3.37.0 (released November 27, 2021), standardized type names (e.g., INTEGER, TEXT) are converted to uppercase in the PRAGMA table_info output, even if declared in lowercase or mixed case. This change was implemented to optimize storage efficiency but inadvertently broke unit tests and systems that relied on lowercase type names returned by the pragma.

The discrepancy arises from SQLite’s internal handling of standardized type names. When a column type matches a predefined SQL standard type (e.g., INTEGER, REAL, TEXT, BLOB), SQLite now normalizes the type name to uppercase in the PRAGMA table_info result set. Non-standard or user-defined type names retain their original case. This optimization reduces storage overhead by replacing standardized type strings with internal integer identifiers, which are later resolved to uppercase strings during pragma execution. The change was not explicitly documented in SQLite’s release notes, leading to unexpected test failures during upgrades.

Technical Roots of the PRAGMA table_info Case Normalization

The root cause of this behavioral shift lies in SQLite’s internal type resolution logic, modified in commit d2da62a9 (November 2021). The commit introduced a space-saving optimization where standardized column type names are stored as integer identifiers instead of raw strings. During schema parsing, SQLite checks if a declared column type matches a known standard type (e.g., integer, TEXT, Real). If a match is found, the type name is replaced with an internal eType identifier, and the original string is discarded. When PRAGMA table_info reconstructs the column information, it maps these internal identifiers back to their uppercase standard equivalents, overriding the original declaration’s case.

This optimization impacts backward compatibility because SQLite’s schema introspection pragmas no longer reflect the exact text of CREATE TABLE statements for standardized types. For example:

CREATE TABLE t1 (a INteGEr, b varchar(10));
PRAGMA table_info(t1);

In SQLite ≤3.36.0, this returns INteGEr and varchar(10) for the column types. In SQLite ≥3.37.0, it returns INTEGER (uppercase) and varchar(10) (unchanged, as varchar is non-standard). The change was validated against SQLite’s own test suite, which required updates to accommodate the new behavior. However, external projects relying on the original case-sensitive output were not explicitly warned, leading to undetected dependencies on the pre-3.37.0 behavior.

Mitigating Test Failures and Aligning with SQLite’s Type Handling

To resolve unit test failures and align code with SQLite’s updated behavior, follow these steps:

1. Audit All Uses of PRAGMA table_info and Schema Introspection
   Identify every instance where PRAGMA table_info or similar introspection tools (e.g., sqlite_schema table queries) are used to validate column types. Replace case-sensitive string comparisons with case-insensitive checks or direct affinity matching. For example, instead of:

   assert col_type == "integer"
   

   Use:

   assert col_type.lower() == "integer"
   

2. Normalize Column Type Names in Application Logic
   If the original CREATE TABLE statements use non-standard type names (e.g., STR instead of TEXT), refactor them to use SQL standard types. This ensures consistency with SQLite’s internal normalization. For legacy systems where schema changes are impractical, use a lookup table to map application-specific type names to their standardized equivalents during introspection.

3. Leverage SQLite’s Type Affinity Directly
   SQLite’s type affinity system determines how values are stored and coerced. Instead of relying on the string returned by PRAGMA table_info, derive the affinity programmatically using typeof() or by querying sqlite3_column_type() in the C API. For example:

   SELECT typeof(col1), typeof(col2) FROM t1 LIMIT 1;
   

   This returns the storage class (e.g., integer, text), which is always lowercase and unaffected by the pragma’s case changes.

4. Update Unit Tests to Reflect SQLite’s Guarantees
   SQLite does not guarantee the preservation of type name case in pragmas. Rewrite tests to validate behavioral contracts (e.g., data insertion/retrieval, type coercion) rather than implementation details like type name casing. For example, test that inserting a string into an INTEGER column triggers type affinity rules, rather than asserting the column’s type name is lowercase.

5. Monitor SQLite’s Development and Release Notes Proactively
   Subscribe to SQLite’s changelog and review commits affecting schema introspection. While SQLite prioritizes backward compatibility, optimizations impacting undocumented behaviors can occur. Use the sqlite3_libversion() function or SELECT sqlite_version() query to log the SQLite version in application diagnostics, aiding in post-mortem analysis of compatibility issues.

6. Implement Version-Specific Workarounds If Necessary
   For systems requiring compatibility across multiple SQLite versions, conditionally handle type name casing based on the detected SQLite version:

   import sqlite3
   conn = sqlite3.connect(":memory:")
   sqlite_version = conn.execute("SELECT sqlite_version()").fetchone()[0]
   if sqlite_version >= "3.37.0":
       expected_type = "INTEGER"
   else:
       expected_type = "integer"
   

By aligning application logic with SQLite’s type affinity rules and avoiding dependencies on undocumented pragma behavior, systems can achieve robustness across SQLite versions. The key is to treat PRAGMA table_info as a diagnostic tool rather than a contractual API, recognizing that its output may evolve to reflect internal optimizations.