For query:

CREATE TABLE v0 ( v1);
INSERT INTO v0 VALUES ( 255);
ALTER TABLE v0 ADD COLUMN v3 AS ( NULL) NOT NULL;
SELECT "-----";
SELECT * FROM v0 WHERE v3 NOT IN ( SELECT count ( * ) FROM v0);
/* Expected return: NULL. Actual return  255| */

In the ALTER TABLE statement, we add column v3 with default value NULL and constraints NOT NULL. We understand that this is not a conventional behavior, because document ALTER TABLE has noted that If a NOT NULL constraint is specified, then the column must have a default value other than NULL. However, the ALTER TABLE statement doesn't throw any errors, and the NULL column triggers unexpected output with unmatched condition in the following SELECT statement.

We do notice that SQLite apply NOT NULL constraints check when we INSERT data into the column, such as the following case:

CREATE TABLE v0 (v1, v2 AS (NULL) NOT NULL);
INSERT INTO v0 VALUES (255);
/* Error: NOT NULL constraint failed: v0.v2 */

However, if possible, we would also suggest to add a NOT NULL constraint check in the ALTER TABLE statement or in the SELECT statement, in order to prevent the unexpected behavior from the first query.

Looking forward to your reply.

Your statement:

ALTER TABLE v0 ADD COLUMN v3 AS ( NULL) NOT NULL;

Adds a virtual generated/computed at access time column with a value of NULL and adds a NOT NULL constraint. There is no "default" value.

If you add a column with a default of null and a not null constraint, then the ALTER TABLE generates your expected error message:

sqlite> alter table v0 add column v3 not null;
Error: Cannot add a NOT NULL column with default value NULL
sqlite> alter table v0 add column v3 default null not null;
Error: Cannot add a NOT NULL column with default value NULL

SQLite3 is merely carrying out what you requested it to do. SQLite3 does not make a guess as to whether or not what you have commanded is sensible but rather only produces an error if you have given a command that is obviously flawed.

Thank you for the correction!

In this case, is the output row from the first query expected?

Assuming v3 is being calculated as NULL, we replaced v3 into NULL in the WHERE clause of the first query and observe this:

CREATE TABLE v0 ( v1);
INSERT INTO v0 VALUES ( 255);
ALTER TABLE v0 ADD COLUMN v3 AS ( NULL) NOT NULL;
SELECT "-----";
SELECT * FROM v0 WHERE NULL NOT IN ( SELECT count ( * ) FROM v0);
/* Return NULL */

This is expected but mismatched with the original outputs.

This will always be False thus the result will always be no rows.

WHERE NULL NOT IN ( SELECT count ( * ) FROM v0);

WHERE x IN (a,b,c,d,...z)
means
x == a or x == b or x == c or x == d or ... x == z

However, if the value of x is null (as in this case) then no matter what there is no value of a that can make NULL == a evaluate true, because all operations against null have a null result (or false, if in the where clause).

sqlite> select null in (null);
┌────────────────┐
│ null in (null) │
├────────────────┤
│                │
└────────────────┘

Check-in e3794997c34f03db enhances the ALTER TABLE ADD COLUMN command so that (if necessary) it will run the equivalent of "PRAGMA quick_check" on the modified table after adding the new column in order to ensure that any NOT NULL or CHECK constraints added as part of the new column are satisfied by existing rows of the table. If any constraints are violated, the ADD COLUMN is aborted and the changes are rolled back.

The existing documentation for ALTER TABLE ADD COLUMN says that when adding a CHECK constraint....

"the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added."

The "future versions of SQLite" mentioned in this remark means check-in e3794997c34f03db and later. And this applies to NOT NULL constraints on generated columns as well. The documentation will be updated for the next release (3.37.0).

Thank you for the reference to the documentation, and thank you for the enhanced check in the ADD COLUMN. After the enhancement, column v3 is not being added to the table, and hence the unexpected outputs are prohibited in the original first query.