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

     1  
     2  -- test sys tenement non-system database, create cluster table.
     3  drop database if exists test_db1;
     4  create database test_db1;
     5  use test_db1;
     6  drop table if exists t1;
     7  create cluster table t1(a int, b int);
     8  drop database test_db1;
     9  
    10  
    11  -- test sys tenement system database, create cluster table.(only mo_catalog support)
    12  use mo_task;
    13  drop table if exists t2;
    14  create cluster table t2(a int, b int);
    15  
    16  use information_schema;
    17  drop table if exists t3;
    18  create cluster table t3(a int, b int);
    19  desc t3;
    20  drop table t3;
    21  
    22  use mysql;
    23  drop table if exists t4;
    24  create cluster table t4(a int, b int);
    25  desc t4;
    26  drop table t4;
    27  
    28  use system_metrics;
    29  drop table if exists t5;
    30  create cluster table t5(a int, b int);
    31  desc t5;
    32  drop table t5;
    33  
    34  use system;
    35  drop table if exists t6;
    36  create cluster table t6(a int, b int);
    37  desc t6;
    38  drop table t6;
    39  
    40  use mo_catalog;
    41  drop table if exists t7;
    42  create cluster table t7(a int, b int);
    43  desc t7;
    44  drop table t7;
    45  
    46  -- test system tenant inserts data into the cluster table
    47  use mo_catalog;
    48  drop table if exists cluster_table_1;
    49  create cluster table cluster_table_1(a int, b int);
    50  
    51  drop account if exists test_account1;
    52  create account test_account1 admin_name = 'test_user' identified by '111';
    53  
    54  drop account if exists test_account2;
    55  create account test_account2 admin_name = 'test_user' identified by '111';
    56  
    57  insert into cluster_table_1 accounts(sys,test_account1,test_account2) values(0,0),(1,1);
    58  select a,b from cluster_table_1;
    59  
    60  -- @session:id=2&user=test_account1:test_user&password=111
    61  use mo_catalog;
    62  select * from cluster_table_1;
    63  -- @session
    64  
    65  -- @session:id=3&user=test_account2:test_user&password=111
    66  use mo_catalog;
    67  select * from cluster_table_1;
    68  -- @session
    69  
    70  insert into cluster_table_1 values(200,200);
    71  insert into cluster_table_1 values(100,100);
    72  insert into cluster_table_1 values(50,50);
    73  select a,b from cluster_table_1;
    74  
    75  -- @session:id=2&user=test_account1:test_user&password=111
    76  use mo_catalog;
    77  select * from cluster_table_1;
    78  -- @session
    79  
    80  -- @session:id=3&user=test_account2:test_user&password=111
    81  use mo_catalog;
    82  select * from cluster_table_1;
    83  -- @session
    84  
    85  drop table cluster_table_1;
    86  
    87  
    88  -- test system tenant load data into the cluster table
    89  drop table if exists cluster_table_2;
    90  create cluster table cluster_table_2(
    91  col1 int,
    92  col2 float,
    93  col3 decimal,
    94  col4 date,
    95  col5 bool,
    96  col6 json,
    97  col7 blob,
    98  col8 text,
    99  col9 varchar
   100  );
   101  
   102  load data infile '$resources/load_data/cluster_table.csv' into table cluster_table_2 accounts(sys,test_account1,test_account2);
   103  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_2;
   104  
   105  load data infile '$resources/load_data/cluster_table.csv' into table cluster_table_2;
   106  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_2;
   107  
   108  -- @session:id=2&user=test_account1:test_user&password=111
   109  use mo_catalog;
   110  select * from cluster_table_2;
   111  -- @session
   112  
   113  -- @session:id=3&user=test_account2:test_user&password=111
   114  use mo_catalog;
   115  select * from cluster_table_2;
   116  -- @session
   117  
   118  drop table cluster_table_2;
   119  
   120  
   121  -- test system tenement, operation cluster table (update,delete,truncate)
   122  drop table if exists cluster_table_3;
   123  create cluster table cluster_table_3(
   124  col1 int,
   125  col2 float,
   126  col3 decimal,
   127  col4 date,
   128  col5 bool,
   129  col6 json,
   130  col7 blob,
   131  col8 text,
   132  col9 varchar
   133  );
   134  
   135  insert into cluster_table_3 accounts(sys,test_account1,test_account2) values (1,1.09,1.345,"2022-10-02",0,'{"a":1}',"你好","text","varchar");
   136  insert into cluster_table_3 accounts(sys,test_account1,test_account2) values (2,10.9,13.45,"2022-10-02",1,'{"b":2}',"nihao","文本","字符");
   137  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   138  
   139  update cluster_table_3 set col1=100 where account_id=0 and col1=1;
   140  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   141  
   142  -- @session:id=2&user=test_account1:test_user&password=111
   143  use mo_catalog;
   144  select * from cluster_table_3;
   145  -- @session
   146  
   147  -- @session:id=3&user=test_account2:test_user&password=111
   148  use mo_catalog;
   149  select * from cluster_table_3;
   150  -- @session
   151  
   152  update cluster_table_3 set col1=100 where account_id=(select account_id from mo_account where account_name="test_account1") and col1=1;
   153  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   154  
   155  -- @session:id=2&user=test_account1:test_user&password=111
   156  use mo_catalog;
   157  select * from cluster_table_3;
   158  -- @session
   159  
   160  -- @session:id=3&user=test_account2:test_user&password=111
   161  use mo_catalog;
   162  select * from cluster_table_3;
   163  -- @session
   164  
   165  update cluster_table_3 set col1=100 where account_id=(select account_id from mo_account where account_name="test_account2") and col1=1;
   166  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   167  
   168  -- @session:id=2&user=test_account1:test_user&password=111
   169  use mo_catalog;
   170  select * from cluster_table_3;
   171  -- @session
   172  
   173  -- @session:id=3&user=test_account2:test_user&password=111
   174  use mo_catalog;
   175  select * from cluster_table_3;
   176  -- @session
   177  
   178  
   179  delete from cluster_table_3 where account_id=0;
   180  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   181  
   182  -- @session:id=2&user=test_account1:test_user&password=111
   183  use mo_catalog;
   184  select * from cluster_table_3;
   185  -- @session
   186  
   187  -- @session:id=3&user=test_account2:test_user&password=111
   188  use mo_catalog;
   189  select * from cluster_table_3;
   190  -- @session
   191  
   192  
   193  delete from cluster_table_3 where account_id=(select account_id from mo_account where account_name="test_account1");
   194  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   195  
   196  -- @session:id=2&user=test_account1:test_user&password=111
   197  use mo_catalog;
   198  select * from cluster_table_3;
   199  -- @session
   200  
   201  -- @session:id=3&user=test_account2:test_user&password=111
   202  use mo_catalog;
   203  select * from cluster_table_3;
   204  -- @session
   205  
   206  
   207  delete from cluster_table_3 where account_id=(select account_id from mo_account where account_name="test_account2");
   208  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   209  
   210  -- @session:id=2&user=test_account1:test_user&password=111
   211  use mo_catalog;
   212  select * from cluster_table_3;
   213  -- @session
   214  
   215  -- @session:id=3&user=test_account2:test_user&password=111
   216  use mo_catalog;
   217  select * from cluster_table_3;
   218  -- @session
   219  
   220  
   221  truncate table cluster_table_3;
   222  select col1,col2,col3,col4,col5,col6,col7,col8,col9 from cluster_table_3;
   223  
   224  -- @session:id=2&user=test_account1:test_user&password=111
   225  use mo_catalog;
   226  select * from cluster_table_3;
   227  -- @session
   228  
   229  -- @session:id=3&user=test_account2:test_user&password=111
   230  use mo_catalog;
   231  select * from cluster_table_3;
   232  -- @session
   233  
   234  drop table cluster_table_3;
   235  
   236  
   237  -- test create cluster table include account_id columns
   238  create cluster table cluster_table_xx(account_id int);
   239  
   240  -- test common tenement operation(desc table,show create table,drop table)
   241  drop table if exists cluster_table_4;
   242  create cluster table cluster_table_4(
   243  col1 int,
   244  col2 varchar
   245  );
   246  
   247  insert into cluster_table_4 accounts(sys,test_account1,test_account2) values (1,'a'),(2,'b');
   248  select col1,col2 from cluster_table_4;
   249  
   250  -- @session:id=2&user=test_account1:test_user&password=111
   251  use mo_catalog;
   252  desc cluster_table_4;
   253  show create table cluster_table_4;
   254  drop table cluster_table_4;
   255  -- @session
   256  
   257  -- test common tenement operation table include (insert,update,delete,truncate)
   258  -- @session:id=2&user=test_account1:test_user&password=111
   259  use mo_catalog;
   260  insert into cluster_table_4 values (3, 'c');
   261  update cluster_table_4 set col1=10 where col2='a';
   262  delete from cluster_table_4 where col1=2;
   263  truncate table cluster_table_4;
   264  -- @session
   265  
   266  drop table cluster_table_4;
   267  
   268  
   269  -- test cluster table relevance query(join,union)
   270  drop table if exists cluster_table_5;
   271  create cluster table cluster_table_5(
   272  col1 int,
   273  col2 varchar
   274  );
   275  
   276  insert into cluster_table_5  accounts(sys,test_account1,test_account2) values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'f'),(6,'g');
   277  
   278  
   279  drop table if exists cluster_table_6;
   280  create cluster table cluster_table_6(
   281  a int,
   282  b varchar
   283  );
   284  
   285  insert into cluster_table_6  accounts(sys,test_account1,test_account2)  values (100,'a'),(200,'a'),(300,'a');
   286  
   287  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;
   288  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;
   289  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;
   290  
   291  select col1,col2 from cluster_table_5 union select a,b from cluster_table_6;
   292  select col1,col2 from cluster_table_5 union all select a,b from cluster_table_6;
   293  
   294  select col1,col2 from cluster_table_5 intersect select a,b from cluster_table_6;
   295  
   296  SELECT col1,col2 FROM cluster_table_5 MINUS SELECT a,b FROM cluster_table_6;
   297  SELECT a,b FROM cluster_table_6 MINUS SELECT col1,col2 FROM cluster_table_5;
   298  
   299  
   300  -- @session:id=2&user=test_account1:test_user&password=111
   301  use mo_catalog;
   302  select * from cluster_table_5  left join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   303  select * from cluster_table_5  right join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   304  select * from cluster_table_5  inner join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   305  
   306  select * from cluster_table_5 union select * from cluster_table_6;
   307  select * from cluster_table_5 union all select * from cluster_table_6;
   308  
   309  select * from cluster_table_5 intersect select * from cluster_table_6;
   310  
   311  SELECT * FROM cluster_table_5 MINUS SELECT * FROM cluster_table_6;
   312  SELECT * FROM cluster_table_6 MINUS SELECT * FROM cluster_table_5;
   313  -- @session
   314  
   315  
   316  -- @session:id=3&user=test_account2:test_user&password=111
   317  use mo_catalog;
   318  select * from cluster_table_5  left join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   319  select * from cluster_table_5  right join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   320  select * from cluster_table_5  inner join cluster_table_6 on cluster_table_5.col2=cluster_table_6.b;
   321  
   322  select * from cluster_table_5 union select * from cluster_table_6;
   323  select * from cluster_table_5 union all select * from cluster_table_6;
   324  
   325  select * from cluster_table_5 intersect select * from cluster_table_6;
   326  
   327  SELECT * FROM cluster_table_5 MINUS SELECT * FROM cluster_table_6;
   328  SELECT * FROM cluster_table_6 MINUS SELECT * FROM cluster_table_5;
   329  -- @session
   330  
   331  
   332  drop table cluster_table_5;
   333  drop table cluster_table_6;
   334  
   335  
   336  -- test when delete a tenant, the data of the tenant in the cluster table is deleted
   337  drop table if exists cluster_table_7;
   338  create cluster table cluster_table_7(
   339  col1 int,
   340  col2 varchar
   341  );
   342  
   343  insert into cluster_table_7 accounts(sys,test_account1,test_account2) values (1,'a'),(2,'b');
   344  select col1,col2 from cluster_table_7;
   345  
   346  drop account test_account1;
   347  select col1,col2 from cluster_table_7;
   348  
   349  drop account test_account2;
   350  select col1,col2 from cluster_table_7;
   351  
   352  drop table cluster_table_7;