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) engine = memory;
13
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t1;
14
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
15
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
16
create table t3(f1 char(20),f2 char(20),f3 integer) engine = memory;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
18
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
21
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
22
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
24
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = memory;
25
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
27
Warning 1265 Data truncated for column 'f3' at row 1
28
Warning 1265 Data truncated for column 'f3' at row 2
29
Warning 1265 Data truncated for column 'f3' at row 3
30
Warning 1265 Data truncated for column 'f3' at row 4
31
Warning 1265 Data truncated for column 'f3' at row 5
32
Warning 1265 Data truncated for column 'f3' at row 6
33
Warning 1265 Data truncated for column 'f3' at row 7
34
Warning 1265 Data truncated for column 'f3' at row 8
35
Warning 1265 Data truncated for column 'f3' at row 9
36
Warning 1265 Data truncated for column 'f3' at row 10
37
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = memory;
38
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
40
Warning 1265 Data truncated for column 'f3' at row 1
41
Warning 1265 Data truncated for column 'f3' at row 2
42
Warning 1265 Data truncated for column 'f3' at row 3
43
Warning 1265 Data truncated for column 'f3' at row 4
44
Warning 1265 Data truncated for column 'f3' at row 5
45
Warning 1265 Data truncated for column 'f3' at row 6
46
Warning 1265 Data truncated for column 'f3' at row 7
47
Warning 1265 Data truncated for column 'f3' at row 8
48
Warning 1265 Data truncated for column 'f3' at row 9
49
Warning 1265 Data truncated for column 'f3' at row 10
50
create table t9(f1 int, f2 char(25), f3 int) engine = memory;
51
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
52
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t10;
54
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
55
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
57
Section 3.1.3 - Syntax checks for the stored procedure-specific flow
58
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
59
--------------------------------------------------------------------------------
64
Ensure that the IF statement acts correctly for all variants, including cases
65
where statements are nested.
66
--------------------------------------------------------------------------------
67
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
68
DROP PROCEDURE IF EXISTS sp9;
69
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
70
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
72
if action = 'action' then
73
if subaction = 'subaction' then
74
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
76
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
79
if subaction = 'subaction'
81
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
82
elseif subaction = 'subaction1'
85
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
88
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
92
CALL sp9( 'action', 'subaction' );
93
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
96
CALL sp9( 'temp', 'subaction' );
97
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
100
CALL sp9( 'temp', 'subaction1' );
101
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
104
CALL sp9( 'action', 'temp' );
105
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
108
CALL sp9( 'temp', 'temp' );
109
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
113
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
118
Ensure that the CASE statement acts correctly for all variants, including cases
119
where statements are nested.
120
--------------------------------------------------------------------------------
121
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
122
DROP PROCEDURE IF EXISTS sp10;
123
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
124
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
129
when subaction = 'subaction_1' then
130
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
131
when subaction = 'subaction_2' then
132
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
134
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
138
when subaction = 'subaction_1' then
139
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
140
when subaction = 'subaction_2' then
141
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
143
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
147
CALL sp10( 'action', 'subaction_1' );
148
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
151
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
152
CALL sp10( 'action', 'subaction_2' );
153
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
156
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
157
CALL sp10( 'temp', 'subaction_1' );
158
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
161
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
162
CALL sp10( 'temp', 'subaction_2' );
163
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
166
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
167
CALL sp10( 'action', 'temp' );
168
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
171
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
172
CALL sp10( 'temp', 'temp' );
173
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
177
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
179
Testcase 3.1.3.9 + 3.1.3.15:
180
----------------------------
182
09. Ensure that the LOOP statement acts correctly for all variants, including
183
. cases where statements are nested.
184
15. Ensure that the LEAVE statement acts correctly for all variants, including
185
. cases where statements are nested.
186
--------------------------------------------------------------------------------
187
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
188
DROP PROCEDURE IF EXISTS sp11;
189
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
190
CREATE PROCEDURE sp11( )
192
declare count1 integer default 1;
193
declare count2 integer default 1;
195
if count2 > 3 then leave label1;
199
if count1 > 4 then leave label2;
201
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
202
set count1 = count1 + 1;
205
set count2 = count2 + 1;
210
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
225
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
230
Ensure that the ITERATE statement acts correctly for all variants, including
231
cases where statements are nested.
232
(tests for this testcase are also included in other testcases)
233
--------------------------------------------------------------------------------
234
DROP PROCEDURE IF EXISTS sp31316;
235
CREATE PROCEDURE sp31316( )
237
declare count1 integer default 1;
238
declare count2 integer default 1;
240
if count2 > 3 then leave label1;
244
if count1 > 4 then leave label2;
246
insert into temp values( count1, count2);
247
set count1 = count1 + 1;
250
set count2 = count2 + 1;
254
ERROR 42000: ITERATE with no matching label: label3
259
Ensure that the REPEAT statement acts correctly for all variants, including
260
cases where statements are nested.
261
--------------------------------------------------------------------------------
262
DROP PROCEDURE IF EXISTS sp17;
263
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
264
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
265
CREATE PROCEDURE sp17( )
267
declare count1 integer default 1;
268
declare count2 integer default 1;
270
set count1 = count1 + 1;
273
set count2 = count2 + 1;
274
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
281
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
293
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
298
Ensure that the WHILE statement acts correctly for all variants, including cases
299
where statements are nested.
300
--------------------------------------------------------------------------------
301
drop table IF EXISTS res_t21;
302
DROP PROCEDURE IF EXISTS sp21;
303
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
304
insert into res_t21 values('ashwin', 'mokadam', 25);
305
CREATE PROCEDURE sp21( )
307
declare count1 integer default 0;
308
declare count2 integer default 0;
311
declare ithisissamevariablename int default 100;
312
SELECT ithisissamevariablename;
314
declare ithisissamevariablename int default 200;
315
SELECT ithisissamevariablename;
318
label1: while count2 < 3 do
320
declare count1 integer default 7;
321
set count2 = count2 + 1;
322
insert into res_t21 values( 'xyz' , 'pqr', count2);
323
label2: while count1 < 10 do
324
set count1 = count1 + 1;
325
insert into res_t21 values( 'xyz' , 'pqr', count1);
329
set count1 = count1 + 1;
334
ithisissamevariablename
336
ithisissamevariablename
338
ithisissamevariablename
340
ithisissamevariablename
342
ithisissamevariablename
344
ithisissamevariablename
346
SELECT * from res_t21;
347
name surname age_averylongfieldname_averylongname_1234569
391
Ensure that multiple cases of all possible combinations of the control flow
392
statements, nested within multiple compound statements within a stored
393
procedure, always act correctly and return the expected result.
394
--------------------------------------------------------------------------------
395
DROP TABLE IF EXISTS res_tbl;
396
DROP PROCEDURE IF EXISTS sp31330;
397
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
398
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
399
CREATE PROCEDURE sp31330 (path int)
401
declare count int default 1;
405
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
408
set var3 = '2000-11-09';
409
set var1 = 'flowing through case 1';
413
set var2 = 'exiting out of case 1 - invalid SET';
416
set var2 = 'exiting out of case 1';
418
insert into res_tbl values (1, var1, var2, var3, (count-2));
420
elseif count = 5 then
421
set count= count + 2;
425
set count= count + 1;
430
set var3 = '1989-11-09';
431
set var1 = 'flowing through case 2';
436
while @count2 <= 5 do
437
set @count2 = @count2 + 1;
440
set @count3=@count3 + @count2;
443
set var2 = 'exiting out of case 2';
446
insert into res_tbl values (2, var1, var2, var3, var4);
448
set @error_opt='undefined path specified';
454
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
456
SELECT * from res_tbl;
458
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
472
SELECT * from res_tbl;
474
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
475
2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
478
undefined path specified
479
DROP PROCEDURE sp31330;
482
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
483
--------------------------------------------------------------------------------
484
DROP DATABASE IF EXISTS db_storedproc;
485
DROP DATABASE IF EXISTS db_storedproc_1;
487
. +++ END OF SCRIPT +++
488
--------------------------------------------------------------------------------