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

     1  drop database if exists db1;
     2  create database db1;
     3  -- table b
     4  create table db1.b(b int);
     5  insert into db1.b values (0),(1),(2),(3);
     6  
     7  drop account if exists account_test;
     8  create account account_test admin_name = 'root' identified by '111' open comment 'account_test';
     9  
    10  use mo_catalog;
    11  drop table if exists a;
    12  
    13  --------------------
    14  -- one attribute
    15  --------------------
    16  
    17  -- cluster table a
    18  create cluster table a(a int);
    19  
    20  -- insert into cluster table
    21  insert into a accounts(sys,account_test) values(0),(1),(2),(3);
    22  select a from a;
    23  
    24  -- check it in the non-sys account
    25  -- @session:id=2&user=account_test:root&password=111
    26  use mo_catalog;
    27  select * from a;
    28  -- @session
    29  
    30  -- delete data from a
    31  delete from a;
    32  
    33  -- check it in the non-sys account
    34  -- @session:id=2&user=account_test:root&password=111
    35  use mo_catalog;
    36  select * from a;
    37  -- @session
    38  
    39  -- insert into cluster table
    40  insert into a accounts(sys,account_test) select b from db1.b;
    41  
    42  select a from a;
    43  
    44  -- check it in the non-sys account
    45  -- @session:id=2&user=account_test:root&password=111
    46  use mo_catalog;
    47  select * from a;
    48  -- @session
    49  
    50  delete from a;
    51  
    52  -- load into cluster table
    53  load data infile '$resources/load_data/cluster_table1.csv' into table a accounts(sys,account_test) (a);
    54  select a from a;
    55  
    56  -- check it in the non-sys account
    57  -- @session:id=2&user=account_test:root&password=111
    58  use mo_catalog;
    59  select * from a;
    60  -- @session
    61  
    62  delete from a;
    63  
    64  -- insert into account_id
    65  insert into a(account_id) values (0),(1),(2),(3);
    66  insert into a(account_id) select b from db1.b;
    67  load data infile '$resources/load_data/cluster_table1.csv' into table a (account_id);
    68  
    69  -- check it in the non-sys account
    70  -- @session:id=2&user=account_test:root&password=111
    71  use mo_catalog;
    72  select * from a;
    73  -- @session
    74  
    75  delete from a;
    76  truncate table a;
    77  
    78  -- non-sys account operate the cluster table
    79  -- @session:id=2&user=account_test:root&password=111
    80  use mo_catalog;
    81  delete from a;
    82  drop table a;
    83  truncate table a;
    84  -- @session
    85  
    86  -- drop account
    87  drop account if exists account_test;
    88  
    89  select a from a;
    90  drop table if exists a;
    91  drop account if exists account_test;
    92  drop database if exists db1;