github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/prepare/prepare.test (about)

     1  
     2  -- @label:bvt
     3  drop table if exists t1;
     4  create table t1 (a int, b int);
     5  prepare stmt1 from 'select * from t1 where a > ?';
     6  insert into t1 values (1, 11), (2, 22), (3, 33);
     7  set @a_var = 1;
     8  execute stmt1 using @a_var;
     9  set @a_var = 2;
    10  execute stmt1 using @a_var;
    11  insert into t1 values (4, 44);
    12  execute stmt1 using @a_var;
    13  delete from t1 where a > 3;
    14  execute stmt1 using @a_var;
    15  deallocate prepare stmt1;
    16  execute stmt1 using @a_var;
    17  
    18  prepare stmt1 from 'update t1 set a=999 where b = ?';
    19  set @b_var = 33;
    20  execute stmt1 using @b_var;
    21  select * from t1;
    22  deallocate prepare stmt1;
    23  
    24  prepare stmt1 from 'delete from t1 where b = ?';
    25  execute stmt1 using @b_var;
    26  select * from t1;
    27  deallocate prepare stmt1;
    28  
    29  prepare stmt1 from "insert into t1 values (?, ?), (?, 99)";
    30  set @a_var=5;
    31  set @b_var=55;
    32  set @c_var=9;
    33  execute stmt1 using @a_var, @b_var, @c_var;
    34  select * from t1;
    35  deallocate prepare stmt1;
    36  
    37  drop table t1;
    38  
    39  create table t1(a timestamp);
    40  prepare stmt1 from "insert into t1 values (current_timestamp())";
    41  execute stmt1;
    42  execute stmt1;
    43  select count(*) from (select distinct a from t1) t;
    44  drop table t1;
    45  
    46  prepare stmt2 from 'select @var_t1';
    47  set @var_t1 = 0;
    48  execute stmt2;
    49  set @var_t1= 'aa';
    50  execute stmt2;
    51  deallocate prepare stmt2;
    52  
    53  create table t1 (a decimal(20,4), b int);
    54  insert into t1 values (12.2222, 1);
    55  prepare stmt1 from 'update t1 set a=a+? where b = 1';
    56  set @a=0.1111;
    57  execute stmt1 using @a;
    58  select a, b from t1;
    59  
    60  drop table if exists t1;
    61  create table t1 (a decimal(12,2));
    62  insert into t1 values (30000);
    63  prepare stmt1 from 'update t1 set a = a + ?';
    64  set @a=4418.59;
    65  execute stmt1;
    66  execute stmt1 using @a;
    67  select a from t1;
    68  drop table if exists t1;
    69  prepare s6 from 'create table t1(a int)';
    70  execute s6;
    71  show tables;
    72  prepare s7 from 'drop table t1';
    73  execute s7;
    74  show tables;
    75  drop table if exists t1;
    76  create table t1(a int);
    77  set autocommit=0;
    78  insert into t1 values (1);
    79  prepare s1 from "create table t2(a int)";
    80  prepare s1 from "drop table t1";
    81  rollback;
    82  prepare s1 from "create table t2(a int)";
    83  commit;
    84  prepare s2 from "drop table t1";
    85  commit;
    86  insert into t1 values (1);
    87  execute s1;
    88  execute s2;
    89  rollback;
    90  set autocommit=1;
    91  
    92  create table bmsql_district ( d_w_id integer not null,
    93  d_id integer not null,
    94  d_ytd decimal(12,2),
    95  d_tax decimal(4,4),
    96  d_next_o_id integer,
    97  d_name varchar(10),
    98  d_street_1 varchar(20),
    99  d_street_2 varchar(20),
   100  d_city varchar(20),
   101  d_state char(2),
   102  d_zip char(9),
   103  primary key (d_w_id, d_id) ) PARTITION BY KEY(d_w_id);
   104  prepare __mo_stmt_id_1 from UPDATE bmsql_district  SET d_next_o_id = d_next_o_id + 1     WHERE d_w_id = ? AND d_id = ?;
   105  set @__mo_stmt_var_0=1, @__mo_stmt_var_1=7;
   106  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1;
   107  deallocate prepare __mo_stmt_id_1;
   108  drop table if exists user;
   109  CREATE TABLE user
   110  (
   111   id BIGINT(20) NOT NULL COMMENT '主键ID',
   112   name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
   113   age INT(11) NULL DEFAULT NULL COMMENT '年龄',
   114   email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
   115   PRIMARY KEY (id)
   116  );
   117  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   118  set @__mo_stmt_var_0 = 0,@__mo_stmt_var_1 = "test_save_0" ,@__mo_stmt_var_2 = 0;
   119  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   120  deallocate prepare __mo_stmt_id_1;
   121  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   122  execute __mo_stmt_id_1;
   123  deallocate prepare __mo_stmt_id_1;
   124  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   125  set @__mo_stmt_var_0 = 1,@__mo_stmt_var_1 = "test_save_1" ,@__mo_stmt_var_2 = 1;
   126  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   127  deallocate prepare __mo_stmt_id_1;
   128  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   129  execute __mo_stmt_id_1;
   130  deallocate prepare __mo_stmt_id_1;
   131  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   132  set @__mo_stmt_var_0 = 2,@__mo_stmt_var_1 = "test_save_2" ,@__mo_stmt_var_2 = 2;
   133  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   134  deallocate prepare __mo_stmt_id_1;
   135  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   136  execute __mo_stmt_id_1;
   137  deallocate prepare __mo_stmt_id_1;
   138  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   139  set @__mo_stmt_var_0 = 3,@__mo_stmt_var_1 = "test_save_3" ,@__mo_stmt_var_2 = 3;
   140  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   141  deallocate prepare __mo_stmt_id_1;
   142  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   143  execute __mo_stmt_id_1;
   144  deallocate prepare __mo_stmt_id_1;
   145  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   146  set @__mo_stmt_var_0 = 4,@__mo_stmt_var_1 = "test_save_4" ,@__mo_stmt_var_2 = 4;
   147  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   148  deallocate prepare __mo_stmt_id_1;
   149  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   150  execute __mo_stmt_id_1;
   151  deallocate prepare __mo_stmt_id_1;
   152  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   153  set @__mo_stmt_var_0 = 5,@__mo_stmt_var_1 = "test_save_5" ,@__mo_stmt_var_2 = 5;
   154  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   155  deallocate prepare __mo_stmt_id_1;
   156  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   157  execute __mo_stmt_id_1;
   158  deallocate prepare __mo_stmt_id_1;
   159  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   160  set @__mo_stmt_var_0 = 6,@__mo_stmt_var_1 = "test_save_6" ,@__mo_stmt_var_2 = 6;
   161  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   162  deallocate prepare __mo_stmt_id_1;
   163  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   164  execute __mo_stmt_id_1;
   165  deallocate prepare __mo_stmt_id_1;
   166  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   167  set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7;
   168  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   169  deallocate prepare __mo_stmt_id_1;
   170  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   171  execute __mo_stmt_id_1;
   172  deallocate prepare __mo_stmt_id_1;
   173  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   174  set @__mo_stmt_var_0 = 8,@__mo_stmt_var_1 = "test_save_8" ,@__mo_stmt_var_2 = 8;
   175  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   176  deallocate prepare __mo_stmt_id_1;
   177  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   178  execute __mo_stmt_id_1;
   179  deallocate prepare __mo_stmt_id_1;
   180  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   181  set @__mo_stmt_var_0 = 9,@__mo_stmt_var_1 = "test_save_9" ,@__mo_stmt_var_2 = 9;
   182  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   183  deallocate prepare __mo_stmt_id_1;
   184  prepare __mo_stmt_id_1 from 'select id, name, email, age from user';
   185  execute __mo_stmt_id_1;
   186  deallocate prepare __mo_stmt_id_1;
   187  create table algo_offline (algo_id int(11) unsigned not null, algo_name varchar(50) not null);
   188  prepare stmt1 from select count(*) from algo_offline where (algo_name like ? or algo_id like ?);
   189  set @a="aa";
   190  set @b="bb";
   191  execute stmt1 using @a, @b;
   192  deallocate prepare stmt1;
   193  drop table if exists t1;
   194  create table t1 (a int, b int);
   195  prepare stmt1 from 'show columns from t1';
   196  execute stmt1;
   197  prepare stmt1 from 'show variables like "aaaa"';
   198  execute stmt1;
   199  
   200  prepare __mo_stmt_id_1 from 'INSERT INTO user  ( id, name, age )  VALUES  ( ?, ?,? )';
   201  set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7;
   202  execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2;
   203  reset prepare __mo_stmt_id_1;
   204  deallocate prepare __mo_stmt_id_1;
   205  
   206  drop table if exists t1;
   207  create table t1( a int unique key,  b int,  c int );
   208  prepare stmt1 from 'INSERT INTO t1 values(1, 2, 3)';
   209  execute stmt1;
   210  execute stmt1;
   211  select * from t1;
   212  
   213  prepare stmt1 from 'update t1 set b = ? where a = ?';
   214  set @varb = 22;
   215  set @vara = 1;
   216  execute stmt1 using @varb,@vara;
   217  select * from t1;
   218  drop table t1;