neilconway opened a new issue, #22484:
URL: https://github.com/apache/datafusion/issues/22484
### Describe the bug
### 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_
### To Reproduce
_No response_
### Expected behavior
_No response_
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
