SQLite Forum

Unexpected return from the NULL column
Login
For query:

```SQL
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](https://www.sqlite.org/lang_altertable.html) 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:

```SQL
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.