github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/table/cluster_table/cluster_table.result (about) 1 drop database if exists test_db1; 2 create database test_db1; 3 use test_db1; 4 drop table if exists t1; 5 create cluster table t1(a int, b int); 6 internal error: do not have privilege to execute the statement 7 drop database test_db1; 8 use mo_task; 9 drop table if exists t2; 10 internal error: do not have privilege to execute the statement 11 create cluster table t2(a int, b int); 12 internal error: do not have privilege to execute the statement 13 use information_schema; 14 drop table if exists t3; 15 internal error: do not have privilege to execute the statement 16 create cluster table t3(a int, b int); 17 internal error: do not have privilege to execute the statement 18 desc t3; 19 no such table information_schema.t3 20 drop table t3; 21 internal error: do not have privilege to execute the statement 22 use mysql; 23 drop table if exists t4; 24 internal error: do not have privilege to execute the statement 25 create cluster table t4(a int, b int); 26 internal error: do not have privilege to execute the statement 27 desc t4; 28 no such table mysql.t4 29 drop table t4; 30 internal error: do not have privilege to execute the statement 31 use system_metrics; 32 drop table if exists t5; 33 internal error: do not have privilege to execute the statement 34 create cluster table t5(a int, b int); 35 internal error: do not have privilege to execute the statement 36 desc t5; 37 no such table system_metrics.t5 38 drop table t5; 39 internal error: do not have privilege to execute the statement 40 use system; 41 drop table if exists t6; 42 internal error: do not have privilege to execute the statement 43 create cluster table t6(a int, b int); 44 internal error: do not have privilege to execute the statement 45 desc t6; 46 no such table system.t6 47 drop table t6; 48 internal error: do not have privilege to execute the statement 49 use mo_catalog; 50 drop table if exists t7; 51 create cluster table t7(a int, b int); 52 desc t7; 53 Field Type Null Key Default Extra Comment 54 a INT(32) YES null 55 b INT(32) YES null 56 account_id INT UNSIGNED(32) NO null the account_id added by the mo 57 drop table t7; 58 use mo_catalog; 59 drop table if exists cluster_table_1; 60 create cluster table cluster_table_1(a int, b int); 61 drop table if exists statement_cu; 62 CREATE CLUSTER TABLE `statement_cu` ( 63 `statement_id` VARCHAR(36) NOT NULL, 64 `account` VARCHAR(300) NOT NULL, 65 `response_at` DATETIME DEFAULT NULL, 66 `cu` DECIMAL(23,3) NOT NULL, 67 PRIMARY KEY (`statement_id`,`account_id`) 68 ); 69 drop account if exists test_account1; 70 create account test_account1 admin_name = 'test_user' identified by '111'; 71 drop account if exists test_account2; 72 create account test_account2 admin_name = 'test_user' identified by '111'; 73 insert into cluster_table_1 values(0,0,0),(1,1,0); 74 insert into cluster_table_1 values(0,0,1),(1,1,1); 75 insert into cluster_table_1 values(0,0,2),(1,1,2) on duplicate key update b=b; 76 update cluster_table_1 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 77 update cluster_table_1 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 78 select a,b from cluster_table_1; 79 a b 80 0 0 81 1 1 82 0 0 83 1 1 84 0 0 85 1 1 86 use mo_catalog; 87 select * from cluster_table_1; 88 a b 89 0 0 90 1 1 91 SELECT attname AS name, mo_show_visible_bin(atttyp,3) AS data_type, replace(mo_table_col_max(att_database,att_relname,attname),'\\0', '') AS `maximum`, mo_table_col_min(att_database,att_relname,attname) as minimum from mo_catalog.mo_columns where att_database='mo_catalog' and att_relname='statement_cu' and attname NOT IN ('__mo_rowid', '__mo_cpkey_col', '__mo_fake_pk_col') ORDER BY attnum; 92 name data_type maximum minimum 93 statement_id VARCHAR(36) null null 94 account VARCHAR(300) null null 95 response_at DATETIME(0) null null 96 cu DECIMAL128(23) null null 97 account_id INT UNSIGNED(32) null null 98 use mo_catalog; 99 select * from cluster_table_1; 100 a b 101 0 0 102 1 1 103 SELECT attname AS name, mo_show_visible_bin(atttyp,3) AS data_type, replace(mo_table_col_max(att_database,att_relname,attname),'\\0', '') AS `maximum`, mo_table_col_min(att_database,att_relname,attname) as minimum from mo_catalog.mo_columns where att_database='mo_catalog' and att_relname='statement_cu' and attname NOT IN ('__mo_rowid', '__mo_cpkey_col', '__mo_fake_pk_col') ORDER BY attnum; 104 name data_type maximum minimum 105 statement_id VARCHAR(36) null null 106 account VARCHAR(300) null null 107 response_at DATETIME(0) null null 108 cu DECIMAL128(23) null null 109 account_id INT UNSIGNED(32) null null 110 insert into cluster_table_1 values(200,200, 0); 111 insert into cluster_table_1 values(100,100, 0); 112 insert into cluster_table_1 values(50,50, 0); 113 select a,b from cluster_table_1; 114 a b 115 0 0 116 1 1 117 0 0 118 1 1 119 0 0 120 1 1 121 200 200 122 100 100 123 50 50 124 use mo_catalog; 125 select * from cluster_table_1; 126 a b 127 0 0 128 1 1 129 use mo_catalog; 130 select * from cluster_table_1; 131 a b 132 0 0 133 1 1 134 drop table cluster_table_1; 135 drop table if exists cluster_table_2; 136 create cluster table cluster_table_2( 137 col1 int, 138 col2 float, 139 col3 decimal, 140 col4 date, 141 col5 bool, 142 col6 json, 143 col7 blob, 144 col8 text, 145 col9 varchar 146 ); 147 load data infile '$resources/load_data/cluster_table.csv' into table cluster_table_2 fields terminated by ','; 148 update cluster_table_2 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 149 update cluster_table_2 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 150 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_2; 151 col1 col2 col3 col4 col5 col6 col7 col8 col9 152 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 153 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 154 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 155 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 156 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 157 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 158 use mo_catalog; 159 select * from cluster_table_2; 160 col1 col2 col3 col4 col5 col6 col7 col8 col9 161 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 162 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 163 use mo_catalog; 164 select * from cluster_table_2; 165 col1 col2 col3 col4 col5 col6 col7 col8 col9 166 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 167 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 168 drop table cluster_table_2; 169 drop table if exists cluster_table_3; 170 create cluster table cluster_table_3( 171 col1 int, 172 col2 float, 173 col3 decimal, 174 col4 date, 175 col5 bool, 176 col6 json, 177 col7 blob, 178 col8 text, 179 col9 varchar 180 ); 181 insert into cluster_table_3 values (1,1.09,1.345,"2022-10-02",0,'{"a":1}',"你好","text","varchar", 0); 182 insert into cluster_table_3 values (1,1.09,1.345,"2022-10-02",0,'{"a":1}',"你好","text","varchar", 1); 183 insert into cluster_table_3 values (1,1.09,1.345,"2022-10-02",0,'{"a":1}',"你好","text","varchar", 2); 184 insert into cluster_table_3 values (2,10.9,13.45,"2022-10-02",1,'{"b":2}',"nihao","文本","字符", 0); 185 insert into cluster_table_3 values (2,10.9,13.45,"2022-10-02",1,'{"b":2}',"nihao","文本","字符", 1); 186 insert into cluster_table_3 values (2,10.9,13.45,"2022-10-02",1,'{"b":2}',"nihao","文本","字符", 2); 187 update cluster_table_3 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 188 update cluster_table_3 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 189 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 190 col1 col2 col3 col4 col5 col6 col7 col8 col9 191 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 192 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 193 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 194 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 195 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 196 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 197 update cluster_table_3 set col1=100 where account_id=0 and col1=1; 198 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 199 col1 col2 col3 col4 col5 col6 col7 col8 col9 200 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 201 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 202 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 203 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 204 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 205 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 206 use mo_catalog; 207 select * from cluster_table_3; 208 col1 col2 col3 col4 col5 col6 col7 col8 col9 209 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 210 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 211 use mo_catalog; 212 select * from cluster_table_3; 213 col1 col2 col3 col4 col5 col6 col7 col8 col9 214 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 215 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 216 update cluster_table_3 set col1=100 where account_id=(select account_id from mo_account where account_name="test_account1") and col1=1; 217 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 218 col1 col2 col3 col4 col5 col6 col7 col8 col9 219 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 220 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 221 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 222 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 223 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 224 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 225 use mo_catalog; 226 select * from cluster_table_3; 227 col1 col2 col3 col4 col5 col6 col7 col8 col9 228 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 229 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 230 use mo_catalog; 231 select * from cluster_table_3; 232 col1 col2 col3 col4 col5 col6 col7 col8 col9 233 1 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 234 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 235 update cluster_table_3 set col1=100 where account_id=(select account_id from mo_account where account_name="test_account2") and col1=1; 236 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 237 col1 col2 col3 col4 col5 col6 col7 col8 col9 238 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 239 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 240 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 241 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 242 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 243 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 244 use mo_catalog; 245 select * from cluster_table_3; 246 col1 col2 col3 col4 col5 col6 col7 col8 col9 247 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 248 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 249 use mo_catalog; 250 select * from cluster_table_3; 251 col1 col2 col3 col4 col5 col6 col7 col8 col9 252 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 253 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 254 delete from cluster_table_3 where account_id=0; 255 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 256 col1 col2 col3 col4 col5 col6 col7 col8 col9 257 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 258 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 259 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 260 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 261 use mo_catalog; 262 select * from cluster_table_3; 263 col1 col2 col3 col4 col5 col6 col7 col8 col9 264 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 265 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 266 use mo_catalog; 267 select * from cluster_table_3; 268 col1 col2 col3 col4 col5 col6 col7 col8 col9 269 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 270 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 271 delete from cluster_table_3 where account_id=(select account_id from mo_account where account_name="test_account1"); 272 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 273 col1 col2 col3 col4 col5 col6 col7 col8 col9 274 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 275 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 276 use mo_catalog; 277 select * from cluster_table_3; 278 col1 col2 col3 col4 col5 col6 col7 col8 col9 279 use mo_catalog; 280 select * from cluster_table_3; 281 col1 col2 col3 col4 col5 col6 col7 col8 col9 282 2 10.9 13 2022-10-02 true {"b": 2} nihao 文本 字符 283 100 1.09 1 2022-10-02 false {"a": 1} ä½ å¥½ text varchar 284 delete from cluster_table_3 where account_id=(select account_id from mo_account where account_name="test_account2"); 285 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 286 col1 col2 col3 col4 col5 col6 col7 col8 col9 287 use mo_catalog; 288 select * from cluster_table_3; 289 col1 col2 col3 col4 col5 col6 col7 col8 col9 290 use mo_catalog; 291 select * from cluster_table_3; 292 col1 col2 col3 col4 col5 col6 col7 col8 col9 293 truncate table cluster_table_3; 294 select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3; 295 col1 col2 col3 col4 col5 col6 col7 col8 col9 296 use mo_catalog; 297 select * from cluster_table_3; 298 col1 col2 col3 col4 col5 col6 col7 col8 col9 299 use mo_catalog; 300 select * from cluster_table_3; 301 col1 col2 col3 col4 col5 col6 col7 col8 col9 302 drop table cluster_table_3; 303 create cluster table cluster_table_xx(account_id int); 304 invalid input: the attribute account_id in the cluster table can not be defined directly by the user 305 drop table if exists cluster_table_4; 306 create cluster table cluster_table_4( 307 col1 int, 308 col2 varchar 309 ); 310 insert into cluster_table_4 values (1,'a',0),(2,'b',0); 311 insert into cluster_table_4 values (1,'a',1),(2,'b',1); 312 insert into cluster_table_4 values (1,'a',2),(2,'b',2); 313 update cluster_table_4 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 314 update cluster_table_4 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 315 select col1,col2 from cluster_table_4; 316 col1 col2 317 1 a 318 2 b 319 1 a 320 2 b 321 1 a 322 2 b 323 use mo_catalog; 324 desc cluster_table_4; 325 Field Type Null Key Default Extra Comment 326 col1 INT(32) YES null 327 col2 VARCHAR(65535) YES null 328 account_id INT UNSIGNED(32) NO null the account_id added by the mo 329 show create table cluster_table_4; 330 Table Create Table 331 cluster_table_4 CREATE CLUSTER TABLE `cluster_table_4` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(65535) DEFAULT NULL\n) 332 drop table cluster_table_4; 333 internal error: do not have privilege to execute the statement 334 use mo_catalog; 335 insert into cluster_table_4 values (3, 'c'); 336 internal error: only the sys account can insert/update/delete the cluster table 337 update cluster_table_4 set col1=10 where col2='a'; 338 internal error: only the sys account can insert/update/delete the cluster table 339 delete from cluster_table_4 where col1=2; 340 internal error: only the sys account can insert/update/delete the cluster table 341 truncate table cluster_table_4; 342 internal error: only the sys account can truncate the cluster table 343 drop table cluster_table_4; 344 drop table if exists cluster_table_5; 345 create cluster table cluster_table_5( 346 col1 int, 347 col2 varchar 348 ); 349 insert into cluster_table_5 values (1,'a',0),(2,'b',0),(3,'c',0),(4,'d',0),(5,'f',0),(6,'g',0); 350 insert into cluster_table_5 values (1,'a',1),(2,'b',1),(3,'c',1),(4,'d',1),(5,'f',1),(6,'g',1); 351 insert into cluster_table_5 values (1,'a',2),(2,'b',2),(3,'c',2),(4,'d',2),(5,'f',2),(6,'g',2); 352 update cluster_table_5 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 353 update cluster_table_5 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 354 drop table if exists cluster_table_6; 355 create cluster table cluster_table_6( 356 a int, 357 b varchar 358 ); 359 insert into cluster_table_6 values (100,'a',0),(200,'a',0),(300,'a',0); 360 insert into cluster_table_6 values (100,'a',1),(200,'a',1),(300,'a',1); 361 insert into cluster_table_6 values (100,'a',2),(200,'a',2),(300,'a',2); 362 update cluster_table_6 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 363 update cluster_table_6 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 364 select a1.col1,a1.col2,a2.a,a2.b from cluster_table_5 a1 left join cluster_table_6 a2 on a1.col2=a2.b; 365 col1 col2 a b 366 1 a 100 a 367 1 a 200 a 368 1 a 300 a 369 1 a 100 a 370 1 a 200 a 371 1 a 300 a 372 1 a 100 a 373 1 a 200 a 374 1 a 300 a 375 2 b null null 376 3 c null null 377 4 d null null 378 5 f null null 379 6 g null null 380 1 a 100 a 381 1 a 200 a 382 1 a 300 a 383 1 a 100 a 384 1 a 200 a 385 1 a 300 a 386 1 a 100 a 387 1 a 200 a 388 1 a 300 a 389 2 b null null 390 3 c null null 391 4 d null null 392 5 f null null 393 6 g null null 394 1 a 100 a 395 1 a 200 a 396 1 a 300 a 397 1 a 100 a 398 1 a 200 a 399 1 a 300 a 400 1 a 100 a 401 1 a 200 a 402 1 a 300 a 403 2 b null null 404 3 c null null 405 4 d null null 406 5 f null null 407 6 g null null 408 select a1.col1,a1.col2,a2.a,a2.b from cluster_table_5 a1 right join cluster_table_6 a2 on a1.col2=a2.b; 409 col1 col2 a b 410 1 a 100 a 411 1 a 100 a 412 1 a 100 a 413 1 a 200 a 414 1 a 200 a 415 1 a 200 a 416 1 a 300 a 417 1 a 300 a 418 1 a 300 a 419 1 a 100 a 420 1 a 100 a 421 1 a 100 a 422 1 a 200 a 423 1 a 200 a 424 1 a 200 a 425 1 a 300 a 426 1 a 300 a 427 1 a 300 a 428 1 a 100 a 429 1 a 100 a 430 1 a 100 a 431 1 a 200 a 432 1 a 200 a 433 1 a 200 a 434 1 a 300 a 435 1 a 300 a 436 1 a 300 a 437 select a1.col1,a1.col2,a2.a,a2.b from cluster_table_5 a1 inner join cluster_table_6 a2 on a1.col2=a2.b; 438 col1 col2 a b 439 1 a 100 a 440 1 a 200 a 441 1 a 300 a 442 1 a 100 a 443 1 a 200 a 444 1 a 300 a 445 1 a 100 a 446 1 a 200 a 447 1 a 300 a 448 1 a 100 a 449 1 a 200 a 450 1 a 300 a 451 1 a 100 a 452 1 a 200 a 453 1 a 300 a 454 1 a 100 a 455 1 a 200 a 456 1 a 300 a 457 1 a 100 a 458 1 a 200 a 459 1 a 300 a 460 1 a 100 a 461 1 a 200 a 462 1 a 300 a 463 1 a 100 a 464 1 a 200 a 465 1 a 300 a 466 select col1,col2 from cluster_table_5 union select a,b from cluster_table_6; 467 col1 col2 468 100 a 469 200 a 470 300 a 471 1 a 472 2 b 473 3 c 474 4 d 475 5 f 476 6 g 477 select col1,col2 from cluster_table_5 union all select a,b from cluster_table_6; 478 col1 col2 479 100 a 480 200 a 481 300 a 482 100 a 483 200 a 484 300 a 485 100 a 486 200 a 487 300 a 488 1 a 489 2 b 490 3 c 491 4 d 492 5 f 493 6 g 494 1 a 495 2 b 496 3 c 497 4 d 498 5 f 499 6 g 500 1 a 501 2 b 502 3 c 503 4 d 504 5 f 505 6 g 506 select col1,col2 from cluster_table_5 intersect select a,b from cluster_table_6; 507 col1 col2 508 SELECT col1,col2 FROM cluster_table_5 MINUS SELECT a,b FROM cluster_table_6; 509 col1 col2 510 1 a 511 2 b 512 3 c 513 4 d 514 5 f 515 6 g 516 SELECT a,b FROM cluster_table_6 MINUS SELECT col1,col2 FROM cluster_table_5; 517 a b 518 100 a 519 200 a 520 300 a 521 use mo_catalog; 522 select * from cluster_table_5 left join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 523 col1 col2 a b 524 1 a 100 a 525 1 a 200 a 526 1 a 300 a 527 2 b null null 528 3 c null null 529 4 d null null 530 5 f null null 531 6 g null null 532 select * from cluster_table_5 right join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 533 col1 col2 a b 534 1 a 100 a 535 1 a 200 a 536 1 a 300 a 537 select * from cluster_table_5 inner join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 538 col1 col2 a b 539 1 a 100 a 540 1 a 200 a 541 1 a 300 a 542 select * from cluster_table_5 union select * from cluster_table_6; 543 col1 col2 544 100 a 545 200 a 546 300 a 547 1 a 548 2 b 549 3 c 550 4 d 551 5 f 552 6 g 553 select * from cluster_table_5 union all select * from cluster_table_6; 554 col1 col2 555 100 a 556 200 a 557 300 a 558 1 a 559 2 b 560 3 c 561 4 d 562 5 f 563 6 g 564 select * from cluster_table_5 intersect select * from cluster_table_6; 565 col1 col2 566 SELECT * FROM cluster_table_5 MINUS SELECT * FROM cluster_table_6; 567 col1 col2 568 1 a 569 2 b 570 3 c 571 4 d 572 5 f 573 6 g 574 SELECT * FROM cluster_table_6 MINUS SELECT * FROM cluster_table_5; 575 a b 576 100 a 577 200 a 578 300 a 579 use mo_catalog; 580 select * from cluster_table_5 left join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 581 col1 col2 a b 582 1 a 100 a 583 1 a 200 a 584 1 a 300 a 585 2 b null null 586 3 c null null 587 4 d null null 588 5 f null null 589 6 g null null 590 select * from cluster_table_5 right join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 591 col1 col2 a b 592 1 a 100 a 593 1 a 200 a 594 1 a 300 a 595 select * from cluster_table_5 inner join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b; 596 col1 col2 a b 597 1 a 100 a 598 1 a 200 a 599 1 a 300 a 600 select * from cluster_table_5 union select * from cluster_table_6; 601 col1 col2 602 100 a 603 200 a 604 300 a 605 1 a 606 2 b 607 3 c 608 4 d 609 5 f 610 6 g 611 select * from cluster_table_5 union all select * from cluster_table_6; 612 col1 col2 613 100 a 614 200 a 615 300 a 616 1 a 617 2 b 618 3 c 619 4 d 620 5 f 621 6 g 622 select * from cluster_table_5 intersect select * from cluster_table_6; 623 col1 col2 624 SELECT * FROM cluster_table_5 MINUS SELECT * FROM cluster_table_6; 625 col1 col2 626 1 a 627 2 b 628 3 c 629 4 d 630 5 f 631 6 g 632 SELECT * FROM cluster_table_6 MINUS SELECT * FROM cluster_table_5; 633 a b 634 100 a 635 200 a 636 300 a 637 drop table cluster_table_5; 638 drop table cluster_table_6; 639 drop table if exists cluster_table_7; 640 create cluster table cluster_table_7( 641 col1 int, 642 col2 varchar 643 ); 644 insert into cluster_table_7 values (1,'a',0),(2,'b',0); 645 insert into cluster_table_7 values (1,'a',1),(2,'b',1); 646 insert into cluster_table_7 values (1,'a',2),(2,'b',2); 647 update cluster_table_7 set account_id=(select account_id from mo_account where account_name="test_account1") where account_id=1; 648 update cluster_table_7 set account_id=(select account_id from mo_account where account_name="test_account2") where account_id=2; 649 select col1,col2 from cluster_table_7; 650 col1 col2 651 1 a 652 2 b 653 1 a 654 2 b 655 1 a 656 2 b 657 drop account test_account1; 658 select col1,col2 from cluster_table_7; 659 col1 col2 660 1 a 661 2 b 662 1 a 663 2 b 664 drop account test_account2; 665 select col1,col2 from cluster_table_7; 666 col1 col2 667 1 a 668 2 b 669 drop table cluster_table_7; 670 use mo_catalog; 671 CREATE CLUSTER TABLE mo_instance (id varchar(128) NOT NULL,name VARCHAR(255) NOT NULL,account_name varchar(128) NOT NULL,provider longtext NOT NULL,provider_id longtext,region longtext NOT NULL,plan_type longtext NOT NULL,version longtext,status longtext,quota longtext,network_policy longtext,created_by longtext,created_at datetime(3) NULL,PRIMARY KEY (id, account_id),UNIQUE INDEX uniq_acc (account_name)); 672 create account acc_idx ADMIN_NAME 'root' IDENTIFIED BY '123456'; 673 drop account acc_idx; 674 drop table mo_instance; 675 drop table if exists statement_cu;