1###################### ps_modify.inc ######################### 2# # 3# Tests for prepared statements: INSERT/DELETE/UPDATE... # 4# # 5############################################################## 6 7# 8# NOTE: PLEASE SEE ps_1general.test (bottom) 9# BEFORE ADDING NEW TEST CASES HERE !!! 10 11# 12# Please be aware, that this file will be sourced by several test case files 13# stored within the subdirectory 't'. So every change here will affect 14# several test cases. 15# 16# Please do not modify the structure (DROP/ALTER..) of the tables 17# 't1' and 't9'. 18# 19# But you are encouraged to use these two tables within your statements 20# (DELETE/UPDATE/...) whenever possible. 21# t1 - very simple table 22# t9 - table with nearly all available column types 23# 24# The structure and the content of these tables can be found in 25# include/ps_create.inc CREATE TABLE ... 26# include/ps_renew.inc DELETE all rows and INSERT some rows 27# 28# Both tables are managed by the same storage engine. 29# The type of the storage engine is stored in the variable '$type' . 30 31 32 33#------------------- Please insert your test cases here -------------------# 34 35 36 37#-------- Please be very carefull when editing behind this line ----------# 38 39--disable_query_log 40select '------ delete tests ------' as test_sequence ; 41--enable_query_log 42--source include/ps_renew.inc 43SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 44## delete without parameter 45prepare stmt1 from 'delete from t1 where a=2' ; 46execute stmt1; 47select a,b from t1 where a=2; 48# delete with row not found 49execute stmt1; 50 51## delete with one parameter in the where clause 52insert into t1 values(0,NULL); 53set @arg00=NULL; 54prepare stmt1 from 'delete from t1 where b=?' ; 55execute stmt1 using @arg00; 56select a,b from t1 where b is NULL ; 57set @arg00='one'; 58execute stmt1 using @arg00; 59select a,b from t1 where b=@arg00; 60 61## truncate a table 62prepare stmt1 from 'truncate table t1' ; 63 64 65--disable_query_log 66select '------ update tests ------' as test_sequence ; 67--enable_query_log 68--source include/ps_renew.inc 69 70## update without parameter 71prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ; 72execute stmt1; 73select a,b from t1 where a=2; 74# dummy update 75execute stmt1; 76select a,b from t1 where a=2; 77 78## update with one parameter in the set clause 79set @arg00=NULL; 80prepare stmt1 from 'update t1 set b=? where a=2' ; 81execute stmt1 using @arg00; 82select a,b from t1 where a=2; 83set @arg00='two'; 84execute stmt1 using @arg00; 85select a,b from t1 where a=2; 86 87## update with one parameter in the where cause 88set @arg00=2; 89prepare stmt1 from 'update t1 set b=NULL where a=?' ; 90execute stmt1 using @arg00; 91select a,b from t1 where a=@arg00; 92update t1 set b='two' where a=@arg00; 93# row not found in update 94set @arg00=2000; 95execute stmt1 using @arg00; 96select a,b from t1 where a=@arg00; 97 98## update on primary key column (two parameters) 99set @arg00=2; 100set @arg01=22; 101prepare stmt1 from 'update t1 set a=? where a=?' ; 102# dummy update 103execute stmt1 using @arg00, @arg00; 104select a,b from t1 where a=@arg00; 105execute stmt1 using @arg01, @arg00; 106select a,b from t1 where a=@arg01; 107execute stmt1 using @arg00, @arg01; 108select a,b from t1 where a=@arg00; 109set @arg00=NULL; 110set @arg01=2; 111execute stmt1 using @arg00, @arg01; 112select a,b from t1 order by a; 113set @arg00=0; 114execute stmt1 using @arg01, @arg00; 115select a,b from t1 order by a; 116 117## update with subquery and several parameters 118set @arg00=23; 119set @arg01='two'; 120set @arg02=2; 121set @arg03='two'; 122set @arg04=2; 123--disable_warnings 124drop table if exists t2; 125--enable_warnings 126# t2 will be of table type 'MYISAM' 127create table t2 as select a,b from t1 ; 128prepare stmt1 from 'update t1 set a=? where b=? 129 and a in (select ? from t2 130 where b = ? or a = ?)'; 131--enable_info 132execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; 133--disable_info 134select a,b from t1 where a = @arg00 ; 135prepare stmt1 from 'update t1 set a=? where b=? 136 and a not in (select ? from t2 137 where b = ? or a = ?)'; 138--enable_info 139execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; 140--disable_info 141select a,b from t1 order by a ; 142drop table t2 ; 143# t2 is now of table type '$type' 144# The test battery for table type 'MERGE' gets here only a 'MYISAM' table 145# 146# Test UPDATE with SUBQUERY in prepared mode 147# 148eval create table t2 149( 150 a int, b varchar(30), 151 primary key(a) 152) engine = $type ; 153insert into t2(a,b) select a, b from t1 ; 154prepare stmt1 from 'update t1 set a=? where b=? 155 and a in (select ? from t2 156 where b = ? or a = ?)'; 157--enable_info 158execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; 159--disable_info 160select a,b from t1 where a = @arg00 ; 161prepare stmt1 from 'update t1 set a=? where b=? 162 and a not in (select ? from t2 163 where b = ? or a = ?)'; 164--enable_info 165execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; 166--disable_info 167select a,b from t1 order by a ; 168drop table t2 ; 169 170## update with parameters in limit 171set @arg00=1; 172prepare stmt1 from 'update t1 set b=''bla'' 173where a=2 174limit 1'; 175execute stmt1 ; 176select a,b from t1 where b = 'bla' ; 177prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?'; 178execute stmt1 using @arg00; 179 180--disable_query_log 181select '------ insert tests ------' as test_sequence ; 182--enable_query_log 183--source include/ps_renew.inc 184 185## insert without parameter 186prepare stmt1 from 'insert into t1 values(5, ''five'' )'; 187execute stmt1; 188select a,b from t1 where a = 5; 189 190## insert with one parameter in values part 191set @arg00='six' ; 192prepare stmt1 from 'insert into t1 values(6, ? )'; 193execute stmt1 using @arg00; 194select a,b from t1 where b = @arg00; 195# the second insert fails, because the first column is primary key 196--error ER_DUP_ENTRY 197execute stmt1 using @arg00; 198set @arg00=NULL ; 199prepare stmt1 from 'insert into t1 values(0, ? )'; 200execute stmt1 using @arg00; 201select a,b from t1 where b is NULL; 202 203## insert with two parameter in values part 204set @arg00=8 ; 205set @arg01='eight' ; 206prepare stmt1 from 'insert into t1 values(?, ? )'; 207execute stmt1 using @arg00, @arg01 ; 208select a,b from t1 where b = @arg01; 209# cases derived from client_test.c: test_null() 210set @NULL= null ; 211set @arg00= 'abc' ; 212# execute must fail, because first column is primary key (-> not null) 213--error 1048 214execute stmt1 using @NULL, @NULL ; 215--error 1048 216execute stmt1 using @NULL, @NULL ; 217--error 1048 218execute stmt1 using @NULL, @arg00 ; 219--error 1048 220execute stmt1 using @NULL, @arg00 ; 221let $1 = 2; 222while ($1) 223{ 224 eval set @arg01= 10000 + $1 ; 225 execute stmt1 using @arg01, @arg00 ; 226 dec $1; 227} 228select * from t1 where a > 10000 order by a ; 229delete from t1 where a > 10000 ; 230let $1 = 2; 231while ($1) 232{ 233 eval set @arg01= 10000 + $1 ; 234 execute stmt1 using @arg01, @NULL ; 235 dec $1; 236} 237select * from t1 where a > 10000 order by a ; 238delete from t1 where a > 10000 ; 239let $1 = 10; 240while ($1) 241{ 242 eval set @arg01= 10000 + $1 ; 243 execute stmt1 using @arg01, @arg01 ; 244 dec $1; 245} 246select * from t1 where a > 10000 order by a ; 247delete from t1 where a > 10000 ; 248 249 250## insert with two rows in values part 251set @arg00=81 ; 252set @arg01='8-1' ; 253set @arg02=82 ; 254set @arg03='8-2' ; 255prepare stmt1 from 'insert into t1 values(?,?),(?,?)'; 256execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; 257--sorted_result 258select a,b from t1 where a in (@arg00,@arg02) ; 259 260## insert with two parameter in the set part 261set @arg00=9 ; 262set @arg01='nine' ; 263prepare stmt1 from 'insert into t1 set a=?, b=? '; 264execute stmt1 using @arg00, @arg01 ; 265select a,b from t1 where a = @arg00 ; 266 267## insert with parameters in the ON DUPLICATE KEY part 268set @arg00=6 ; 269set @arg01=1 ; 270prepare stmt1 from 'insert into t1 set a=?, b=''sechs'' 271 on duplicate key update a=a + ?, b=concat(b,''modified'') '; 272execute stmt1 using @arg00, @arg01; 273select * from t1 order by a; 274set @arg00=81 ; 275set @arg01=1 ; 276--error ER_DUP_ENTRY 277execute stmt1 using @arg00, @arg01; 278 279## insert, autoincrement column and ' SELECT LAST_INSERT_ID() ' 280# cases derived from client_test.c: test_bug3117() 281--disable_warnings 282drop table if exists t2 ; 283--enable_warnings 284# The test battery for table type 'MERGE' gets here only a 'MYISAM' table 285eval create table t2 (id int auto_increment primary key) 286ENGINE= $type ; 287prepare stmt1 from ' select last_insert_id() ' ; 288insert into t2 values (NULL) ; 289execute stmt1 ; 290insert into t2 values (NULL) ; 291# bug#3117 292execute stmt1 ; 293drop table t2 ; 294 295## many parameters 296set @1000=1000 ; 297set @x1000_2="x1000_2" ; 298set @x1000_3="x1000_3" ; 299 300set @x1000="x1000" ; 301set @1100=1100 ; 302set @x1100="x1100" ; 303set @100=100 ; 304set @updated="updated" ; 305insert into t1 values(1000,'x1000_1') ; 306insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3) 307 on duplicate key update a = a + @100, b = concat(b,@updated) ; 308select a,b from t1 where a >= 1000 order by a ; 309delete from t1 where a >= 1000 ; 310insert into t1 values(1000,'x1000_1') ; 311prepare stmt1 from ' insert into t1 values(?,?),(?,?) 312 on duplicate key update a = a + ?, b = concat(b,?) '; 313execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ; 314select a,b from t1 where a >= 1000 order by a ; 315delete from t1 where a >= 1000 ; 316insert into t1 values(1000,'x1000_1') ; 317execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ; 318select a,b from t1 where a >= 1000 order by a ; 319delete from t1 where a >= 1000 ; 320 321## replace 322prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' '; 323execute stmt1; 324execute stmt1; 325execute stmt1; 326 327 328 329## multi table statements 330--disable_query_log 331select '------ multi table tests ------' as test_sequence ; 332--enable_query_log 333# cases derived from client_test.c: test_multi 334delete from t1 ; 335delete from t9 ; 336insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ; 337insert into t9 (c1,c21) 338 values (1, 'one'), (2, 'two'), (3, 'three') ; 339prepare stmt_delete from " delete t1, t9 340 from t1, t9 where t1.a=t9.c1 and t1.b='updated' "; 341prepare stmt_update from " update t1, t9 342 set t1.b='updated', t9.c21='updated' 343 where t1.a=t9.c1 and t1.a=? "; 344prepare stmt_select1 from " select a, b from t1 order by a" ; 345prepare stmt_select2 from " select c1, c21 from t9 order by c1" ; 346set @arg00= 1 ; 347let $1= 3 ; 348while ($1) 349{ 350 execute stmt_update using @arg00 ; 351 execute stmt_delete ; 352 execute stmt_select1 ; 353 execute stmt_select2 ; 354 set @arg00= @arg00 + 1 ; 355 dec $1 ; 356} 357SET sql_mode = default; 358