diff options
Diffstat (limited to 'mysql-test/include/ps_modify.inc')
| -rw-r--r-- | mysql-test/include/ps_modify.inc | 358 |
1 files changed, 358 insertions, 0 deletions
diff --git a/mysql-test/include/ps_modify.inc b/mysql-test/include/ps_modify.inc new file mode 100644 index 00000000..d4cd4dbe --- /dev/null +++ b/mysql-test/include/ps_modify.inc @@ -0,0 +1,358 @@ +###################### ps_modify.inc ######################### +# # +# Tests for prepared statements: INSERT/DELETE/UPDATE... # +# # +############################################################## + +# +# NOTE: PLEASE SEE ps_1general.test (bottom) +# BEFORE ADDING NEW TEST CASES HERE !!! + +# +# Please be aware, that this file will be sourced by several test case files +# stored within the subdirectory 't'. So every change here will affect +# several test cases. +# +# Please do not modify the structure (DROP/ALTER..) of the tables +# 't1' and 't9'. +# +# But you are encouraged to use these two tables within your statements +# (DELETE/UPDATE/...) whenever possible. +# t1 - very simple table +# t9 - table with nearly all available column types +# +# The structure and the content of these tables can be found in +# include/ps_create.inc CREATE TABLE ... +# include/ps_renew.inc DELETE all rows and INSERT some rows +# +# Both tables are managed by the same storage engine. +# The type of the storage engine is stored in the variable '$type' . + + + +#------------------- Please insert your test cases here -------------------# + + + +#-------- Please be very carefull when editing behind this line ----------# + +--disable_query_log +select '------ delete tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## delete without parameter +prepare stmt1 from 'delete from t1 where a=2' ; +execute stmt1; +select a,b from t1 where a=2; +# delete with row not found +execute stmt1; + +## delete with one parameter in the where clause +insert into t1 values(0,NULL); +set @arg00=NULL; +prepare stmt1 from 'delete from t1 where b=?' ; +execute stmt1 using @arg00; +select a,b from t1 where b is NULL ; +set @arg00='one'; +execute stmt1 using @arg00; +select a,b from t1 where b=@arg00; + +## truncate a table +prepare stmt1 from 'truncate table t1' ; + + +--disable_query_log +select '------ update tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## update without parameter +prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ; +execute stmt1; +select a,b from t1 where a=2; +# dummy update +execute stmt1; +select a,b from t1 where a=2; + +## update with one parameter in the set clause +set @arg00=NULL; +prepare stmt1 from 'update t1 set b=? where a=2' ; +execute stmt1 using @arg00; +select a,b from t1 where a=2; +set @arg00='two'; +execute stmt1 using @arg00; +select a,b from t1 where a=2; + +## update with one parameter in the where cause +set @arg00=2; +prepare stmt1 from 'update t1 set b=NULL where a=?' ; +execute stmt1 using @arg00; +select a,b from t1 where a=@arg00; +update t1 set b='two' where a=@arg00; +# row not found in update +set @arg00=2000; +execute stmt1 using @arg00; +select a,b from t1 where a=@arg00; + +## update on primary key column (two parameters) +set @arg00=2; +set @arg01=22; +prepare stmt1 from 'update t1 set a=? where a=?' ; +# dummy update +execute stmt1 using @arg00, @arg00; +select a,b from t1 where a=@arg00; +execute stmt1 using @arg01, @arg00; +select a,b from t1 where a=@arg01; +execute stmt1 using @arg00, @arg01; +select a,b from t1 where a=@arg00; +set @arg00=NULL; +set @arg01=2; +set sql_mode = ''; +execute stmt1 using @arg00, @arg01; +set sql_mode = default; +select a,b from t1 order by a; +set @arg00=0; +execute stmt1 using @arg01, @arg00; +select a,b from t1 order by a; + +## update with subquery and several parameters +set @arg00=23; +set @arg01='two'; +set @arg02=2; +set @arg03='two'; +set @arg04=2; +--disable_warnings +drop table if exists t2; +--enable_warnings +# t2 will be of table type 'MYISAM' +create table t2 as select a,b from t1 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; +--disable_info +select a,b from t1 where a = @arg00 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a not in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; +--disable_info +select a,b from t1 order by a ; +drop table t2 ; +# t2 is now of table type '$type' +# The test battery for table type 'MERGE' gets here only a 'MYISAM' table +# +# Test UPDATE with SUBQUERY in prepared mode +# +eval create table t2 +( + a int, b varchar(30), + primary key(a) +) engine = $type ; +insert into t2(a,b) select a, b from t1 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ; +--disable_info +select a,b from t1 where a = @arg00 ; +prepare stmt1 from 'update t1 set a=? where b=? + and a not in (select ? from t2 + where b = ? or a = ?)'; +--enable_info +execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ; +--disable_info +select a,b from t1 order by a ; +drop table t2 ; + +## update with parameters in limit +set @arg00=1; +prepare stmt1 from 'update t1 set b=''bla'' +where a=2 +limit 1'; +execute stmt1 ; +select a,b from t1 where b = 'bla' ; +prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?'; +execute stmt1 using @arg00; + +--disable_query_log +select '------ insert tests ------' as test_sequence ; +--enable_query_log +--source include/ps_renew.inc + +## insert without parameter +prepare stmt1 from 'insert into t1 values(5, ''five'' )'; +execute stmt1; +select a,b from t1 where a = 5; + +## insert with one parameter in values part +set @arg00='six' ; +prepare stmt1 from 'insert into t1 values(6, ? )'; +execute stmt1 using @arg00; +select a,b from t1 where b = @arg00; +# the second insert fails, because the first column is primary key +--error ER_DUP_ENTRY +execute stmt1 using @arg00; +set @arg00=NULL ; +prepare stmt1 from 'insert into t1 values(0, ? )'; +execute stmt1 using @arg00; +select a,b from t1 where b is NULL; + +## insert with two parameter in values part +set @arg00=8 ; +set @arg01='eight' ; +prepare stmt1 from 'insert into t1 values(?, ? )'; +execute stmt1 using @arg00, @arg01 ; +select a,b from t1 where b = @arg01; +# cases derived from client_test.c: test_null() +set @NULL= null ; +set @arg00= 'abc' ; +# execute must fail, because first column is primary key (-> not null) +--error 1048 +execute stmt1 using @NULL, @NULL ; +--error 1048 +execute stmt1 using @NULL, @NULL ; +--error 1048 +execute stmt1 using @NULL, @arg00 ; +--error 1048 +execute stmt1 using @NULL, @arg00 ; +let $1 = 2; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @arg00 ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; +let $1 = 2; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @NULL ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; +let $1 = 10; +while ($1) +{ + eval set @arg01= 10000 + $1 ; + execute stmt1 using @arg01, @arg01 ; + dec $1; +} +select * from t1 where a > 10000 order by a ; +delete from t1 where a > 10000 ; + + +## insert with two rows in values part +set @arg00=81 ; +set @arg01='8-1' ; +set @arg02=82 ; +set @arg03='8-2' ; +prepare stmt1 from 'insert into t1 values(?,?),(?,?)'; +execute stmt1 using @arg00, @arg01, @arg02, @arg03 ; +select a,b from t1 where a in (@arg00,@arg02) ; + +## insert with two parameter in the set part +set @arg00=9 ; +set @arg01='nine' ; +prepare stmt1 from 'insert into t1 set a=?, b=? '; +execute stmt1 using @arg00, @arg01 ; +select a,b from t1 where a = @arg00 ; + +## insert with parameters in the ON DUPLICATE KEY part +set @arg00=6 ; +set @arg01=1 ; +prepare stmt1 from 'insert into t1 set a=?, b=''sechs'' + on duplicate key update a=a + ?, b=concat(b,''modified'') '; +execute stmt1 using @arg00, @arg01; +select * from t1 order by a; +set @arg00=81 ; +set @arg01=1 ; +--error ER_DUP_ENTRY +execute stmt1 using @arg00, @arg01; + +## insert, autoincrement column and ' SELECT LAST_INSERT_ID() ' +# cases derived from client_test.c: test_bug3117() +--disable_warnings +drop table if exists t2 ; +--enable_warnings +# The test battery for table type 'MERGE' gets here only a 'MYISAM' table +eval create table t2 (id int auto_increment primary key) +ENGINE= $type ; +prepare stmt1 from ' select last_insert_id() ' ; +insert into t2 values (NULL) ; +execute stmt1 ; +insert into t2 values (NULL) ; +# bug#3117 +execute stmt1 ; +drop table t2 ; + +## many parameters +set @1000=1000 ; +set @x1000_2="x1000_2" ; +set @x1000_3="x1000_3" ; + +set @x1000="x1000" ; +set @1100=1100 ; +set @x1100="x1100" ; +set @100=100 ; +set @updated="updated" ; +insert into t1 values(1000,'x1000_1') ; +insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3) + on duplicate key update a = a + @100, b = concat(b,@updated) ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; +insert into t1 values(1000,'x1000_1') ; +prepare stmt1 from ' insert into t1 values(?,?),(?,?) + on duplicate key update a = a + ?, b = concat(b,?) '; +execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; +insert into t1 values(1000,'x1000_1') ; +execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ; +select a,b from t1 where a >= 1000 order by a ; +delete from t1 where a >= 1000 ; + +## replace +prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' '; +execute stmt1; +execute stmt1; +execute stmt1; + + + +## multi table statements +--disable_query_log +select '------ multi table tests ------' as test_sequence ; +--enable_query_log +# cases derived from client_test.c: test_multi +delete from t1 ; +delete from t9 ; +insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ; +insert into t9 (c1,c21) + values (1, 'one'), (2, 'two'), (3, 'three') ; +prepare stmt_delete from " delete t1, t9 + from t1, t9 where t1.a=t9.c1 and t1.b='updated' "; +prepare stmt_update from " update t1, t9 + set t1.b='updated', t9.c21='updated' + where t1.a=t9.c1 and t1.a=? "; +prepare stmt_select1 from " select a, b from t1 order by a" ; +prepare stmt_select2 from " select c1, c21 from t9 order by c1" ; +set @arg00= 1 ; +let $1= 3 ; +while ($1) +{ + execute stmt_update using @arg00 ; + execute stmt_delete ; + execute stmt_select1 ; + execute stmt_select2 ; + set @arg00= @arg00 + 1 ; + dec $1 ; +} + |