[Enhancement] Let "create table as ... select" statement could insert data which query from select statement

[lide-reed]

Search before asking

• I had searched in the issues and found no similar issues.

Description

Now in Doris, the statement "create table as ... select" only create table and can not insert data, it is not comply with the statement's real semantic logic.

Solution

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[stalary]

Assign it to me. I did insert into select before, and I was only halfway done. I will perfect him in the near future

[long2ice]

In addition, the columns of created table can be same as origin? Because if I create table with list partition, the error always occur, since the columns of created table are always null able

[HY000][1105] errCode = 2, detailMessage = Failed to execute CREATE TABLE AS SELECT Reason: errCode = 2, detailMessage = The list partition column must be NOT NULL

[stalary]

In addition, the columns of created table can be same as origin? Because if I create table with list partition, the error always occur, since the columns of created table are always null able

[HY000][1105] errCode = 2, detailMessage = Failed to execute CREATE TABLE AS SELECT Reason: errCode = 2, detailMessage = The list partition column must be NOT NULL

create table is fully reusable and please post your complete SQL.

[long2ice]

origin table:

CREATE TABLE `country_codes` (
  `country_code` varchar(2) NOT NULL COMMENT "",
  `country_name` varchar(32) NOT NULL COMMENT "",
  `calling_code` int(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`country_code`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`country_code`, `country_name`, `calling_code`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2"
)

create table:

create table `gaia_test`.`test_incremental` PARTITION BY LIST (country_code)(PARTITION p1 values in ("CA","GB","US","ZH")) PROPERTIES ("replication_num" = "1") as select
*
from gaia_test.country_codes;

[stalary]

The field was not copied not null. I will fix it later. @long2ice

if (!ConnectContext.get().getSessionVariable().isAllowPartitionColumnNullable()
                            && columnDef.isAllowNull()) {
                        throw new AnalysisException("The partition column must be NOT NULL");
                    }

[long2ice]

OK