1
###################### ps_modify1.inc ########################
3
# Tests for prepared statements: big INSERT .. SELECTs #
5
##############################################################
8
# NOTE: THESE TESTS CANNOT BE APPLIED TO TABLES OF TYPE MERGE.
9
# Test which can be applied to MERGE tables should be stored in
10
# include/ps_modify.inc .
14
# NOTE: PLEASE SEE ps_1general.test (bottom)
15
# BEFORE ADDING NEW TEST CASES HERE !!!
18
# Please be aware, that this file will be sourced by several test case files
19
# stored within the subdirectory 't'. So every change here will affect
22
# Please do not modify the structure (DROP/ALTER..) of the tables
25
# But you are encouraged to use these two tables within your statements
26
# (DELETE/UPDATE/...) whenever possible.
27
# t1 - very simple table
28
# t9 - table with nearly all available column types
30
# The structure and the content of these tables can be found in
31
# include/ps_create.inc CREATE TABLE ...
32
# include/ps_renew.inc DELETE all rows and INSERT some rows
34
# Both tables are managed by the same storage engine.
35
# The type of the storage engine is stored in the variable '$type' .
39
#------------------- Please insert your test cases here -------------------#
42
#-------- Please be very carefull when editing behind this line ----------#
44
--source include/ps_renew.inc
46
# add a NULL row to t1: this row is used only in this test
47
insert into t1 values(0,NULL) ;
49
## big insert select statements
50
set @duplicate='duplicate ' ;
53
select a,b from t1 where a < 5 order by a ;
55
insert into t1 select a + @1000, concat(@duplicate,b) from t1
58
select a,b from t1 where a >= 1000 order by a ;
59
delete from t1 where a >= 1000 ;
60
prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1
63
execute stmt1 using @1000, @duplicate, @5;
65
select a,b from t1 where a >= 1000 order by a ;
66
delete from t1 where a >= 1000 ;
74
drop table if exists t2;
76
create table t2 like t1 ;
79
select @duplicate, sum(first.a) from t1 first, t1 second
80
where first.a <> @5 and second.b = first.b
83
having sum(second.a) > @2
85
select b, a + @100 from t1
86
where (a,b) in ( select sqrt(a+@1)+CAST(@float AS signed),b
89
select a,b from t2 order by a ;
91
prepare stmt1 from ' insert into t2 (b,a)
92
select ?, sum(first.a)
93
from t1 first, t1 second
94
where first.a <> ? and second.b = first.b and second.b <> ?
96
having sum(second.a) > ?
98
select b, a + ? from t1
99
where (a,b) in ( select sqrt(a+?)+CAST(? AS signed),b
102
execute stmt1 using @duplicate, @5, @five, @2, @100, @1, @float ;
104
select a,b from t2 order by a ;