github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/table/truncate_table_2.sql (about) 1 set time_zone='SYSTEM'; 2 --env prepare statement 3 drop table if exists trun_table_01; 4 drop table if exists trun_table_02; 5 drop table if exists trun_table_03; 6 7 create table trun_table_01(clo1 tinyint AUTO_INCREMENT,clo2 smallint not null,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255) default 'style',col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 json,primary key(clo3,col12),unique key uk1 (col16),key k1 (clo1)); 8 insert into trun_table_01 values (1,-2,3,56,9,8,10,50,99.0,82.99,'yellllow','1999-11-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,23.98430943,'tttext','{"a": "3","b": [0,1,2]}'); 9 insert into trun_table_01 values (2,-2,90,56,9,8,10,50,99.0,82.99,'yellllow','2011-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,98.23,'tttext','{"a": "3","b": [0,1,2]}'); 10 insert into trun_table_01 values (3,-2,100,56,9,8,10,50,99.0,82.99,'yellllow','2021-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,77.3,'tttext','{"a": "3","b": [0,1,2]}'); 11 insert into trun_table_01 values (4,-2,102,56,9,8,10,50,99.0,82.99,'yellllow','2022-10-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,209.43,'tttext','{"a": "3","b": [0,1,2]}'); 12 select * from trun_table_01; 13 truncate table trun_table_01; 14 select * from trun_table_01; 15 truncate table trun_table_01; 16 select * from trun_table_01; 17 insert into trun_table_01(clo2 ,clo3 ,clo4,clo5,clo6,clo7,clo8 ,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18) values (-2,3,56,9,8,10,50,99.0,82.99,'yellllow','1999-11-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,54.3,'tttext','{"a": "3","b": [0,1,2]}'); 18 insert into trun_table_01(clo2 ,clo3 ,clo4,clo5,clo6,clo7,clo8 ,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18) values (-2,90,56,9,8,10,50,99.0,82.99,'yellllow','2011-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,11.43,'tttext','{"a": "3","b": [0,1,2]}'); 19 select * from trun_table_01; 20 -- @bvt:issue#7133 21 update trun_table_01 set clo3=90 ,col12='2011-01-21' where clo1=1; 22 update trun_table_01 set clo3=66 ,col12='2011-01-21' where clo1=1; 23 select * from trun_table_01; 24 -- @bvt:issue 25 truncate table trun_table_01; 26 select * from trun_table_01; 27 delete from trun_table_01 where clo1=2; 28 select * from trun_table_01; 29 insert into trun_table_01(clo2,clo3,col12) select 36,118,'2002-12-03'; 30 insert into trun_table_01(clo2,clo3,col12) select 45,108,'2012-02-23'; 31 select * from trun_table_01; 32 truncate table trun_table_01; 33 select * from trun_table_01; 34 drop table trun_table_01; 35 create table trun_table_01(clo1 tinyint AUTO_INCREMENT,clo2 smallint not null,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255) default 'style',col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 json,primary key(clo3,col12),unique key uk1 (col16),key k1 (clo1)); 36 insert into trun_table_01 values (1,-2,3,56,9,8,10,50,99.0,82.99,'yellllow','1999-11-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,23.98430943,'tttext','{"a": "3","b": [0,1,2]}'); 37 select * from trun_table_01; 38 39 -- @bvt:issue#9124 40 create temporary table trun_table_02(clo1 tinyint AUTO_INCREMENT,clo2 smallint not null,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255) default 'style',col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 json,primary key(clo3,col12),unique key uk1 (col16),key k1 (clo1)); 41 insert into trun_table_02 values (1,-2,3,56,9,8,10,50,99.0,82.99,'yellllow','1999-11-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,23.98430943,'tttext','{"a": "3","b": [0,1,2]}'); 42 insert into trun_table_02 values (2,-2,90,56,9,8,10,50,99.0,82.99,'yellllow','2011-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,98.23,'tttext','{"a": "3","b": [0,1,2]}'); 43 insert into trun_table_02 values (3,-2,100,56,9,8,10,50,99.0,82.99,'yellllow','2021-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,77.3,'tttext','{"a": "3","b": [0,1,2]}'); 44 insert into trun_table_02 values (4,-2,102,56,9,8,10,50,99.0,82.99,'yellllow','2022-10-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,209.43,'tttext','{"a": "3","b": [0,1,2]}'); 45 select * from trun_table_02; 46 truncate table trun_table_02; 47 select * from trun_table_02; 48 delete from trun_table_02 where clo1=1; 49 insert into trun_table_02 values (3,-2,100,56,9,8,10,50,99.0,82.99,'yellllow','2021-01-21','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,23.98430943,'tttext','{"a": "3","b": [0,1,2]}'); 50 insert into trun_table_02 values (4,-2,102,56,9,8,10,50,99.0,82.99,'yellllow','2022-10-11','1999-11-11 12:00:00','2010-11-11 11:00:00.00',false,2.43,'tttext','{"a": "3","b": [0,1,2]}'); 51 select * from trun_table_02; 52 53 delete from trun_table_02 where clo1=3; 54 select * from trun_table_02; 55 56 truncate table trun_table_02; 57 select * from trun_table_02; 58 insert into trun_table_02 select * from trun_table_01; 59 select * from trun_table_02; 60 61 update trun_table_02 set clo3=90 where clo1=1; 62 select * from trun_table_02; 63 update trun_table_02 set clo3=90, col12='1992-11-01' where clo1=1; 64 select * from trun_table_02; 65 66 truncate table trun_table_02; 67 select * from trun_table_02; 68 drop table trun_table_02; 69 -- @bvt:issue 70 71 create external table trun_table_03(clo1 tinyint AUTO_INCREMENT,clo2 smallint not null,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255) default 'style',col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 json)infile{"filepath"='$resources/external_table_file/trun_table.csv'} fields terminated by '|' lines terminated by '\n'; 72 select * from trun_table_03; 73 truncate table trun_table_03; 74 select * from trun_table_03; 75 truncate table trun_table_03; 76 drop table trun_table_03; 77 create external table trun_table_03(clo1 tinyint AUTO_INCREMENT,clo2 smallint not null,clo3 int,clo4 bigint,clo5 tinyint unsigned,clo6 smallint unsigned,clo7 int unsigned,clo8 bigint unsigned,col9 float,col10 double,col11 varchar(255) default 'style',col12 Date,col13 DateTime,col14 timestamp,col15 bool,col16 decimal(5,2),col17 text,col18 json)infile{"filepath"='$resources/external_table_file/trun_table.csv'} fields terminated by '|' lines terminated by '\n'; 78 drop table trun_table_03; 79 80 -- accesscontrol 81 use mo_catalog; 82 truncate table mo_database; 83 truncate table mo_tables; 84 truncate table mo_account; 85 truncate table mo_role; 86 truncate table mo_user_grant; 87 truncate table mo_role_grant; 88 truncate table mo_role_privs; 89 use system ; 90 truncate table statement_info; 91 truncate table rawlog; 92 truncate table log_info; 93 truncate table error_info; 94 truncate table span_info; 95 use information_schema; 96 truncate table key_column_usage ; 97 truncate table columns; 98 truncate table profiling; 99 truncate table user_privileges; 100 truncate table schemata; 101 truncate table character_sets; 102 truncate table triggers; 103 truncate table tables; 104 truncate table engines; 105 use truncate_table_2; 106 drop table if exists trun_table_01; 107 drop table if exists trun_table_02; 108 drop table if exists trun_table_03; 109 110 111 112 113 114