sql: null-value constraint is not enforced in CREATE TABLE ... AS (values)

[sajjadrizvi]

The problem

A PK column can have a null value when a table is created using CREATE TABLE ... AS (values).

To Reproduce

BEGIN; CREATE TABLE foo (x PRIMARY KEY) AS VALUES (1), (NULL); END;

SELECT * FROM foo results in:

   x
--------
  NULL
     1
(2 rows)

I tested this using current master branch.

Expected behavior
The query should not succeed.

[sajjadrizvi]

In addition, no jobs are created:

SELECT count(*) FROM [SHOW JOBS];
  count
---------
      0
(1 row)

[nvanbenschoten]

Thanks for the report @sajjadrizvi!

I was able to reproduce this. However, I can only reproduce this when running the CREATE TABLE statement in an explicit transaction:

root@127.0.0.1:26257/movr> CREATE TABLE foo (x PRIMARY KEY) AS VALUES (1), (NULL);
ERROR: generate insert row: null value in column "x" violates not-null constraint
SQLSTATE: 23502

root@127.0.0.1:26257/movr> BEGIN; CREATE TABLE foo (x PRIMARY KEY) AS VALUES (1), (NULL); END;
COMMIT

[sajjadrizvi]

I can only reproduce this when running the CREATE TABLE statement in an explicit transaction

Yes, I have also noticed that.

[ajwerner]

This is a @cockroachdb/sql-schema bug I think as we own the initial schema change. It's funny that these two are different cases.