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.

So we're saying in the original version, that since v3 is calculated as NULL, that the "v3 NOT IN..." should therefore evaluate to NULL, and thus the record should not pass the WHERE clause. But somehow it is.

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.