github.com/matrixorigin/matrixone@v1.2.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(6)); 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 rollback; 109 prepare s1 from "create table t2(a int)"; 110 commit; 111 prepare s2 from "drop table t1"; 112 commit; 113 insert into t1 values (1); 114 execute s1; 115 execute s2; 116 rollback; 117 set autocommit=1; 118 create table bmsql_district ( d_w_id integer not null, 119 d_id integer not null, 120 d_ytd decimal(12,2), 121 d_tax decimal(4,4), 122 d_next_o_id integer, 123 d_name varchar(10), 124 d_street_1 varchar(20), 125 d_street_2 varchar(20), 126 d_city varchar(20), 127 d_state char(2), 128 d_zip char(9), 129 primary key (d_w_id, d_id) ) PARTITION BY KEY(d_w_id); 130 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 = ?; 131 set @__mo_stmt_var_0=1, @__mo_stmt_var_1=7; 132 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1; 133 deallocate prepare __mo_stmt_id_1; 134 drop table if exists user; 135 CREATE TABLE user 136 ( 137 id BIGINT(20) NOT NULL COMMENT '主键ID', 138 name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', 139 age INT(11) NULL DEFAULT NULL COMMENT '年龄', 140 email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', 141 PRIMARY KEY (id) 142 ); 143 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 144 set @__mo_stmt_var_0 = 0,@__mo_stmt_var_1 = "test_save_0" ,@__mo_stmt_var_2 = 0; 145 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 146 deallocate prepare __mo_stmt_id_1; 147 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 148 execute __mo_stmt_id_1; 149 id name email age 150 0 test_save_0 null 0 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 = 1,@__mo_stmt_var_1 = "test_save_1" ,@__mo_stmt_var_2 = 1; 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 id name email age 159 0 test_save_0 null 0 160 1 test_save_1 null 1 161 deallocate prepare __mo_stmt_id_1; 162 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 163 set @__mo_stmt_var_0 = 2,@__mo_stmt_var_1 = "test_save_2" ,@__mo_stmt_var_2 = 2; 164 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 165 deallocate prepare __mo_stmt_id_1; 166 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 167 execute __mo_stmt_id_1; 168 id name email age 169 0 test_save_0 null 0 170 1 test_save_1 null 1 171 2 test_save_2 null 2 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 = 3,@__mo_stmt_var_1 = "test_save_3" ,@__mo_stmt_var_2 = 3; 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 id name email age 180 0 test_save_0 null 0 181 1 test_save_1 null 1 182 2 test_save_2 null 2 183 3 test_save_3 null 3 184 deallocate prepare __mo_stmt_id_1; 185 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 186 set @__mo_stmt_var_0 = 4,@__mo_stmt_var_1 = "test_save_4" ,@__mo_stmt_var_2 = 4; 187 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 188 deallocate prepare __mo_stmt_id_1; 189 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 190 execute __mo_stmt_id_1; 191 id name email age 192 0 test_save_0 null 0 193 1 test_save_1 null 1 194 2 test_save_2 null 2 195 3 test_save_3 null 3 196 4 test_save_4 null 4 197 deallocate prepare __mo_stmt_id_1; 198 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 199 set @__mo_stmt_var_0 = 5,@__mo_stmt_var_1 = "test_save_5" ,@__mo_stmt_var_2 = 5; 200 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 201 deallocate prepare __mo_stmt_id_1; 202 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 203 execute __mo_stmt_id_1; 204 id name email age 205 0 test_save_0 null 0 206 1 test_save_1 null 1 207 2 test_save_2 null 2 208 3 test_save_3 null 3 209 4 test_save_4 null 4 210 5 test_save_5 null 5 211 deallocate prepare __mo_stmt_id_1; 212 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 213 set @__mo_stmt_var_0 = 6,@__mo_stmt_var_1 = "test_save_6" ,@__mo_stmt_var_2 = 6; 214 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 215 deallocate prepare __mo_stmt_id_1; 216 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 217 execute __mo_stmt_id_1; 218 id name email age 219 0 test_save_0 null 0 220 1 test_save_1 null 1 221 2 test_save_2 null 2 222 3 test_save_3 null 3 223 4 test_save_4 null 4 224 5 test_save_5 null 5 225 6 test_save_6 null 6 226 deallocate prepare __mo_stmt_id_1; 227 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 228 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 229 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 230 deallocate prepare __mo_stmt_id_1; 231 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 232 execute __mo_stmt_id_1; 233 id name email age 234 0 test_save_0 null 0 235 1 test_save_1 null 1 236 2 test_save_2 null 2 237 3 test_save_3 null 3 238 4 test_save_4 null 4 239 5 test_save_5 null 5 240 6 test_save_6 null 6 241 7 test_save_7 null 7 242 deallocate prepare __mo_stmt_id_1; 243 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 244 set @__mo_stmt_var_0 = 8,@__mo_stmt_var_1 = "test_save_8" ,@__mo_stmt_var_2 = 8; 245 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 246 deallocate prepare __mo_stmt_id_1; 247 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 248 execute __mo_stmt_id_1; 249 id name email age 250 0 test_save_0 null 0 251 1 test_save_1 null 1 252 2 test_save_2 null 2 253 3 test_save_3 null 3 254 4 test_save_4 null 4 255 5 test_save_5 null 5 256 6 test_save_6 null 6 257 7 test_save_7 null 7 258 8 test_save_8 null 8 259 deallocate prepare __mo_stmt_id_1; 260 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 261 set @__mo_stmt_var_0 = 9,@__mo_stmt_var_1 = "test_save_9" ,@__mo_stmt_var_2 = 9; 262 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 263 deallocate prepare __mo_stmt_id_1; 264 prepare __mo_stmt_id_1 from 'select id, name, email, age from user'; 265 execute __mo_stmt_id_1; 266 id name email age 267 0 test_save_0 null 0 268 1 test_save_1 null 1 269 2 test_save_2 null 2 270 3 test_save_3 null 3 271 4 test_save_4 null 4 272 5 test_save_5 null 5 273 6 test_save_6 null 6 274 7 test_save_7 null 7 275 8 test_save_8 null 8 276 9 test_save_9 null 9 277 deallocate prepare __mo_stmt_id_1; 278 create table algo_offline (algo_id int(11) unsigned not null, algo_name varchar(50) not null); 279 prepare stmt1 from select count(*) from algo_offline where (algo_name like ? or algo_id like ?); 280 set @a="aa"; 281 set @b="bb"; 282 execute stmt1 using @a, @b; 283 count(*) 284 0 285 deallocate prepare stmt1; 286 drop table if exists t1; 287 create table t1 (a int, b int); 288 prepare stmt1 from 'show columns from t1'; 289 execute stmt1; 290 Field Type Null Key Default Extra Comment 291 a INT(32) YES null 292 b INT(32) YES null 293 prepare stmt1 from 'show variables like "aaaa"'; 294 execute stmt1; 295 Variable_name Value 296 prepare __mo_stmt_id_1 from 'INSERT INTO user ( id, name, age ) VALUES ( ?, ?,? )'; 297 set @__mo_stmt_var_0 = 7,@__mo_stmt_var_1 = "test_save_7" ,@__mo_stmt_var_2 = 7; 298 execute __mo_stmt_id_1 using @__mo_stmt_var_0,@__mo_stmt_var_1,@__mo_stmt_var_2; 299 Duplicate entry '7' for key 'id' 300 reset prepare __mo_stmt_id_1; 301 deallocate prepare __mo_stmt_id_1; 302 drop table if exists t1; 303 create table t1( a int unique key, b int, c int ); 304 prepare stmt1 from 'INSERT INTO t1 values(1, 2, 3)'; 305 execute stmt1; 306 execute stmt1; 307 Duplicate entry '1' for key '__mo_index_idx_col' 308 select * from t1; 309 a b c 310 1 2 3 311 prepare stmt1 from 'update t1 set b = ? where a = ?'; 312 set @varb = 22; 313 set @vara = 1; 314 execute stmt1 using @varb,@vara; 315 select * from t1; 316 a b c 317 1 22 3 318 drop table t1; 319 CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT); 320 INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807); 321 SELECT * FROM numbers WHERE ui=-9223372036854775808; 322 pk ui si 323 SET @si_min = -9223372036854775808; 324 PREPARE s1 FROM 'SELECT * FROM numbers WHERE ui=?'; 325 EXECUTE s1 USING @si_min; 326 invalid argument cast to uint64, bad value -9223372036854775808 327 DEALLOCATE PREPARE s1; 328 drop table numbers; 329 drop table if exists t1; 330 create table t1 (a int); 331 prepare stmt1 from select * from t1; 332 execute stmt1; 333 a 334 alter table t1 add column b int after a; 335 execute stmt1; 336 internal error: table 't1' has been changed, please reset prepare statement 'stmt1' 337 prepare stmt1 from select * from t1; 338 execute stmt1; 339 a b 340 truncate table t1; 341 execute stmt1; 342 internal error: table 't1' has been changed, please reset prepare statement 'stmt1' 343 prepare stmt1 from select * from t1; 344 execute stmt1; 345 a b 346 drop table t1; 347 execute stmt1; 348 internal error: table 't1' in prepare statement 'stmt1' does not exist anymore 349 deallocate prepare stmt1; 350 create database abc; 351 use abc; 352 drop table if exists t1; 353 create table t1 (a int, b int); 354 insert into t1 values(1, 1); 355 insert into t1 values(2, 2); 356 prepare stmt1 from select * from t1 limit ?; 357 set @a_var = 1; 358 execute stmt1 using @a_var; 359 a b 360 1 1 361 prepare stmt2 from select * from t1 limit ?; 362 set @b_var = '1'; 363 execute stmt2 using @b_var; 364 a b 365 1 1 366 deallocate prepare stmt1; 367 deallocate prepare stmt2; 368 insert into t1 values(3, 3); 369 insert into t1 values(4, 4); 370 insert into t1 values(5, 5); 371 prepare stmt3 from select * from t1 limit ? offset ?; 372 set @a_var = 2; 373 set @b_var = 0; 374 execute stmt3 using @a_var, @b_var; 375 a b 376 1 1 377 2 2 378 select * from t1 where a > ?; 379 invalid input: only prepare statement can use ? expr 380 deallocate prepare stmt3; 381 drop database abc; 382 create database prepare_test; 383 use prepare_test; 384 CREATE TABLE m_user ( 385 m_id INT NOT NULL, 386 m_name CHAR(25) NOT NULL 387 ); 388 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name=?;'; 389 set @a_var_1 = 111; 390 set @a_var_2 = "aaaa"; 391 set @a_var_3 = "bbb"; 392 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 393 select * from m_user; 394 m_id m_name 395 111 aaaa 396 deallocate prepare stmt1; 397 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name= substring(?, 1, 3);'; 398 set @a_var_1 = 111; 399 set @a_var_2 = "aaaa"; 400 set @a_var_3 = "bbbbbbb"; 401 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 402 select * from m_user; 403 m_id m_name 404 111 aaaa 405 111 aaaa 406 deallocate prepare stmt1; 407 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_id= m_id + ?;'; 408 set @a_var_1 = 111; 409 set @a_var_2 = "aaaa"; 410 set @a_var_3 = 1; 411 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 412 select * from m_user; 413 m_id m_name 414 111 aaaa 415 111 aaaa 416 111 aaaa 417 deallocate prepare stmt1; 418 drop table m_user; 419 CREATE TABLE m_user ( 420 m_id INT NOT NULL primary key, 421 m_name CHAR(25) NOT NULL 422 ); 423 prepare stmt1 from 'INSERT INTO m_user SET m_id=?, m_name=? ON DUPLICATE KEY UPDATE m_name=?;'; 424 set @a_var_1 = 111; 425 set @a_var_2 = "aaaa"; 426 set @a_var_3 = "bbb"; 427 execute stmt1 using @a_var_1, @a_var_2, @a_var_3; 428 select * from m_user; 429 m_id m_name 430 111 aaaa 431 deallocate prepare stmt1; 432 drop table m_user; 433 drop database prepare_test;