matrixone [Bug]: after create table as select ，table schema is not consistent with mysql.

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

main

Commit ID

868980a8664bcac9f8d268e0467a720cc810070b

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

情景一：

情景二：

情景三：

情景四：

情景5:

情景六：

情景七：

Expected Behavior

No response

Steps to Reproduce

情景1:
drop table if exists math01;
create table math01 (col1 int default 0, col2 decimal, col3 float, col4 double not null);
insert into math01 values (1, 7382.4324, 432453.3243, -2930.321323);
insert into math01 values (-100, 3283.32324, 328932.0, -9392032);
insert into math01 values (22813, -241, 932342.4324, -0.1);
insert into math01 values (null, null, null, 10);

drop table if exists agg01;
create table agg01 as select avg(col1) as avgCol, sum(col2) as sumcol, count(col3) as countCol, max(col4) as maxCol, min(col4) as minCol from math01;
show create table agg01;
select * from agg01;
drop table agg01;

情景2:
drop table if exists bit01;
create table bit01 (col1 char(1), col2 int);
insert into bit01 values ('a',111),('a',110),('a',100),('a',000),('b',001),('b',011);
select * from bit01;

drop table if exists bit02;
create table bit02 as select bit_and(col2), bit_or(col2), bit_xor(col2) from bit01;
desc bit02;

情景3:
drop table if exists table03;
create table table03(col1 int, col2 char, col3 varchar(10), col4 text, col5 tinyint unsigned, col6 bigint, col7 decimal, col8 float, col9 double);
insert into table03 values (1, 'a', 'database', 'cover all data types', 12, 372743927942, 3232.000, -1489.1231, 72392342);
insert into table03 values (2, 'b', 'table', 'database management system', 1, 324214, 0.0001, 32932.000, -321342.0);
insert into table03 values (null, null, null, null, null, null, null, null, null);
select * from table03;

create table if not exists table04 as select * from table03;
show create table table04;

情景4：
drop table if exists string01;
create table string01 (col1 varchar(40), col2 char, col3 text default null);
insert into string01 values ('  database system', 'a', '云原生数据库');
insert into string01 values (' string function ', '1', '字符串函数');
insert into string01 values ('test create table as select', '0', null);

drop table if exists string02;
create table string02 as select concat_ws(',', col1, 'abcde') as newCol from string01;
show create table string02;


情景5:
drop table if exists table01;
create table table01 (
    id int auto_increment primary key,
    col1 varchar(255) not null ,
    col2 int,
    col3 decimal(10, 2),
    col4 date,
    col5 boolean,
    col6 enum('apple', 'banana', 'orange'),
    col7 text,
    col8 timestamp,
    col9 blob,
    col10 char,
    unique index(col8, col10)
);
insert into table01 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values
('Value2', 456, 78.90, '2023-10-24', false, 'banana', 'Another text', '2022-01-01 01:01:01.000', 'More binary data', 'D'),
('Value3', 789, 12.34, '2023-10-25', true, 'orange', 'Yet another text', '1979-01-01 01:01:01.123', 'Even more binary data', 'E');
create table table02 as select * from table01;
show create table table02;

情景六：
drop table if exists time01;
create table time01(col1 date, col2 datetime, col3 timestamp, col4 time);
insert into time01 values ('2020-10-11', '2023-11-11 10:00:01', '1997-01-13 12:12:12.000', '12:12:12');
insert into time01 values ('1919-12-01', '1990-10-10 01:01:01', '2001-12-12 01:01:01.000', '10:59:59');
insert into time01 values (null, null, null, null);
select * from time01;

drop table if exists time02;
create table time02 as select date_format(col2, '%W %M %Y') from time01;
show create table time02;
desc time02;

情景七：
drop table if exists table04;
drop table if exists table03;
create table table03 (a int primary key, b varchar(5) unique key);
create table table04 (a int ,b varchar(5), c int, foreign key(c) references table03(a));
insert into table03 values (101,'abc'),(102,'def');
insert into table04 values (1,'zs1',101),(2,'zs2',102);

create table if not exists table05 as select * from table04;
show create table table05;
select * from table05;
create table if not exists table06 (d char not null default 'a') as select a from table03;
show create table table06;

Additional information

No response

Mar 05 '24 09:03 Ariznawlll

场景1,2,4,6 都是function相关的问题 create table复用select列的定义，上面这些case都是function作为列，结果与mysql不一致是因为这些function的结果列定义与mysql不一致，如果有必要改的话，应该修改这些函数的结果列定义。
场景3 mysql不允许text类型设置default值，show create table也不打印；mo可以
场景5 enum类型的问题，mo有特殊处理逻辑，修复中
场景7 已知行为，当有default值时，一定不会为NULL，所以not null不会打印出来