ps_modify.inc
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:10k
- ###################### 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
- --error 1295
- 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;
- execute stmt1 using @arg00, @arg01;
- 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' ;
- # currently (May 2004, Version 4.1) it is impossible
- -- error 1064
- prepare stmt1 from 'update t1 set b=''bla''
- where a=2
- limit ?';
- --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 1062
- 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 1062
- 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 ;
- }