github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/ddl/comprimary_key.sql (about)

     1  -- env statement prepare
     2  drop table if exists ex_table_cpk;
     3  drop table if exists cpk_table_1;
     4  drop table if exists cpk_table_1_pk;
     5  drop table if exists cpk_table_2;
     6  drop table if exists cpk_table_3;
     7  drop table if exists cpk_table_3_pk;
     8  drop table if exists cpk_table_4;
     9  drop table if exists cpk_table_5;
    10  drop table if exists cpk_table_6;
    11  drop table if exists cpk_table_7;
    12  drop table if exists cpk_table_8;
    13  drop table if exists cpk_table_9;
    14  drop table if exists cpk_table_10;
    15  drop table if exists cpk_table_11;
    16  drop table if exists cpk_table_42;
    17  drop table if exists cpk_table_43;
    18  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'} ;
    19  
    20  -- 复合主键int+varchar
    21  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));
    22  insert into cpk_table_1 select * from ex_table_cpk;
    23  select col3,col18 from cpk_table_1;
    24  -- 唯一性验证
    25  insert into cpk_table_1 select * from ex_table_cpk;
    26  
    27  -- 复合主键tinyint+datetime+int
    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  -- 唯一性验证
    32  insert into cpk_table_2 select * from ex_table_cpk;
    33  
    34  -- 复合主键smallint+float+timestamp+varchar
    35  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));
    36  insert into cpk_table_3 select * from ex_table_cpk;
    37  select col2,col9,col14,col20 from cpk_table_3;
    38  -- 唯一性验证
    39  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));
    40  -- @bvt:issue#6261
    41  insert into cpk_table_3_pk select * from ex_table_cpk;
    42  -- @bvt:issue
    43  -- 复合主键bigint+tinyint unsigned+DateTime+ decimal+double
    44  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));
    45  insert into cpk_table_4 select * from ex_table_cpk;
    46  select col4,col5,col13,col10,col16 from cpk_table_4;
    47  -- 唯一性验证
    48  -- @bvt:issue#6261
    49  insert into cpk_table_4 select * from ex_table_cpk;
    50  -- @bvt:issue
    51  -- 复合主键smallint unsigned+int unsigned+bigint unsigned+char+bool+varchar
    52  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));
    53  insert into cpk_table_5 select * from ex_table_cpk;
    54  select col6,col7,col8,col18,col16,col19 from cpk_table_5;
    55  -- 唯一性验证
    56  -- @bvt:issue#6261
    57  insert into cpk_table_5 select * from ex_table_cpk;
    58  -- @bvt:issue
    59  show create table cpk_table_5;
    60  
    61  -- 复合主键19个
    62  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));
    63  insert into cpk_table_6 select * from ex_table_cpk;
    64  select * from cpk_table_6;
    65  -- 唯一性验证
    66  -- @bvt:issue#6261
    67  insert into cpk_table_6 select * from ex_table_cpk;
    68  -- @bvt:issue
    69  -- 异常：复合主键列部分不存在
    70  create table cpk_table_7(a int,b float,c char(20),primary key(a,d));
    71  create table cpk_table_8(a int,b float,c char(20),primary key(e,f));
    72  
    73  -- 部分复合主键有空值
    74  create table cpk_table_9(col1 int,col2 varchar(255),col3 timestamp, col4 double,col5 date,primary key(col1, col2,col5));
    75  insert into cpk_table_9 values (3,'','2019-02-10 00:00:00',78.90,'2001-07-10');
    76  insert into cpk_table_9 values (4,'beijing','2019-02-10 00:00:00',78.90,NULL);
    77  
    78  -- 异常：全部有空值
    79  insert into cpk_table_9 values (NULL,NULL,'2019-02-10 00:00:00',78.90,NULL);
    80  
    81  -- 异常：复合主键出现重复字段名
    82  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));
    83  create table cpk_table_10(col1 int,col2 text,col3 double,primary key(col1,col2));
    84  -- load data
    85  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));
    86  load data infile '$resources/external_table_file/ex_table_sep_1.csv' into table cpk_table_11 fields terminated by '|' enclosed by '\"';
    87  
    88  -- insert into select xxx
    89  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));
    90  insert into cpk_table_42  select * from ex_table_cpk;
    91  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));
    92  insert into cpk_table_43 select * from cpk_table_42;
    93  select clo4, col14,col20 from cpk_table_43;
    94  -- insert into values
    95  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");
    96  select clo4, col14,col20 from cpk_table_43;
    97  -- insert into 无复合主键表 select 复合主键表
    98  insert into cpk_table_42 select * from cpk_table_43;
    99  select clo4, col14,col20 from cpk_table_43;
   100  
   101  -- test cases for external table with null values
   102  drop table if exists rawlog_withnull;
   103  CREATE external TABLE rawlog_withnull (
   104  `raw_item` VARCHAR(1024),
   105  `node_uuid` VARCHAR(36),
   106  `node_type` VARCHAR(64),
   107  `span_id` VARCHAR(16),
   108  `statement_id` VARCHAR(36),
   109  `logger_name` VARCHAR(1024),
   110  `timestamp` DATETIME,
   111  `level` VARCHAR(1024),
   112  `caller` VARCHAR(1024),
   113  `message` TEXT,
   114  `extra` JSON,
   115  `err_code` VARCHAR(1024),
   116  `error` TEXT,
   117  `stack` VARCHAR(4096),
   118  `span_name` VARCHAR(1024),
   119  `parent_span_id` VARCHAR(16),
   120  `start_time` DATETIME,
   121  `end_time` DATETIME,
   122  `duration` BIGINT UNSIGNED,
   123  `resource` JSON)
   124  infile{"filepath"='$resources/external_table_file/rawlog_withnull.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n';
   125  select raw_item,node_uuid,node_type,span_id,statement_id,logger_name,timestamp from rawlog_withnull order by 1 limit 1;
   126  drop table if exists rawlog_withnull;