SQLite Integer Column Returns Decimal Due to Generated Column Expression

Understanding the Discrepancy Between Column Type and Stored Decimal Value

The core issue revolves around a generated column defined as INT which unexpectedly stores and returns a decimal value (0.5) despite its integer type declaration. This occurs when inserting a text value containing a decimal into a table with a generated column that references this text column. The fundamental conflict arises from three key aspects of SQLite’s architecture:

1. Type affinity vs storage class dynamics
2. Generated column expression evaluation
3. SQLite’s flexible typing system

A table created with CREATE TABLE t0(c0 INT AS (c1) UNIQUE, c1 TEXT) demonstrates this behavior through its generated column (c0) displaying decimal precision despite being declared as integer. This creates apparent contradictions between the column’s declared type and actual stored value, requiring detailed analysis of SQLite’s type handling mechanics.

Analysis of SQLite’s Type Conversion Mechanics in Generated Columns

1. Column Affinity vs Expression Evaluation

SQLite implements type affinity rather than rigid type enforcement. The INT declaration in column c0 establishes numeric affinity but doesn’t force values to integers. The generated column’s value derives directly from the expression (c1) without implicit casting. Since c1 stores the text value ‘0.5’, the generated column inherits this value’s storage class through direct assignment.

The expression tree for the generated column contains no type conversion nodes. SQLite’s bytecode executor (VDBE) pushes the c1 value directly onto the stack when evaluating c0’s expression. This results in identical type characteristics between c1 and c0 despite their different declared affinities.

2. Insertion Path Analysis

When executing INSERT INTO t0(c1) VALUES (0.5), SQLite processes the value through these stages:

1. Literal interpretation: The 0.5 is initially treated as a floating-point numeric literal
2. Type conversion for TEXT storage: Since c1 is declared as TEXT, the value undergoes conversion to text format ‘0.5’
3. Generated column calculation: The c0 expression reads the TEXT value ‘0.5’ from c1
4. Affinity application: The NUMERIC affinity of c0 attempts to convert ‘0.5’ to numeric form

This creates a paradox where the TEXT column stores a string representation of 0.5, which the generated column then interprets as a numeric value through affinity conversion. The final storage class becomes REAL (floating-point) rather than INTEGER.

3. Affinity Conversion Nuances

SQLite applies type affinity after calculating generated column values. The sequence of operations is:

Expression Result → Apply Column Affinity → Store Value

For the c0 column:

1. Expression evaluates to TEXT ‘0.5’ (from c1)
2. NUMERIC affinity converts TEXT ‘0.5’ to REAL 0.5
3. Resulting REAL value is stored with NUMERIC affinity

This explains why SELECT t0.c0 returns 0.5 instead of converting to integer. The NUMERIC affinity accepts REAL values when no data loss occurs in conversion, unlike INTEGER affinity which would truncate decimals.

Strategies for Enforcing Integer Storage in Generated Columns

1. Explicit Type Casting in Generated Expressions

Modify the generated column definition to include explicit casting:

CREATE TABLE t0(
  c0 INT GENERATED ALWAYS AS (CAST(c1 AS INTEGER)) UNIQUE,
  c1 TEXT
);

CAST operator behavior:

• Converts TEXT ‘0.5’ to INTEGER using SQLite’s casting rules
• Applies numeric conversion before truncation
• 0.5 → 0 (not 1) due to truncation toward zero

Implementation details:

• The CAST expression creates a conversion node in the expression tree
• VDBE opcode Cast is generated for type conversion
• Conversion occurs during row insertion/update, not at query time

2. Strict Tables for Type Enforcement

Enable strict typing using SQLite 3.37+ STRICT tables:

CREATE TABLE t0(
  c0 INTEGER GENERATED ALWAYS AS (c1) UNIQUE,
  c1 TEXT
) STRICT;

Strict mode effects:

• Column c0 now enforces INTEGER type constraints
• Insertion would fail with '0.5' due to type mismatch
• Requires generated expression to produce compatible integer values

Migration considerations:

• Existing tables must be recreated with STRICT keyword
• All columns in strict tables enforce declared types
• Generated columns must match declared type exactly

3. Rounding Functions for Numeric Conversion

Use mathematical functions to force integer conversion:

CREATE TABLE t0(
  c0 INT GENERATED ALWAYS AS (ROUND(c1)) UNIQUE,
  c1 TEXT
);

ROUND function behavior:

• Converts TEXT ‘0.5’ to REAL 0.5
• Applies banker’s rounding (rounds to nearest even number)
• 0.5 → 0 (if even) or 1 (if odd) depending on context

Alternative functions:

• CAST(x AS INTEGER): Truncates decimal portion
• CEIL(x): Rounds up to nearest integer
• FLOOR(x): Rounds down to nearest integer

4. Trigger-Based Validation

Implement insert/update triggers for type enforcement:

CREATE TRIGGER validate_t0_c1 BEFORE INSERT ON t0
BEGIN
  SELECT CASE
    WHEN CAST(NEW.c1 AS INTEGER) != NEW.c1 THEN
      RAISE(ABORT,'Non-integer value in c1')
  END;
END;

Trigger advantages:

• Works with older SQLite versions (<3.37)
• Allows custom error messages
• Can handle complex validation logic

Performance considerations:

• Adds overhead for each insert/update operation
• May conflict with generated column expressions
• Requires careful exception handling

5. Data Type Validation at Insertion

Force integer conversion at insertion time:

INSERT INTO t0(c1) VALUES (CAST(0.5 AS INTEGER));

Insertion-side casting:

• Converts 0.5 to 0 before storing in c1
• Generated column c0 inherits integer value
• Requires application-level data sanitation

Tradeoffs:

• Shifts validation burden to application code
• May lose original data precision
• Requires consistent casting across all insert paths

Comprehensive Decision Matrix for Integer Conversion

Debugging Methodology for Type Mismatches

1. Storage Class Inspection

Use typeof() function to analyze value storage classes:

SELECT
  c0,
  typeof(c0),
  c1,
  typeof(c1)
FROM t0;

Output analysis:

c0 | typeof(c0) | c1 | typeof(c1)
0.5| real       |0.5| text

This reveals:

• c0 stores REAL numbers despite INTEGER affinity
• c1 stores TEXT values as declared

2. Expression Tree Analysis

Use EXPLAIN to examine generated column processing:

EXPLAIN
SELECT c0 FROM t0;

Bytecode inspection:

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    00  Start at 11
1     OpenRead       0     2     0     2              00  root=2 iDb=0; t0
2     Rewind         0     9     0                    00
3       Column         0     0     1                    00  r[1]=t0.c0
4       ResultRow      1     1     0                    00  output=r[1]
5     Next           0     3     0                    01
6     Halt           0     0     0                    00
7     Transaction    0     0     1     0              01  usesStmtJournal=0
8     Goto           0     1     0                    00
9     Close          0     0     0                    00
10    Halt           0     0     0                    00

Key observations:

• No type conversion opcodes present
• Direct column value retrieval

3. Schema Metadata Verification

Query sqlite_schema for table definition:

SELECT sql FROM sqlite_schema WHERE name='t0';

Output verification:

CREATE TABLE t0(c0 INT AS (c1) UNIQUE, c1 TEXT)

Confirms absence of explicit casting in generated column expression.

Best Practices for Generated Column Typing

1. Explicit Conversion: Always use CAST() or conversion functions in generated column expressions when type enforcement is required

2. Strict Typing: Adopt STRICT tables for new development to prevent implicit conversions

3. Validation Layers:

   • Application-level type checks
   • Database triggers for critical columns
   • Pre-insert data sanitation

4. Testing Strategies:

   • Unit tests for boundary values (0.499…, 0.5, 1.0)
   • Type assertion in query results
   • Cross-version compatibility testing

5. Documentation Practices:

   • Clearly annotate generated column expressions
   • Maintain data type conversion matrices
   • Document rounding/truncation policies

Performance Implications of Conversion Methods

1. CAST in Generated Columns

• Overhead: Adds CPU cycles for type conversion during writes
• Indexing: Converted values create optimal indexes
• Storage: Uses less space for integer values vs real numbers

2. STRICT Tables

• Validation Cost: Type checking during insertion
• Storage Efficiency: Guaranteed type-compliant storage
• Error Handling: Immediate failure prevents data corruption

3. Trigger-Based Solutions

• Runtime Cost: Additional trigger execution per write operation
• Flexibility: Complex validation logic possible
• Maintenance: Requires trigger documentation and version control

Migration Paths for Existing Tables

1. Adding Type Safety to Legacy Tables

Step-by-step migration:

-- 1. Create new strict table
CREATE TABLE new_t0(
  c0 INTEGER GENERATED ALWAYS AS (CAST(c1 AS INTEGER)) UNIQUE,
  c1 TEXT
) STRICT;

-- 2. Data migration with validation
INSERT INTO new_t0(c1)
SELECT
  CASE
    WHEN CAST(c1 AS INTEGER) = c1 THEN c1
    ELSE NULL
  END
FROM t0;

-- 3. Data validation
SELECT COUNT(*) FROM t0 WHERE CAST(c1 AS INTEGER) != c1;

-- 4. Replace table
DROP TABLE t0;
ALTER TABLE new_t0 RENAME TO t0;

2. Gradual Type Enforcement

Add check constraints incrementally:

-- Phase 1: Log violations
CREATE TABLE type_violations(
  rowid INTEGER,
  column_name TEXT,
  expected_type TEXT,
  actual_value TEXT
);

CREATE TRIGGER log_c1_violations BEFORE UPDATE ON t0
BEGIN
  INSERT INTO type_violations
  SELECT
    old.rowid,
    'c1',
    'INTEGER',
    new.c1
  WHERE CAST(new.c1 AS INTEGER) != new.c1;
END;

-- Phase 2: Enforce after cleanup
ALTER TABLE t0 ADD CHECK (CAST(c1 AS INTEGER) = c1);

Cross-Database Comparison of Type Handling

Key differentiators:

• SQLite prioritizes storage flexibility over strict typing
• Major databases require explicit generated column type matching
• Conversion behavior varies significantly in strictness

Advanced Topics in SQLite Type Management

1. Type Affinity Hierarchy

SQLite’s type resolution follows specific precedence rules:

1. INT and INTEGER → NUMERIC affinity
2. TEXT, CLOB → TEXT affinity
3. BLOB → no affinity
4. REAL, FLOA, DOUB → REAL affinity

Affinity conversion priorities:

• TEXT: Prefer text representation
• NUMERIC: Convert to INTEGER if possible, else REAL
• INTEGER: Force integer storage
• REAL: Force floating-point storage

2. Storage Class Internals

SQLite uses five primary storage classes:

1. NULL
2. INTEGER (signed 64-bit)
3. REAL (64-bit float)
4. TEXT (UTF-8/16)
5. BLOB (binary data)

Generated columns preserve the storage class of their expression results unless modified by affinity rules.

3. Expression Evaluation Dynamics

The VDBE processes generated column expressions using these key opcodes:

• Column: Retrieve base column value
• Cast: Type conversion when explicit
• Affinity: Apply column type affinity
• ResultRow: Output final value

Absence of Cast and Affinity opcodes in the expression execution leads to direct value propagation.

Conclusion

The apparent anomaly of decimal values in integer columns stems from SQLite’s fundamental design philosophy prioritizing flexibility over strict type enforcement. By comprehensively understanding type affinity, generated column evaluation, and conversion mechanics, developers can implement robust type safety measures through explicit casting, strict tables, and validation layers. The optimal solution combines SQLite’s flexibility with deliberate constraints that match specific application requirements for data integrity and type precision.