2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
5
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
6
--------------------------------------------------------------------------------
7
DROP DATABASE IF EXISTS db_storedproc;
8
DROP DATABASE IF EXISTS db_storedproc_1;
9
CREATE DATABASE db_storedproc;
10
CREATE DATABASE db_storedproc_1;
12
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
13
engine = <engine_to_be_tested>;
14
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
15
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
16
engine = <engine_to_be_tested>;
17
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
18
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
19
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
20
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
21
engine = <engine_to_be_tested>;
22
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
24
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
25
engine = <engine_to_be_tested>;
26
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
28
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
29
engine = <engine_to_be_tested>;
30
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
32
Warning 1265 Data truncated for column 'f3' at row 1
33
Warning 1265 Data truncated for column 'f3' at row 2
34
Warning 1265 Data truncated for column 'f3' at row 3
35
Warning 1265 Data truncated for column 'f3' at row 4
36
Warning 1265 Data truncated for column 'f3' at row 5
37
Warning 1265 Data truncated for column 'f3' at row 6
38
Warning 1265 Data truncated for column 'f3' at row 7
39
Warning 1265 Data truncated for column 'f3' at row 8
40
Warning 1265 Data truncated for column 'f3' at row 9
41
Warning 1265 Data truncated for column 'f3' at row 10
42
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
43
engine = <engine_to_be_tested>;
44
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
46
Warning 1265 Data truncated for column 'f3' at row 1
47
Warning 1265 Data truncated for column 'f3' at row 2
48
Warning 1265 Data truncated for column 'f3' at row 3
49
Warning 1265 Data truncated for column 'f3' at row 4
50
Warning 1265 Data truncated for column 'f3' at row 5
51
Warning 1265 Data truncated for column 'f3' at row 6
52
Warning 1265 Data truncated for column 'f3' at row 7
53
Warning 1265 Data truncated for column 'f3' at row 8
54
Warning 1265 Data truncated for column 'f3' at row 9
55
Warning 1265 Data truncated for column 'f3' at row 10
56
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
57
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
58
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
59
engine = <engine_to_be_tested>;
60
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
61
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
62
engine = <engine_to_be_tested>;
63
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
65
Section 3.1.3 - Syntax checks for the stored procedure-specific flow
66
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
67
--------------------------------------------------------------------------------
72
Ensure that the IF statement acts correctly for all variants, including cases
73
where statements are nested.
74
--------------------------------------------------------------------------------
75
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
76
DROP PROCEDURE IF EXISTS sp9;
77
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
78
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
80
if action = 'action' then
81
if subaction = 'subaction' then
82
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
84
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
87
if subaction = 'subaction'
89
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
90
elseif subaction = 'subaction1'
93
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
96
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
100
CALL sp9( 'action', 'subaction' );
101
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
104
CALL sp9( 'temp', 'subaction' );
105
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
108
CALL sp9( 'temp', 'subaction1' );
109
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
112
CALL sp9( 'action', 'temp' );
113
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
116
CALL sp9( 'temp', 'temp' );
117
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
121
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
126
Ensure that the CASE statement acts correctly for all variants, including cases
127
where statements are nested.
128
--------------------------------------------------------------------------------
129
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
130
DROP PROCEDURE IF EXISTS sp10;
131
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
132
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
137
when subaction = 'subaction_1' then
138
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
139
when subaction = 'subaction_2' then
140
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
142
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
146
when subaction = 'subaction_1' then
147
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
148
when subaction = 'subaction_2' then
149
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
151
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
155
CALL sp10( 'action', 'subaction_1' );
156
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
159
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
160
CALL sp10( 'action', 'subaction_2' );
161
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
164
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
165
CALL sp10( 'temp', 'subaction_1' );
166
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
169
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
170
CALL sp10( 'temp', 'subaction_2' );
171
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
174
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
175
CALL sp10( 'action', 'temp' );
176
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
179
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
180
CALL sp10( 'temp', 'temp' );
181
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
185
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
187
Testcase 3.1.3.9 + 3.1.3.15:
188
----------------------------
190
09. Ensure that the LOOP statement acts correctly for all variants, including
191
. cases where statements are nested.
192
15. Ensure that the LEAVE statement acts correctly for all variants, including
193
. cases where statements are nested.
194
--------------------------------------------------------------------------------
195
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
196
DROP PROCEDURE IF EXISTS sp11;
197
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
198
CREATE PROCEDURE sp11( )
200
declare count1 integer default 1;
201
declare count2 integer default 1;
203
if count2 > 3 then leave label1;
207
if count1 > 4 then leave label2;
209
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
210
set count1 = count1 + 1;
213
set count2 = count2 + 1;
218
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
233
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
238
Ensure that the ITERATE statement acts correctly for all variants, including
239
cases where statements are nested.
240
(tests for this testcase are also included in other testcases)
241
--------------------------------------------------------------------------------
242
DROP PROCEDURE IF EXISTS sp31316;
243
CREATE PROCEDURE sp31316( )
245
declare count1 integer default 1;
246
declare count2 integer default 1;
248
if count2 > 3 then leave label1;
252
if count1 > 4 then leave label2;
254
insert into temp values( count1, count2);
255
set count1 = count1 + 1;
258
set count2 = count2 + 1;
262
ERROR 42000: ITERATE with no matching label: label3
267
Ensure that the REPEAT statement acts correctly for all variants, including
268
cases where statements are nested.
269
--------------------------------------------------------------------------------
270
DROP PROCEDURE IF EXISTS sp17;
271
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
272
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
273
CREATE PROCEDURE sp17( )
275
declare count1 integer default 1;
276
declare count2 integer default 1;
278
set count1 = count1 + 1;
281
set count2 = count2 + 1;
282
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
289
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
301
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
306
Ensure that the WHILE statement acts correctly for all variants, including cases
307
where statements are nested.
308
--------------------------------------------------------------------------------
309
drop table IF EXISTS res_t21;
310
DROP PROCEDURE IF EXISTS sp21;
311
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
312
insert into res_t21 values('ashwin', 'mokadam', 25);
313
CREATE PROCEDURE sp21( )
315
declare count1 integer default 0;
316
declare count2 integer default 0;
319
declare ithisissamevariablename int default 100;
320
SELECT ithisissamevariablename;
322
declare ithisissamevariablename int default 200;
323
SELECT ithisissamevariablename;
326
label1: while count2 < 3 do
328
declare count1 integer default 7;
329
set count2 = count2 + 1;
330
insert into res_t21 values( 'xyz' , 'pqr', count2);
331
label2: while count1 < 10 do
332
set count1 = count1 + 1;
333
insert into res_t21 values( 'xyz' , 'pqr', count1);
337
set count1 = count1 + 1;
342
ithisissamevariablename
344
ithisissamevariablename
346
ithisissamevariablename
348
ithisissamevariablename
350
ithisissamevariablename
352
ithisissamevariablename
354
SELECT * from res_t21;
355
name surname age_averylongfieldname_averylongname_1234569
399
Ensure that multiple cases of all possible combinations of the control flow
400
statements, nested within multiple compound statements within a stored
401
procedure, always act correctly and return the expected result.
402
--------------------------------------------------------------------------------
403
DROP TABLE IF EXISTS res_tbl;
404
DROP PROCEDURE IF EXISTS sp31330;
405
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
406
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
407
CREATE PROCEDURE sp31330 (path int)
409
declare count int default 1;
413
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
416
set var3 = '2000-11-09';
417
set var1 = 'flowing through case 1';
421
set var2 = 'exiting out of case 1 - invalid SET';
424
set var2 = 'exiting out of case 1';
426
insert into res_tbl values (1, var1, var2, var3, (count-2));
428
elseif count = 5 then
429
set count= count + 2;
433
set count= count + 1;
438
set var3 = '1989-11-09';
439
set var1 = 'flowing through case 2';
444
while @count2 <= 5 do
445
set @count2 = @count2 + 1;
448
set @count3=@count3 + @count2;
451
set var2 = 'exiting out of case 2';
454
insert into res_tbl values (2, var1, var2, var3, var4);
456
set @error_opt='undefined path specified';
462
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
464
SELECT * from res_tbl;
466
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
480
SELECT * from res_tbl;
482
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
483
2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
486
undefined path specified
487
DROP PROCEDURE sp31330;
490
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
491
--------------------------------------------------------------------------------
492
DROP DATABASE IF EXISTS db_storedproc;
493
DROP DATABASE IF EXISTS db_storedproc_1;
495
. +++ END OF SCRIPT +++
496
--------------------------------------------------------------------------------