github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/result_count/result_count.sql (about)

     1  -- prepare
     2  drop account if exists bvt_result_count;
     3  create account bvt_result_count admin_name 'admin' identified by '111';
     4  
     5  -- case 1
     6  -- @session:id=2&user=bvt_result_count:admin&password=111
     7  -- transaction sql
     8  begin;
     9  rollback;
    10  commit;
    11  start transaction;
    12  commit;
    13  
    14  -- create db/table/view (insert,delete,update,select)
    15  create database db1;
    16  use db1;
    17  create table t1(a int, b varchar);
    18  insert into t1 values (1, 'a'),(1, 'b'),(3, 'c'),(4,'d'),(5,'e');
    19  update t1 set b='xx' where a=5;
    20  update t1 set b='yy' where a=1;
    21  select * from t1;
    22  select * from t1 limit 3;
    23  delete from t1 where a=5;
    24  delete from t1 where a=1;
    25  
    26  -- test create view
    27  create view v1 as select * from t1;
    28  create view v2 as select * from t1 limit 1;
    29  
    30  -- test prepare
    31  set @a=1;
    32  prepare s1 from "select * from t1 where a>?";
    33  prepare s2 from "select * from t1 where a=?";
    34  
    35  -- @bvt:issue#9525
    36  execute s1 using @a;
    37  execute s2 using @a;
    38  -- @bvt:issue
    39  
    40  deallocate prepare s2;
    41  deallocate prepare s2;
    42  
    43  -- test show
    44  show databases like 'mysql';
    45  show tables;
    46  show create database db1;
    47  show create view v1;
    48  show create table t1;
    49  show columns from t1;
    50  
    51  -- test drop table/database/view
    52  drop view v1;
    53  drop table t1;
    54  drop view v2;
    55  drop database db1;
    56  
    57  -- test DCL sql
    58  create role test_role;
    59  create user user_name identified by 'password';
    60  create database if not exists db1;
    61  grant create table,drop table on database *.* to test_role;
    62  revoke test_role from user_name;
    63  drop user user_name;
    64  drop role test_role;
    65  drop database db1;
    66  
    67  -- test transaction (insert/delete/update/select)
    68  create database db2;
    69  use db2;
    70  create table t2(a int, b varchar);
    71  begin;
    72  insert into t2 values (1, 'a'),(1, 'b'),(3, 'c'),(4,'d'),(5,'e');
    73  update t2 set b='xx' where a=5;
    74  update t2 set b='yy' where a=1;
    75  select * from t2;
    76  select * from t2 limit 3;
    77  delete from t2 where a=5;
    78  delete from t2 where a=1;
    79  commit;
    80  drop database db2;
    81  
    82  -- test cloud user execute
    83  /* cloud_user */ create database db2;
    84  /* cloud_user */ use db2;
    85  /* cloud_user */ create table t2(a int, b varchar);
    86  /* cloud_user */ insert into t2 values (1, 'a'),(1, 'b'),(3, 'c'),(4,'d'),(5,'e');
    87  /* cloud_user */ update t2 set b='xx' where a=5;
    88  /* cloud_user */ update t2 set b='yy' where a=1;
    89  /* cloud_user */ select * from t2;
    90  /* cloud_user */ select * from t2 limit 3;
    91  /* cloud_user */ delete from t2 where a=5;
    92  /* cloud_user */ delete from t2 where a=1;
    93  /* cloud_user */ drop database db2;
    94  
    95  -- test other
    96  select 1;
    97  select 1 union select 2;
    98  values row(1,1), row(2,2), row(3,3) order by column_0 desc;
    99  WITH cte1 AS (SELECT 1),cte2 AS (SELECT 2) SELECT * FROM cte1 join cte2;
   100  select * from unnest('{"a":1}') as f;
   101  use system;
   102  -- @session
   103  -- case 1: END
   104  
   105  -- case 2: 通过dump 账号测试 create account/drop account
   106  -- test create/drop account
   107  /* cloud_user */create account test_tenant_1 admin_name 'test_account' identified by '111';
   108  /* cloud_user */drop account test_tenant_1;
   109  -- case 2: END
   110  
   111  -- result check
   112  select sleep(18) s;
   113  use system;
   114  -- check case 1
   115  -- Reason for changing the sql.
   116  -- For the 'set_var' statement, some ast generated by the background execution only have the ast instead of the source sql.
   117  -- For instance, 'set @a = (select 1)'. The ast of '(select 1)' will be rewritten to the ast of 'select (select 1) from dual'.
   118  -- We do not have the source sql of 'select (select 1) from dual'. We just have the ast of 'select (select 1) from dual'.
   119  -- So the statement_info.statement for 'select (select 1) from dual' only is empty string.
   120  
   121  -- @bvt:issue#13064
   122  select statement, result_count from statement_info where account="bvt_result_count" and statement not like '%mo_ctl%' and length(statement) > 0 and status != 'Running' and aggr_count < 1 order by request_at desc limit 50;
   123  -- @bvt:issue
   124  
   125  -- check case 2
   126  select statement, result_count from statement_info where user="dump" and sql_source_type="cloud_user_sql" and status != 'Running' and aggr_count < 1 order by request_at desc limit 2;
   127  
   128  -- cleanup
   129  drop account if exists bvt_result_count;
   130