1
###################### ps_modify.inc #########################
3
# Tests for prepared statements: INSERT/DELETE/UPDATE... #
5
##############################################################
8
# NOTE: PLEASE SEE ps_1general.test (bottom)
9
# BEFORE ADDING NEW TEST CASES HERE !!!
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
16
# Please do not modify the structure (DROP/ALTER..) of the tables
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
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
28
# Both tables are managed by the same storage engine.
29
# The type of the storage engine is stored in the variable '$type' .
33
#------------------- Please insert your test cases here -------------------#
37
#-------- Please be very carefull when editing behind this line ----------#
40
select '------ delete tests ------' as test_sequence ;
42
--source include/ps_renew.inc
44
## delete without parameter
45
prepare stmt1 from 'delete from t1 where a=2' ;
47
select a,b from t1 where a=2;
48
# delete with row not found
51
## delete with one parameter in the where clause
52
insert into t1 values(0,NULL);
54
prepare stmt1 from 'delete from t1 where b=?' ;
55
execute stmt1 using @arg00;
56
select a,b from t1 where b is NULL ;
58
execute stmt1 using @arg00;
59
select a,b from t1 where b=@arg00;
62
prepare stmt1 from 'truncate table t1' ;
66
select '------ update tests ------' as test_sequence ;
68
--source include/ps_renew.inc
70
## update without parameter
71
prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;
73
select a,b from t1 where a=2;
76
select a,b from t1 where a=2;
78
## update with one parameter in the set clause
80
prepare stmt1 from 'update t1 set b=? where a=2' ;
81
execute stmt1 using @arg00;
82
select a,b from t1 where a=2;
84
execute stmt1 using @arg00;
85
select a,b from t1 where a=2;
87
## update with one parameter in the where cause
89
prepare stmt1 from 'update t1 set b=NULL where a=?' ;
90
execute stmt1 using @arg00;
91
select a,b from t1 where a=@arg00;
92
update t1 set b='two' where a=@arg00;
93
# row not found in update
95
execute stmt1 using @arg00;
96
select a,b from t1 where a=@arg00;
98
## update on primary key column (two parameters)
101
prepare stmt1 from 'update t1 set a=? where a=?' ;
103
execute stmt1 using @arg00, @arg00;
104
select a,b from t1 where a=@arg00;
105
execute stmt1 using @arg01, @arg00;
106
select a,b from t1 where a=@arg01;
107
execute stmt1 using @arg00, @arg01;
108
select a,b from t1 where a=@arg00;
111
execute stmt1 using @arg00, @arg01;
112
select a,b from t1 order by a;
114
execute stmt1 using @arg01, @arg00;
115
select a,b from t1 order by a;
117
## update with subquery and several parameters
124
drop table if exists t2;
126
# t2 will be of table type 'MYISAM'
127
create table t2 as select a,b from t1 ;
128
prepare stmt1 from 'update t1 set a=? where b=?
129
and a in (select ? from t2
130
where b = ? or a = ?)';
132
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
134
select a,b from t1 where a = @arg00 ;
135
prepare stmt1 from 'update t1 set a=? where b=?
136
and a not in (select ? from t2
137
where b = ? or a = ?)';
139
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
141
select a,b from t1 order by a ;
143
# t2 is now of table type '$type'
144
# The test battery for table type 'MERGE' gets here only a 'MYISAM' table
146
# Test UPDATE with SUBQUERY in prepared mode
150
a int, b varchar(30),
153
insert into t2(a,b) select a, b from t1 ;
154
prepare stmt1 from 'update t1 set a=? where b=?
155
and a in (select ? from t2
156
where b = ? or a = ?)';
158
execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;
160
select a,b from t1 where a = @arg00 ;
161
prepare stmt1 from 'update t1 set a=? where b=?
162
and a not in (select ? from t2
163
where b = ? or a = ?)';
165
execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;
167
select a,b from t1 order by a ;
170
## update with parameters in limit
172
prepare stmt1 from 'update t1 set b=''bla''
176
select a,b from t1 where b = 'bla' ;
177
prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
178
execute stmt1 using @arg00;
181
select '------ insert tests ------' as test_sequence ;
183
--source include/ps_renew.inc
185
## insert without parameter
186
prepare stmt1 from 'insert into t1 values(5, ''five'' )';
188
select a,b from t1 where a = 5;
190
## insert with one parameter in values part
192
prepare stmt1 from 'insert into t1 values(6, ? )';
193
execute stmt1 using @arg00;
194
select a,b from t1 where b = @arg00;
195
# the second insert fails, because the first column is primary key
197
execute stmt1 using @arg00;
199
prepare stmt1 from 'insert into t1 values(0, ? )';
200
execute stmt1 using @arg00;
201
select a,b from t1 where b is NULL;
203
## insert with two parameter in values part
206
prepare stmt1 from 'insert into t1 values(?, ? )';
207
execute stmt1 using @arg00, @arg01 ;
208
select a,b from t1 where b = @arg01;
209
# cases derived from client_test.c: test_null()
212
# execute must fail, because first column is primary key (-> not null)
214
execute stmt1 using @NULL, @NULL ;
216
execute stmt1 using @NULL, @NULL ;
218
execute stmt1 using @NULL, @arg00 ;
220
execute stmt1 using @NULL, @arg00 ;
224
eval set @arg01= 10000 + $1 ;
225
execute stmt1 using @arg01, @arg00 ;
228
select * from t1 where a > 10000 order by a ;
229
delete from t1 where a > 10000 ;
233
eval set @arg01= 10000 + $1 ;
234
execute stmt1 using @arg01, @NULL ;
237
select * from t1 where a > 10000 order by a ;
238
delete from t1 where a > 10000 ;
242
eval set @arg01= 10000 + $1 ;
243
execute stmt1 using @arg01, @arg01 ;
246
select * from t1 where a > 10000 order by a ;
247
delete from t1 where a > 10000 ;
250
## insert with two rows in values part
255
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
256
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
257
select a,b from t1 where a in (@arg00,@arg02) ;
259
## insert with two parameter in the set part
262
prepare stmt1 from 'insert into t1 set a=?, b=? ';
263
execute stmt1 using @arg00, @arg01 ;
264
select a,b from t1 where a = @arg00 ;
266
## insert with parameters in the ON DUPLICATE KEY part
269
prepare stmt1 from 'insert into t1 set a=?, b=''sechs''
270
on duplicate key update a=a + ?, b=concat(b,''modified'') ';
271
execute stmt1 using @arg00, @arg01;
272
select * from t1 order by a;
276
execute stmt1 using @arg00, @arg01;
278
## insert, autoincrement column and ' SELECT LAST_INSERT_ID() '
279
# cases derived from client_test.c: test_bug3117()
281
drop table if exists t2 ;
283
# The test battery for table type 'MERGE' gets here only a 'MYISAM' table
284
eval create table t2 (id int auto_increment primary key)
286
prepare stmt1 from ' select last_insert_id() ' ;
287
insert into t2 values (NULL) ;
289
insert into t2 values (NULL) ;
296
set @x1000_2="x1000_2" ;
297
set @x1000_3="x1000_3" ;
303
set @updated="updated" ;
304
insert into t1 values(1000,'x1000_1') ;
305
insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)
306
on duplicate key update a = a + @100, b = concat(b,@updated) ;
307
select a,b from t1 where a >= 1000 order by a ;
308
delete from t1 where a >= 1000 ;
309
insert into t1 values(1000,'x1000_1') ;
310
prepare stmt1 from ' insert into t1 values(?,?),(?,?)
311
on duplicate key update a = a + ?, b = concat(b,?) ';
312
execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;
313
select a,b from t1 where a >= 1000 order by a ;
314
delete from t1 where a >= 1000 ;
315
insert into t1 values(1000,'x1000_1') ;
316
execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;
317
select a,b from t1 where a >= 1000 order by a ;
318
delete from t1 where a >= 1000 ;
321
prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';
328
## multi table statements
330
select '------ multi table tests ------' as test_sequence ;
332
# cases derived from client_test.c: test_multi
335
insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;
336
insert into t9 (c1,c21)
337
values (1, 'one'), (2, 'two'), (3, 'three') ;
338
prepare stmt_delete from " delete t1, t9
339
from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";
340
prepare stmt_update from " update t1, t9
341
set t1.b='updated', t9.c21='updated'
342
where t1.a=t9.c1 and t1.a=? ";
343
prepare stmt_select1 from " select a, b from t1 order by a" ;
344
prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;
349
execute stmt_update using @arg00 ;
350
execute stmt_delete ;
351
execute stmt_select1 ;
352
execute stmt_select2 ;
353
set @arg00= @arg00 + 1 ;