mysql-test/include/ps_modify.inc

###################### 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 ;
}