summaryrefslogtreecommitdiffstats
path: root/mysql-test/include/ps_modify.inc
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/include/ps_modify.inc
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/include/ps_modify.inc')
-rw-r--r--mysql-test/include/ps_modify.inc358
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 ;
+}
+