github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/prepare/prepare.result (about) 1 drop table if exists t1; 2 create table t1 (a int, b int); 3 prepare stmt1 from 'select * from t1 where a > ?'; 4 insert into t1 values (1, 11), (2, 22), (3, 33); 5 set @a_var = 1; 6 execute stmt1 using @a_var; 7 a b 8 2 22 9 3 33 10 set @a_var = 2; 11 execute stmt1 using @a_var; 12 a b 13 3 33 14 insert into t1 values (4, 44); 15 execute stmt1 using @a_var; 16 a b 17 3 33 18 4 44 19 delete from t1 where a > 3; 20 execute stmt1 using @a_var; 21 a b 22 3 33 23 deallocate prepare stmt1; 24 execute stmt1 using @a_var; 25 invalid state prepared statement 'stmt1' does not exist 26 prepare stmt1 from 'update t1 set a=999 where b = ?'; 27 set @b_var = 33; 28 execute stmt1 using @b_var; 29 select * from t1; 30 a b 31 1 11 32 2 22 33 999 33 34 deallocate prepare stmt1; 35 prepare stmt1 from 'delete from t1 where b = ?'; 36 execute stmt1 using @b_var; 37 select * from t1; 38 a b 39 1 11 40 2 22 41 deallocate prepare stmt1; 42 prepare stmt1 from "insert into t1 values (?, ?), (?, 99)"; 43 set @a_var=5; 44 set @b_var=55; 45 set @c_var=9; 46 execute stmt1 using @a_var, @b_var, @c_var; 47 select * from t1; 48 a b 49 1 11 50 2 22 51 5 55 52 9 99 53 deallocate prepare stmt1; 54 drop table t1; 55 create table t1(a timestamp); 56 prepare stmt1 from "insert into t1 values (current_timestamp())"; 57 execute stmt1; 58 execute stmt1; 59 select count(*) from (select distinct a from t1) t; 60 count(*) 61 2 62 drop table t1; 63 prepare stmt2 from 'select @var_t1'; 64 set @var_t1 = 0; 65 execute stmt2; 66 @var_t1 67 0 68 set @var_t1= 'aa'; 69 execute stmt2; 70 @var_t1 71 aa 72 deallocate prepare stmt2; 73 create table t1 (a decimal(20,4), b int); 74 insert into t1 values (12.2222, 1); 75 prepare stmt1 from 'update t1 set a=a+? where b = 1'; 76 set @a=0.1111; 77 execute stmt1 using @a; 78 select a, b from t1; 79 a b 80 12.3333 1 81 drop table if exists t1; 82 create table t1 (a decimal(12,2)); 83 insert into t1 values (30000); 84 prepare stmt1 from 'update t1 set a = a + ?'; 85 set @a=4418.59; 86 execute stmt1; 87 invalid input: Incorrect arguments to EXECUTE 88 execute stmt1 using @a; 89 select a from t1; 90 a 91 34418.59 92 drop table if exists t1; 93 prepare s6 from 'create table t1(a int)'; 94 execute s6; 95 show tables; 96 tables_in_db1 97 t1 98 prepare s7 from 'drop table t1'; 99 execute s7; 100 show tables; 101 tables_in_db1 102 drop table if exists t1; 103 create table t1(a int); 104 set autocommit=0; 105 insert into t1 values (1); 106 prepare s1 from "create table t2(a int)"; 107 prepare s1 from "drop table t1"; 108 internal error: unclassified statement appears in uncommitted transaction 109 rollback; 110 prepare s1 from "create table t2(a int)"; 111 commit; 112 prepare s2 from "drop table t1"; 113 commit; 114 insert into t1 values (1); 115 execute s1; 116 execute s2; 117 internal error: unclassified statement appears in uncommitted transaction 118 rollback; 119 set autocommit=1; 120 create table bmsql_district ( d_w_id integer not null, 121 d_id integer not null, 122 d_ytd decimal(12,2), 123 d_tax decimal(4,4), 124 d_next_o_id integer, 125 d_name varchar(10), 126 d_street_1 varchar(20), 127 d_street_2 varchar(20), 128 d_city varchar(20), 129 d_state char(2), 130 d_zip char(9), 131 primary key (d_w_id, d_id) ) PARTITION BY KEY(d_w_id); 132 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 = ?; 133 set @__mo_stmt_var_0=1, @__mo_stmt_var_1=7; 134 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1; 135 deallocate prepare __mo_stmt_id_1; 136 drop table if exists user; 137 CREATE TABLE user 138 ( 139 id BIGINT(20) NOT NULL COMMENT '主键ID', 140 name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', 141 age INT(11) NULL DEFAULT NULL COMMENT '年龄', 142 email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', 143 PRIMARY KEY (id) 144 ); 145 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 146 set @__mo_stmt_var_0 = 0,@__mo_stmt_var_1 = "test_save_0" ,@__mo_stmt_var_2 = 0; 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 id name email age 152 0 test_save_0 null 0 153 deallocate prepare __mo_stmt_id_1; 154 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 155 set @__mo_stmt_var_0 = 1,@__mo_stmt_var_1 = "test_save_1" ,@__mo_stmt_var_2 = 1; 156 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 157 deallocate prepare __mo_stmt_id_1; 158 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 159 execute __mo_stmt_id_1; 160 id name email age 161 0 test_save_0 null 0 162 1 test_save_1 null 1 163 deallocate prepare __mo_stmt_id_1; 164 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 165 set @__mo_stmt_var_0 = 2,@__mo_stmt_var_1 = "test_save_2" ,@__mo_stmt_var_2 = 2; 166 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 167 deallocate prepare __mo_stmt_id_1; 168 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 169 execute __mo_stmt_id_1; 170 id name email age 171 0 test_save_0 null 0 172 1 test_save_1 null 1 173 2 test_save_2 null 2 174 deallocate prepare __mo_stmt_id_1; 175 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 176 set @__mo_stmt_var_0 = 3,@__mo_stmt_var_1 = "test_save_3" ,@__mo_stmt_var_2 = 3; 177 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 178 deallocate prepare __mo_stmt_id_1; 179 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 180 execute __mo_stmt_id_1; 181 id name email age 182 0 test_save_0 null 0 183 1 test_save_1 null 1 184 2 test_save_2 null 2 185 3 test_save_3 null 3 186 deallocate prepare __mo_stmt_id_1; 187 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 188 set @__mo_stmt_var_0 = 4,@__mo_stmt_var_1 = "test_save_4" ,@__mo_stmt_var_2 = 4; 189 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 190 deallocate prepare __mo_stmt_id_1; 191 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 192 execute __mo_stmt_id_1; 193 id name email age 194 0 test_save_0 null 0 195 1 test_save_1 null 1 196 2 test_save_2 null 2 197 3 test_save_3 null 3 198 4 test_save_4 null 4 199 deallocate prepare __mo_stmt_id_1; 200 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 201 set @__mo_stmt_var_0 = 5,@__mo_stmt_var_1 = "test_save_5" ,@__mo_stmt_var_2 = 5; 202 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 203 deallocate prepare __mo_stmt_id_1; 204 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 205 execute __mo_stmt_id_1; 206 id name email age 207 0 test_save_0 null 0 208 1 test_save_1 null 1 209 2 test_save_2 null 2 210 3 test_save_3 null 3 211 4 test_save_4 null 4 212 5 test_save_5 null 5 213 deallocate prepare __mo_stmt_id_1; 214 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 215 set @__mo_stmt_var_0 = 6,@__mo_stmt_var_1 = "test_save_6" ,@__mo_stmt_var_2 = 6; 216 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 217 deallocate prepare __mo_stmt_id_1; 218 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 219 execute __mo_stmt_id_1; 220 id name email age 221 0 test_save_0 null 0 222 1 test_save_1 null 1 223 2 test_save_2 null 2 224 3 test_save_3 null 3 225 4 test_save_4 null 4 226 5 test_save_5 null 5 227 6 test_save_6 null 6 228 deallocate prepare __mo_stmt_id_1; 229 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 230 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 231 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 232 deallocate prepare __mo_stmt_id_1; 233 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 234 execute __mo_stmt_id_1; 235 id name email age 236 0 test_save_0 null 0 237 1 test_save_1 null 1 238 2 test_save_2 null 2 239 3 test_save_3 null 3 240 4 test_save_4 null 4 241 5 test_save_5 null 5 242 6 test_save_6 null 6 243 7 test_save_7 null 7 244 deallocate prepare __mo_stmt_id_1; 245 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 246 set @__mo_stmt_var_0 = 8,@__mo_stmt_var_1 = "test_save_8" ,@__mo_stmt_var_2 = 8; 247 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 248 deallocate prepare __mo_stmt_id_1; 249 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 250 execute __mo_stmt_id_1; 251 id name email age 252 0 test_save_0 null 0 253 1 test_save_1 null 1 254 2 test_save_2 null 2 255 3 test_save_3 null 3 256 4 test_save_4 null 4 257 5 test_save_5 null 5 258 6 test_save_6 null 6 259 7 test_save_7 null 7 260 8 test_save_8 null 8 261 deallocate prepare __mo_stmt_id_1; 262 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 263 set @__mo_stmt_var_0 = 9,@__mo_stmt_var_1 = "test_save_9" ,@__mo_stmt_var_2 = 9; 264 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 265 deallocate prepare __mo_stmt_id_1; 266 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 267 execute __mo_stmt_id_1; 268 id name email age 269 0 test_save_0 null 0 270 1 test_save_1 null 1 271 2 test_save_2 null 2 272 3 test_save_3 null 3 273 4 test_save_4 null 4 274 5 test_save_5 null 5 275 6 test_save_6 null 6 276 7 test_save_7 null 7 277 8 test_save_8 null 8 278 9 test_save_9 null 9 279 deallocate prepare __mo_stmt_id_1; 280 create table algo_offline (algo_id int(11) unsigned not null, algo_name varchar(50) not null); 281 prepare stmt1 from select count(*) from algo_offline where (algo_name like ? or algo_id like ?); 282 set @a="aa"; 283 set @b="bb"; 284 execute stmt1 using @a, @b; 285 count(*) 286 0 287 deallocate prepare stmt1; 288 drop table if exists t1; 289 create table t1 (a int, b int); 290 prepare stmt1 from 'show columns from t1'; 291 execute stmt1; 292 Field Type Null Key Default Extra Comment 293 a INT YES NULL 294 b INT YES NULL 295 prepare stmt1 from 'show variables like "aaaa"'; 296 execute stmt1; 297 Variable_name Value 298 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 299 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 300 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 301 Duplicate entry '7' for key 'id' 302 reset prepare __mo_stmt_id_1; 303 deallocate prepare __mo_stmt_id_1; 304 drop table if exists t1; 305 create table t1( a int unique key, b int, c int ); 306 prepare stmt1 from 'INSERT INTO t1 values(1, 2, 3)'; 307 execute stmt1; 308 execute stmt1; 309 Duplicate entry '1' for key '__mo_index_idx_col' 310 select * from t1; 311 a b c 312 1 2 3 313 prepare stmt1 from 'update t1 set b = ? where a = ?'; 314 set @varb = 22; 315 set @vara = 1; 316 execute stmt1 using @varb,@vara; 317 select * from t1; 318 a b c 319 1 22 3 320 drop table t1;