github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/comprimary_key.result (about) 1 drop table if exists ex_table_cpk; 2 drop table if exists cpk_table_1; 3 drop table if exists cpk_table_1_pk; 4 drop table if exists cpk_table_2; 5 drop table if exists cpk_table_3; 6 drop table if exists cpk_table_3_pk; 7 drop table if exists cpk_table_4; 8 drop table if exists cpk_table_5; 9 drop table if exists cpk_table_6; 10 drop table if exists cpk_table_7; 11 drop table if exists cpk_table_8; 12 drop table if exists cpk_table_9; 13 drop table if exists cpk_table_10; 14 drop table if exists cpk_table_11; 15 drop table if exists cpk_table_42; 16 drop table if exists cpk_table_43; 17 create external table ex_table_cpk(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255))infile{"filepath"='$resources/external_table_file/cpk_table_1.csv'} fields terminated by ','; 18 create table cpk_table_1(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col3, col18)); 19 insert into cpk_table_1 select * from ex_table_cpk; 20 select col3,col18 from cpk_table_1; 21 col3 col18 22 1 message 23 3 message 24 5 OPQR.STU-_+=VWXYZa 25 3 L/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxyz012 26 insert into cpk_table_1 select * from ex_table_cpk; 27 Duplicate entry ('\(\d\,\w{7}\)'|'\d\w\d{9}\w\d{14}') for key '__mo_cpkey_col' 28 create table cpk_table_2(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col1,col12,col3)); 29 insert into cpk_table_2 select * from ex_table_cpk; 30 select col1,col12,col3 from cpk_table_2; 31 col1 col12 col3 32 1 2020-04-30 1 33 2 2020-02-22 3 34 3 2020-02-16 5 35 3 2020-05-08 3 36 insert into cpk_table_2 select * from ex_table_cpk; 37 Duplicate entry ('\(\d\,\d{4}-\d{2}-\d{2}\,\d\)'|'\d{11}\w\d{5}\w\d{4}') for key '__mo_cpkey_col' 38 create table cpk_table_3(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col2,col9,col14,col20)); 39 insert into cpk_table_3 select * from ex_table_cpk; 40 select col2,col9,col14,col20 from cpk_table_3; 41 col2 col9 col14 col20 42 11 3.4365 1975-09-09 23:59:59 comment balabalabalabalabala 43 20 7.2914 1985-01-12 23:59:59 aaaabbbbbbccccc 44 9 1.1559 2034-02-10 00:00:00 .STU-_+=VWXYZab 45 20 7.2914 1985-01-12 23:59:59 bbbbbbccccc 46 create table cpk_table_3_pk(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col2,col9,col14,col19)); 47 insert into cpk_table_3_pk select * from ex_table_cpk; 48 tae data: duplicate 49 create table cpk_table_4(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col4,col5,col13,col10,col6)); 50 insert into cpk_table_4 select * from ex_table_cpk; 51 select col4,col5,col13,col10,col16 from cpk_table_4; 52 col4 col5 col13 col10 col16 53 2 15 1998-08-07 00:00:00 5.559 602.53 54 4 21 1998-06-04 00:00:00 6.5836 878.09 55 20 1 1998-01-21 23:59:59 6.5635 439.95 56 7 1 1998-12-30 00:00:00 1.1732 428.14 57 insert into cpk_table_4 select * from ex_table_cpk; 58 tae data: duplicate 59 create table cpk_table_5(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 char(255),col19 varchar(255),col20 varchar(255),primary key(col6,col7,col8,col18,col16,col19)); 60 insert into cpk_table_5 select * from ex_table_cpk; 61 select col6,col7,col8,col18,col16,col19 from cpk_table_5; 62 col6 col7 col8 col18 col16 col19 63 600 700 56 message 602.53 s@126.com 64 220 1 3 message 878.09 r@sina.com 65 500 2 4 OPQR.STU-_+=VWXYZa 439.95 U-_+=VWXYZabcdefghigklmno 66 700 600 20 L/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxyz012 428.14 r@sina.com 67 insert into cpk_table_5 select * from ex_table_cpk; 68 tae data: duplicate 69 show create table cpk_table_5; 70 Table Create Table 71 cpk_table_5 CREATE TABLE `cpk_table_5` (\n`col1` TINYINT DEFAULT NULL,\n`col2` SMALLINT DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\n`col5` TINYINT UNSIGNED DEFAULT NULL,\n`col6` SMALLINT UNSIGNED NOT NULL,\n`col7` INT UNSIGNED NOT NULL,\n`col8` BIGINT UNSIGNED NOT NULL,\n`col9` FLOAT DEFAULT NULL,\n`col10` DOUBLE DEFAULT NULL,\n`col11` VARCHAR(255) DEFAULT NULL,\n`col12` DATE DEFAULT NULL,\n`col13` DATETIME DEFAULT NULL,\n`col14` TIMESTAMP DEFAULT NULL,\n`col15` BOOL DEFAULT NULL,\n`col16` DECIMAL(5,2) NOT NULL,\n`col17` TEXT DEFAULT NULL,\n`col18` CHAR(255) NOT NULL,\n`col19` VARCHAR(255) NOT NULL,\n`col20` VARCHAR(255) DEFAULT NULL,\nPRIMARY KEY (`col6`,`col7`,`col8`,`col18`,`col16`,`col19`)\n) 72 create table cpk_table_6(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 char(255),col19 varchar(255),col20 varchar(255),primary key(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col18,col19,col20)); 73 insert into cpk_table_6 select * from ex_table_cpk; 74 select * from cpk_table_6; 75 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 76 1 11 1 2 15 600 700 56 3.4365 5.559 math 2020-04-30 1998-08-07 00:00:00 1975-09-09 23:59:59 true 602.53 abcdefg message s@126.com comment balabalabalabalabala 77 2 20 3 4 21 220 1 3 7.2914 6.5836 art 2020-02-22 1998-06-04 00:00:00 1985-01-12 23:59:59 false 878.09 abcdefg message r@sina.com aaaabbbbbbccccc 78 3 9 5 20 1 500 2 4 1.1559 6.5635 english 2020-02-16 1998-01-21 23:59:59 2034-02-10 00:00:00 true 439.95 EF,GHI,G;KL/MN?OPQR.STU-_+=VWXYZabcdefgh OPQR.STU-_+=VWXYZa U-_+=VWXYZabcdefghigklmno .STU-_+=VWXYZab 79 3 20 3 7 1 700 600 20 7.2914 1.1732 science 2020-05-08 1998-12-30 00:00:00 1985-01-12 23:59:59 false 428.14 U-_+=VWXYZabcdefghigklmnopqrstuvwxy L/MN?OPQR.STU-_+=VWXYZabcdefghigklmnopqrstuvwxyz012 r@sina.com bbbbbbccccc 80 insert into cpk_table_6 select * from ex_table_cpk; 81 tae data: duplicate 82 create table cpk_table_7(a int,b float,c char(20),primary key(a,d)); 83 invalid input: column 'd' doesn't exist in table 84 create table cpk_table_8(a int,b float,c char(20),primary key(e,f)); 85 invalid input: column 'e' doesn't exist in table 86 create table cpk_table_9(col1 int,col2 varchar(255),col3 timestamp, col4 double,col5 date,primary key(col1, col2,col5)); 87 insert into cpk_table_9 values (3,'','2019-02-10 00:00:00',78.90,'2001-07-10'); 88 insert into cpk_table_9 values (4,'beijing','2019-02-10 00:00:00',78.90,NULL); 89 constraint violation: Column 'col5' cannot be null 90 insert into cpk_table_9 values (NULL,NULL,'2019-02-10 00:00:00',78.90,NULL); 91 constraint violation: Column 'col1' cannot be null 92 create table cpk_table_10(col1 tinyint,col2 smallint,col3 int,col4 bigint,col5 tinyint unsigned,col6 smallint unsigned,col7 int unsigned,col8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(col3,col11,col12,col3)); 93 invalid input: duplicate column name 'col3' in primary key 94 create table cpk_table_10(col1 int,col2 text,col3 double,primary key(col1,col2)); 95 not supported: TEXT column 'col2' cannot be in index 96 create table cpk_table_11(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(clo3, col19)); 97 load data infile '$resources/external_table_file/ex_table_sep_1.csv' into table cpk_table_11 fields terminated by '|' enclosed by '\"'; 98 create table cpk_table_42(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255)); 99 insert into cpk_table_42 select * from ex_table_cpk; 100 create table cpk_table_43(clo1 tinyint,clo2 smallint,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255),col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 varchar(255),col19 varchar(255),col20 varchar(255),primary key(clo4, col14,col20)); 101 insert into cpk_table_43 select * from cpk_table_42; 102 select clo4, col14,col20 from cpk_table_43; 103 clo4 col14 col20 104 2 1975-09-09 23:59:59 comment balabalabalabalabala 105 4 1985-01-12 23:59:59 aaaabbbbbbccccc 106 20 2034-02-10 00:00:00 .STU-_+=VWXYZab 107 7 1985-01-12 23:59:59 bbbbbbccccc 108 insert into cpk_table_43 values(8,11,1,9,15,600,700,56,3.4365,5.5590,"math","2020-04-30","1999-08-07 00:00:00","1975-09-09 23:59:59",true,602.53,"abcdefg","message","s@126.com","balabalabalabalabala"); 109 select clo4, col14,col20 from cpk_table_43; 110 clo4 col14 col20 111 2 1975-09-09 23:59:59 comment balabalabalabalabala 112 4 1985-01-12 23:59:59 aaaabbbbbbccccc 113 20 2034-02-10 00:00:00 .STU-_+=VWXYZab 114 7 1985-01-12 23:59:59 bbbbbbccccc 115 9 1975-09-09 23:59:59 balabalabalabalabala 116 insert into cpk_table_42 select * from cpk_table_43; 117 select clo4, col14,col20 from cpk_table_43; 118 clo4 col14 col20 119 2 1975-09-09 23:59:59 comment balabalabalabalabala 120 4 1985-01-12 23:59:59 aaaabbbbbbccccc 121 20 2034-02-10 00:00:00 .STU-_+=VWXYZab 122 7 1985-01-12 23:59:59 bbbbbbccccc 123 9 1975-09-09 23:59:59 balabalabalabalabala 124 drop table if exists rawlog_withnull; 125 CREATE external TABLE rawlog_withnull ( 126 `raw_item` VARCHAR(1024), 127 `node_uuid` VARCHAR(36), 128 `node_type` VARCHAR(64), 129 `span_id` VARCHAR(16), 130 `statement_id` VARCHAR(36), 131 `logger_name` VARCHAR(1024), 132 `timestamp` DATETIME, 133 `level` VARCHAR(1024), 134 `caller` VARCHAR(1024), 135 `message` TEXT, 136 `extra` JSON, 137 `err_code` VARCHAR(1024), 138 `error` TEXT, 139 `stack` VARCHAR(4096), 140 `span_name` VARCHAR(1024), 141 `parent_span_id` VARCHAR(16), 142 `start_time` DATETIME, 143 `end_time` DATETIME, 144 `duration` BIGINT UNSIGNED, 145 `resource` JSON) 146 infile{"filepath"='$resources/external_table_file/rawlog_withnull.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 147 select raw_item,node_uuid,node_type,span_id,statement_id,logger_name,timestamp from rawlog_withnull order by 1 limit 1; 148 raw_item node_uuid node_type span_id statement_id logger_name timestamp 149 error_info 7c4dccb4-4d3c-41f8-b482-5251dc7a41bf ALL 0 0 2022-11-01 11:11:24 150 drop table if exists rawlog_withnull;