github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/table/truncate_table_2.result (about) 1 set time_zone='SYSTEM'; 2 drop table if exists trun_table_01; 3 drop table if exists trun_table_02; 4 drop table if exists trun_table_03; 5 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)); 6 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]}'); 7 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]}'); 8 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]}'); 9 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]}'); 10 select * from trun_table_01; 11 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 12 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 13 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 false 98.23 tttext {"a": "3", "b": [0, 1, 2]} 14 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 false 77.30 tttext {"a": "3", "b": [0, 1, 2]} 15 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 false 209.43 tttext {"a": "3", "b": [0, 1, 2]} 16 truncate table trun_table_01; 17 select * from trun_table_01; 18 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 19 truncate table trun_table_01; 20 select * from trun_table_01; 21 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 22 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]}'); 23 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]}'); 24 select * from trun_table_01; 25 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 26 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 false 54.30 tttext {"a": "3", "b": [0, 1, 2]} 27 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 false 11.43 tttext {"a": "3", "b": [0, 1, 2]} 28 update trun_table_01 set clo3=90 ,col12='2011-01-21' where clo1=1; 29 Duplicate entry '3a155a41170b33cd' for key '__mo_cpkey_004clo3005col12' 30 update trun_table_01 set clo3=66 ,col12='2011-01-21' where clo1=1; 31 select * from trun_table_01; 32 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 33 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 false 11.43 tttext {"a": "3", "b": [0, 1, 2]} 34 1 -2 66 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 false 54.30 tttext {"a": "3", "b": [0, 1, 2]} 35 truncate table trun_table_01; 36 select * from trun_table_01; 37 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 38 delete from trun_table_01 where clo1=2; 39 select * from trun_table_01; 40 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 41 insert into trun_table_01(clo2,clo3,col12) select 36,118,'2002-12-03'; 42 insert into trun_table_01(clo2,clo3,col12) select 45,108,'2012-02-23'; 43 select * from trun_table_01; 44 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 45 1 36 118 null null null null null null null style 2002-12-03 null null null null null null 46 2 45 108 null null null null null null null style 2012-02-23 null null null null null null 47 truncate table trun_table_01; 48 select * from trun_table_01; 49 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 50 drop table trun_table_01; 51 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)); 52 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]}'); 53 select * from trun_table_01; 54 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 55 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 56 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)); 57 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]}'); 58 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]}'); 59 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]}'); 60 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]}'); 61 select * from trun_table_02; 62 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 63 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 64 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 false 98.23 tttext {"a": "3", "b": [0, 1, 2]} 65 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 false 77.30 tttext {"a": "3", "b": [0, 1, 2]} 66 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 false 209.43 tttext {"a": "3", "b": [0, 1, 2]} 67 truncate table trun_table_02; 68 select * from trun_table_02; 69 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 70 delete from trun_table_02 where clo1=1; 71 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]}'); 72 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]}'); 73 select * from trun_table_02; 74 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 75 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 76 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 false 2.43 tttext {"a": "3", "b": [0, 1, 2]} 77 delete from trun_table_02 where clo1=3; 78 select * from trun_table_02; 79 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 80 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 false 2.43 tttext {"a": "3", "b": [0, 1, 2]} 81 truncate table trun_table_02; 82 select * from trun_table_02; 83 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 84 insert into trun_table_02 select * from trun_table_01; 85 select * from trun_table_02; 86 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 87 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 88 update trun_table_02 set clo3=90 where clo1=1; 89 select * from trun_table_02; 90 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 91 1 -2 90 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 92 update trun_table_02 set clo3=90, col12='1992-11-01' where clo1=1; 93 select * from trun_table_02; 94 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 95 1 -2 90 56 9 8 10 50 99.0 82.99 yellllow 1992-11-01 1999-11-11 12:00:00 2010-11-11 11:00:00 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 96 truncate table trun_table_02; 97 select * from trun_table_02; 98 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 99 drop table trun_table_02; 100 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'; 101 select * from trun_table_03; 102 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 103 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 104 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 105 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 106 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 107 truncate table trun_table_03; 108 select * from trun_table_03; 109 clo1 clo2 clo3 clo4 clo5 clo6 clo7 clo8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 110 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 111 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 112 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 113 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 false 23.98 tttext {"a": "3", "b": [0, 1, 2]} 114 truncate table trun_table_03; 115 drop table trun_table_03; 116 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'; 117 drop table trun_table_03; 118 use mo_catalog; 119 truncate table mo_database; 120 internal error: do not have privilege to execute the statement 121 truncate table mo_tables; 122 internal error: do not have privilege to execute the statement 123 truncate table mo_account; 124 internal error: do not have privilege to execute the statement 125 truncate table mo_role; 126 internal error: do not have privilege to execute the statement 127 truncate table mo_user_grant; 128 internal error: do not have privilege to execute the statement 129 truncate table mo_role_grant; 130 internal error: do not have privilege to execute the statement 131 truncate table mo_role_privs; 132 internal error: do not have privilege to execute the statement 133 use system ; 134 truncate table statement_info; 135 internal error: do not have privilege to execute the statement 136 truncate table rawlog; 137 internal error: do not have privilege to execute the statement 138 truncate table log_info; 139 no such table system.log_info 140 truncate table error_info; 141 no such table system.error_info 142 truncate table span_info; 143 no such table system.span_info 144 use information_schema; 145 truncate table key_column_usage ; 146 internal error: do not have privilege to execute the statement 147 truncate table columns; 148 no such table information_schema.columns 149 truncate table profiling; 150 internal error: do not have privilege to execute the statement 151 truncate table user_privileges; 152 internal error: do not have privilege to execute the statement 153 truncate table schemata; 154 no such table information_schema.schemata 155 truncate table character_sets; 156 internal error: do not have privilege to execute the statement 157 truncate table triggers; 158 internal error: do not have privilege to execute the statement 159 truncate table tables; 160 no such table information_schema.tables 161 truncate table engines; 162 internal error: do not have privilege to execute the statement 163 use truncate_table_2; 164 drop table if exists trun_table_01; 165 drop table if exists trun_table_02; 166 drop table if exists trun_table_03;