A corner case of ALTER TABLE ADD COLUMN

(1) By wattroll on 2024-02-13 17:48:02 [source]

The following snippet fails with "Runtime error: NOT NULL constraint failed".

CREATE TABLE t (a);
INSERT INTO t (a) VALUES (0);
ALTER TABLE t ADD COLUMN b NOT NULL DEFAULT .25;
ALTER TABLE t ADD COLUMN c CHECK (1);

Interestingly it only fails when default value for "b" is of REAL affinity. Replacing .25 with some INTEGER, TEXT, or BLOB value seem to be working fine.

I have tested this behaviour against 3.44.2 and 3.46.0.

(2) By Richard Hipp (drh) on 2024-02-13 18:50:29 in reply to 1 [link] [source]

The bug is actually in PRAGMA integrity_check. You are seeing it in ALTER TABLE because ALTER TABLE runs PRAGMA integrity_check after altering the table to make sure it didn't break anything.

The bug is a false-positive detection of corruption by PRAGMA integrity_check. The bug was introduced by enhancement (8a) in release 3.42.0.

The one-line fix can be seen in check-in 460353dfff8f2fb0. That fix will appear in the next release. Or you can run from trunk or from the tip of branch-3.45 if you need an immediate fix.

Thanks for the report.

(3) By wattroll on 2024-02-13 21:42:29 in reply to 2 [link] [source]

Thanks for a very quick fix and detailed explanation. I felt satisfied to understand the cause and effect.