Storing Hexadecimal String ‘0E06111638718900’ as Zero in SQLite: Type Affinity and Conversion Pitfalls

Hexadecimal String Truncation Due to Implicit Numeric Conversion

Understanding the Core Problem: String-to-Number Conversion in Type-Affinity Scenarios

The problem arises when attempting to store the hexadecimal string 0E06111638718900 in a SQLite database column declared with non-standard or ambiguous type affinities (e.g., STRING). Instead of persisting the full string, the value is truncated to 0. This occurs due to SQLite’s type affinity system, which triggers implicit conversion rules when the column’s declared type is not strictly TEXT. The string 0E... is interpreted as a numeric literal in scientific notation (0E+...), which evaluates to zero. This conversion is irreversible and leads to data loss when the column’s affinity permits numeric interpretation.

The hexadecimal string 0E06111638718900 contains the substring 0E at its start, which aligns with the syntax of numeric literals in exponential notation. SQLite’s type affinity rules prioritize numeric conversion for columns with NUMERIC or REAL affinities. When the column is declared as STRING (a non-standard type in SQLite), the database engine defaults to NUMERIC affinity. This creates a mismatch between the intended storage (textual hexadecimal data) and the actual storage (numeric conversion). The result is silent data truncation, where only the numeric interpretation (0) is stored.

Root Causes: Declared Column Types, Type Affinity, and String Parsing

The issue is rooted in three interconnected factors:

1. Non-Standard Column Type Declaration
   The use of STRING as a column type is invalid in SQLite. SQLite supports five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and assigns type affinity based on declared column types. Declaring a column as STRING does not map to the TEXT affinity. Instead, SQLite applies heuristic rules to determine affinity. For STRING, the parser identifies the substring STR (matching REAL’s ST and RE patterns), leading to NUMERIC affinity. This triggers automatic conversion of inserted values to numeric types when possible.

2. Numeric Literal Interpretation
   The string 0E06111638718900 matches the pattern of a numeric literal in exponential notation:

   • 0 is the significand (the base number).
   • E (or e) denotes the exponent.
   • 06111638718900 is interpreted as the exponent value.
     Numerically, 0E<digits> evaluates to 0 because any number multiplied by zero remains zero. SQLite’s parser converts this string to the numeric value 0 during insertion when the column has NUMERIC affinity.

3. Absence of Quoting or Binding Parameters
   If the value is inserted without enclosing quotes (e.g., INSERT INTO test VALUES (0E06111638718900)), SQLite treats it as a numeric literal. However, the original discussion specifies that quotes were used (VALUES ('0E06111638718900')). In this case, the quotes should preserve the string, but the column’s NUMERIC affinity overrides this, forcing conversion.

Resolution: Enforcing Text Storage Through Schema Design and Binding Practices

To prevent unintended numeric conversion and ensure hexadecimal strings are stored verbatim, implement the following fixes:

1. Explicitly Declare Columns with TEXT Affinity
   Redefine the column using SQLite’s recognized TEXT type:

   CREATE TABLE test (key TEXT);  -- Correct affinity for textual data
   

   This ensures all inserted values retain their original form without conversion.

2. Validate Insertion Quoting and Parameter Binding
   When inserting values:

   • Use single quotes for literals:

     INSERT INTO test (key) VALUES ('0E06111638718900');
     

   • If using parameterized queries (e.g., in Python, Java), explicitly bind the value as a string:

     cursor.execute("INSERT INTO test (key) VALUES (?)", ('0E06111638718900',))
     

3. Audit Existing Data and Schema Migrations
   For existing tables with incorrect affinities:

   • Create a new table with TEXT columns:

     CREATE TABLE test_new (key TEXT);
     

   • Copy data while enforcing textual representation:

     INSERT INTO test_new (key) SELECT CAST(key AS TEXT) FROM test;
     

   • Drop the old table and rename the new one:

     DROP TABLE test;
     ALTER TABLE test_new RENAME TO test;
     

4. Disable Type Affinity Overrides
   Avoid using non-standard or ambiguous type declarations like STRING, VARCHAR, or CHAR. Stick to TEXT for textual data.

5. Use CHECK Constraints for Format Validation
   To enforce hexadecimal formatting, add a constraint:

   CREATE TABLE test (
       key TEXT CHECK (key GLOB '[0-9A-Fa-f]*')
   );
   

   This rejects non-hexadecimal strings at insertion time.

6. Leverage BLOB for Binary Data
   If storing raw bytes (e.g., hash digests), use BLOB affinity:

   CREATE TABLE test (key BLOB);
   

   Insert values as byte arrays:

   cursor.execute("INSERT INTO test (key) VALUES (?)", (bytes.fromhex('0E06111638718900'),))
   

By addressing the interplay between SQLite’s type affinity rules and hexadecimal string formatting, this guide ensures robust storage of textual data without unintended conversions.