`CREATE TABLE AS` should not inherit nullability

[neilconway]

Describe the bug

CREATE TABLE AS SELECT ... converts non-nullable properties of the SELECT into not-nullable constraints on the columns of the newly created table. This is defensible, but I think it's surprising, for two reasons:

1. "nullable=false" in the query just says that the optimizer can prove that the query doesn't contain any nullable values in that column. That is distinct from saying that the newly created table ought to semantically have a NOT NULL constraint. You can imagine plenty of scenarios where the initial values of a table are populated with CREATE TABLE AS SELECT ... that doesn't have any NULL values, but then subsequent DML on the table expects to be able to insert NULLs.
2. Whether an expression in a query is not-nullable depends on the vaguaries of query optimization, which change between releases. Adding this instability to the output of CREATE TABLE AS SELECT ... seems undesirable.

The current behavior is also incompatible with Postgres and DuckDB, although that shouldn't necessarily be determinative.

To Reproduce

> CREATE OR REPLACE TABLE t(a int) AS SELECT 1 FROM VALUES (5);
0 row(s) fetched.
Elapsed 0.012 seconds.

> \d t
+---------------+--------------+------------+-------------+-----------+-------------+
| table_catalog | table_schema | table_name | column_name | data_type | is_nullable |
+---------------+--------------+------------+-------------+-----------+-------------+
| datafusion    | public       | t          | a           | Int32     | NO          |
+---------------+--------------+------------+-------------+-----------+-------------+
1 row(s) fetched.
Elapsed 0.010 seconds.

Expected behavior

No response

Additional context

No response

[neilconway]

take