ALTER TABLE on STRICT Table Fails Adding REAL Column with Decimal Default

Understanding the Failure of NOT NULL Constraints During REAL Column Addition in STRICT Tables

Issue Overview
The problem arises when attempting to add a new REAL column with a decimal default value (e.g., 0.5) to a non-empty STRICT table in SQLite versions 3.45.0 and 3.45.1. The operation triggers a NOT NULL constraint failed error, even though the column definition explicitly includes a DEFAULT clause. This behavior is inconsistent with earlier SQLite versions (e.g., 3.39.4), where the same operation succeeds. The failure occurs exclusively under these conditions:

1. The target table is declared with the STRICT keyword.
2. The table contains existing rows at the time of the ALTER TABLE ... ADD COLUMN operation.
3. The new column is of type REAL and has a non-integer default value (e.g., 0.5 instead of 1).

The error indicates that SQLite is failing to apply the default value to preexisting rows during the column addition process. This contradicts expectations because the DEFAULT clause should automatically populate the new column for all existing rows. The inconsistency between integer and decimal defaults (e.g., 1 works, 0.5 fails) suggests a type-handling issue specific to REAL columns in STRICT tables.

Root Causes: Type Affinity Mismatches and STRICT Mode Enforcement

The failure stems from two interconnected factors:

1. Type Affinity Enforcement in STRICT Tables
   In STRICT tables, SQLite enforces rigorous type checking. Columns reject values that do not exactly match the declared type. For example, inserting a TEXT value into an INTEGER column in a STRICT table will fail. When adding a new column via ALTER TABLE, SQLite must populate existing rows with the default value. If the default value does not strictly conform to the column’s declared type affinity, the operation fails.

   However, the anomaly here is that 0.5 is a valid REAL value, and REAL columns should accept it. This implies a deeper issue in how default values are processed during column addition in STRICT tables.

2. Default Value Representation in Schema Alterations
   SQLite’s ALTER TABLE command does not rewrite existing rows immediately. Instead, it updates the table schema and applies defaults on-the-fly during subsequent SELECT or INSERT operations. When a new column is added with a DEFAULT, SQLite stores the default value as a literal in the schema. For existing rows, the value is computed dynamically when accessed.

   In STRICT tables, this dynamic computation may inadvertently trigger type checks before the value is cast to the column’s type. For example, if the default expression 0.5 is initially interpreted as a NUMERIC literal (not strictly REAL), the STRICT table rejects it during row access, causing the NOT NULL constraint failure.

3. Version-Specific Regression
   The error does not occur in SQLite 3.39.4, indicating a regression in versions 3.45.0 and 3.45.1. This regression likely relates to changes in how default values are stored or evaluated during schema alterations. The commit referenced in the forum discussion (298d6977285c71be) addresses this by ensuring default values are correctly coerced to the column’s type affinity before being applied to existing rows.

Resolution: Validating Defaults, Adjusting Queries, and Applying Patches

Step 1: Confirm the SQLite Version and STRICT Table Configuration
Verify the SQLite version using sqlite3 --version. If the version is 3.45.0 or 3.45.1, the bug is present. Next, confirm that the table uses STRICT mode by inspecting its schema:

SELECT sql FROM sqlite_schema WHERE name = 'test';  

The output should include the STRICT keyword.

Step 2: Test with Integer vs. Decimal Defaults
To isolate the issue, attempt adding columns with different default value types:

ALTER TABLE test ADD COLUMN test1 REAL NOT NULL DEFAULT 1;      -- Succeeds  
ALTER TABLE test ADD COLUMN test2 REAL NOT NULL DEFAULT 0.5;    -- Fails  

The failure of the second command confirms the decimal default is mishandled.

Step 3: Evaluate Default Value Representation
In STRICT tables, explicitly cast default values to the column’s type to ensure compatibility:

ALTER TABLE test ADD COLUMN test2 REAL NOT NULL DEFAULT CAST(0.5 AS REAL);  

This forces SQLite to treat the default as a REAL literal, bypassing type inference ambiguities.

Step 4: Temporarily Disable STRICT Mode (If Feasible)
If immediate fixes are needed and data integrity allows, recreate the table without STRICT:

PRAGMA foreign_keys = OFF;  
BEGIN TRANSACTION;  
CREATE TABLE new_test(id INTEGER PRIMARY KEY, test1 REAL NOT NULL DEFAULT 1, test2 REAL NOT NULL DEFAULT 0.5);  
INSERT INTO new_test SELECT * FROM test;  
DROP TABLE test;  
ALTER TABLE new_test RENAME TO test;  
COMMIT;  
PRAGMA foreign_keys = ON;  

This workaround avoids the ALTER TABLE bug by rebuilding the table.

Step 5: Apply the Official Fix
The bug is resolved in SQLite versions containing the commit 298d6977285c71be. Upgrade to a patched version (3.45.2 or newer) where available. Confirm the fix by re-running the ALTER TABLE command with decimal defaults.

Step 6: Adjust Schema Design Practices
To prevent similar issues:

• Avoid mixing STRICT tables with ALTER TABLE operations that add columns requiring non-integer defaults.
• Predefine all columns upfront where possible.
• Use CAST in default expressions to enforce type affinity explicitly.

Final Validation
After applying fixes, validate the schema and data:

SELECT id, test2 FROM test;  -- Should return 0.5 for all rows  
PRAGMA table_info(test);     -- Confirm test2 is REAL with DEFAULT 0.5  

By methodically addressing type enforcement rules and applying upstream patches, the NOT NULL constraint failed error during column addition is fully resolvable.