Issue Overview: ALTER TABLE Integrity Check False Positive on REAL Default Values

When attempting to add a new column with a NOT NULL constraint and a DEFAULT value of REAL affinity (e.g., DEFAULT .25) via ALTER TABLE, SQLite versions 3.42.0 through 3.46.0 may throw a "Runtime error: NOT NULL constraint failed" despite valid schema modifications. This occurs even though the column declaration includes a non-null default value that should populate existing rows automatically. The failure is triggered by an internal call to PRAGMA integrity_check during the ALTER TABLE operation, which incorrectly flags the new column’s data as violating the NOT NULL constraint when the default value has REAL affinity. Integer, TEXT, or BLOB defaults do not exhibit this behavior.

The root cause lies in a specific enhancement to the integrity checker introduced in SQLite 3.42.0. While the ALTER TABLE logic correctly adds the column and applies the default value, the subsequent integrity check misinterprets the stored representation of REAL default values in existing rows. This creates a false corruption report, causing the entire operation to abort. The issue does not affect newly inserted rows after the column is added—only the validation step during the schema alteration itself.

Possible Causes: Integrity Check Misinterpretation of Stored REAL Values

Three interrelated factors contribute to this failure:

1. Internal Representation of REAL Defaults:
   SQLite stores REAL values using IEEE 754 binary64 format. When a new column with a REAL default is added via ALTER TABLE, SQLite writes the default value directly into the database file’s record format without converting it to a string or integer. The integrity checker introduced in version 3.42.0 performs low-level validation of column constraints by inspecting these stored values. A discrepancy arises in how the checker interprets the binary representation of REAL values versus how the SQL engine handles them during normal query execution.

2. Enhancement 8a in SQLite 3.42.0:
   Release 3.42.0 included improvements to the PRAGMA integrity_check command to detect NOT NULL violations in non-ROWID tables (enhancement 8a). This enhancement introduced stricter validation logic that inadvertently fails to account for default REAL values added via ALTER TABLE. The checker mistakenly assumes that the absence of an explicit type declaration for the default value (e.g., DEFAULT 0.25 vs. DEFAULT CAST(0.25 AS REAL)) indicates a missing value, triggering the false NOT NULL violation.

3. ALTER TABLE’s Validation Workflow:
   When executing ALTER TABLE ADD COLUMN, SQLite modifies the table schema in three phases:

   • Schema Update: The table’s SQL schema is updated in the sqlite_schema table.
   • Data Population: Existing rows receive the new column’s default value.
   • Integrity Verification: PRAGMA integrity_check is run to ensure no constraints were violated.
     The third phase exposes the bug because the integrity checker operates at the storage layer, bypassing the SQL engine’s normal handling of defaults. This layer-specific discrepancy causes REAL defaults to be misclassified as NULL during validation.

Troubleshooting Steps, Solutions & Fixes: Workarounds and Patches

Immediate Workarounds

1. Use Explicit Type Annotations:
   Convert REAL defaults to TEXT or INTEGER representations that SQLite will cast back to REAL during queries:

   ALTER TABLE t ADD COLUMN b NOT NULL DEFAULT '0.25'; -- TEXT affinity
   ALTER TABLE t ADD COLUMN b NOT NULL DEFAULT 1/4;     -- INTEGER affinity
   

   This forces the default value to be stored as TEXT or INTEGER, avoiding the integrity checker’s misinterpretation of REAL values. SQLite’s type affinity rules will automatically convert these values to REAL when queried.

2. Temporarily Disable Integrity Checks (Not Recommended for Production):
   If using a scripting layer, disable the integrity check during ALTER TABLE by patching SQLite’s ALTER TABLE routine. This requires modifying the SQLite source code to skip the PRAGMA integrity_check call when adding columns. Example patch:

   // In alter.c, function sqlite3AlterFinishAddColumn():
   -  if( db->flags & SQLITE_InternChanges ){
   -    integrityCheckProblem(pParse, "NOT NULL constraint failed");
   -  }
   +  // Skip integrity check for NOT NULL defaults (temporary workaround)
   

   Warning: This compromises database corruption detection and should only be used for testing.

Long-Term Solutions

1. Upgrade to SQLite 3.47.0 or Later:
   The fix (check-in 460353dfff8f2fb0) corrects the integrity checker’s handling of REAL defaults by refining how stored values are validated against NOT NULL constraints. This fix will be included in all releases after 3.46.0.

2. Backport the Fix to Custom Builds:
   Apply the one-line change from the check-in to your SQLite build:

   // In integrity_check.c, function checkNotNullConstraints():
   -  if( sqlite3_column_type(pStmt, i)!=SQLITE_NULL ) continue;
   +  if( sqlite3_column_type(pStmt, i)!=SQLITE_NULL && sqlite3_column_bytes(pStmt,i)>0 ) continue;
   

   This ensures that columns with stored default values (including REAL) are not falsely flagged as NULL. Recompile SQLite after applying this patch.

Schema Design Adjustments
For databases requiring NOT NULL columns with REAL defaults, pre-initialize the column during table creation or use a view to abstract the default value:

-- Create table without the problematic column
CREATE TABLE t (a);

-- Use a view to simulate the column
CREATE VIEW t_view AS 
SELECT a, 0.25 AS b FROM t WHERE 1; -- Default value enforced at query time

This avoids ALTER TABLE entirely but shifts default handling to the application layer. Use triggers to enforce consistency if updates are required.

Verification Steps
After applying fixes or workarounds, validate the solution:

1. Add the column with a REAL default:

   ALTER TABLE t ADD COLUMN b NOT NULL DEFAULT .25;
   

2. Query the table to confirm default values are populated:

   SELECT a, b, typeof(b) FROM t;
   -- Expected output: 0 | 0.25 | real
   

3. Run PRAGMA integrity_check manually to ensure no errors:

   PRAGMA integrity_check;
   -- Should return "ok"
   

By addressing the integrity checker’s misinterpretation of stored REAL values and adjusting schema modification strategies, this issue can be resolved without compromising data integrity.