Strict table type constraints are ignored for generated columns

(1) By Bo Lindbergh (_blgl_) on 2025-06-18 12:08:00 [source]

(Found during experimentation inspired by this forum post.)

Reproducing input:

create table odd (
    id integer
        primary key,
    weird real
        not null
        generated always as
            (case id%5
             when 1 then
                 1.0
             when 2 then
                 2
             when 3 then
                 '3x'
             when 4 then
                 x'34'
             else
                 null
             end) stored
) strict;

insert into odd default values;
insert into odd default values;
insert into odd default values;
insert into odd default values;
insert into odd default values;

.mode table --quote
select * from odd;

Unexpected output:

Runtime error near line 25: NOT NULL constraint failed: odd.weird (19)
+----+-------+
| id | weird |
+----+-------+
| 1  | 1.0   |
| 2  | 2.0   |
| 3  | '3x'  |
| 4  | x'34' |
+----+-------+

So not null constraints on generated columns are checked at insertion time, but type constraints imposed by table strictness aren't.

(2.1) By Richard Hipp (drh) on 2025-06-18 16:18:55 edited from 2.0 in reply to 1 [link] [source]

Here is the simplified script, derived from Bo's original, that I am using to debug this issue:

CREATE TABLE strict (
  k INTEGER PRIMARY KEY,
  c1 REAL AS(if(k=11,1.5, k=12,2, k=13,'x', k=14,x'34', 0.0))   STORED,
  c2 INT  AS(if(k=21,1.5, k=22,2, k=23,'x', k=24,x'34', 0))     STORED,
  c3 TEXT AS(if(k=31,1.5, k=32,2, k=33,'x', k=34,x'34', 'x'))   STORED,
  c4 BLOB AS(if(k=41,1.5, k=42,2, k=43,'x', k=44,x'34', x'00')) STORED,
  c5 ANY  AS(if(k=51,1.5, k=52,2, k=53,'x', k=54,x'34', 0))     STORED
) STRICT;
.echo on
INSERT INTO strict(k) VALUES(11);
INSERT INTO strict(k) VALUES(12);
INSERT INTO strict(k) VALUES(13);
INSERT INTO strict(k) VALUES(14);
INSERT INTO strict(k) VALUES(21);
INSERT INTO strict(k) VALUES(22);
INSERT INTO strict(k) VALUES(23);
INSERT INTO strict(k) VALUES(24);
INSERT INTO strict(k) VALUES(31);
INSERT INTO strict(k) VALUES(32);
INSERT INTO strict(k) VALUES(33);
INSERT INTO strict(k) VALUES(34);
INSERT INTO strict(k) VALUES(41);
INSERT INTO strict(k) VALUES(42);
INSERT INTO strict(k) VALUES(43);
INSERT INTO strict(k) VALUES(44);
.mode qbox
SELECT * FROM strict;
PRAGMA integrity_check;

(Edited to show an improved test script)