2
--source suite/funcs_1/storedproc/load_sp_tb.inc
3
--------------------------------------------------------------------------------
4
SET @@global.max_heap_table_size=4294967295;
5
SET @@session.max_heap_table_size=4294967295;
7
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
8
--------------------------------------------------------------------------------
9
DROP DATABASE IF EXISTS db_storedproc;
10
DROP DATABASE IF EXISTS db_storedproc_1;
11
CREATE DATABASE db_storedproc;
12
CREATE DATABASE db_storedproc_1;
14
create table t1(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 t1;
16
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
17
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t2;
18
create table t3(f1 char(20),f2 char(20),f3 integer) engine = memory;
19
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' into table t3;
20
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
21
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t4;
23
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
24
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t6;
26
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = memory;
27
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t7;
29
Warning 1265 Data truncated for column 'f3' at row 1
30
Warning 1265 Data truncated for column 'f3' at row 2
31
Warning 1265 Data truncated for column 'f3' at row 3
32
Warning 1265 Data truncated for column 'f3' at row 4
33
Warning 1265 Data truncated for column 'f3' at row 5
34
Warning 1265 Data truncated for column 'f3' at row 6
35
Warning 1265 Data truncated for column 'f3' at row 7
36
Warning 1265 Data truncated for column 'f3' at row 8
37
Warning 1265 Data truncated for column 'f3' at row 9
38
Warning 1265 Data truncated for column 'f3' at row 10
39
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = memory;
40
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' into table t8;
42
Warning 1265 Data truncated for column 'f3' at row 1
43
Warning 1265 Data truncated for column 'f3' at row 2
44
Warning 1265 Data truncated for column 'f3' at row 3
45
Warning 1265 Data truncated for column 'f3' at row 4
46
Warning 1265 Data truncated for column 'f3' at row 5
47
Warning 1265 Data truncated for column 'f3' at row 6
48
Warning 1265 Data truncated for column 'f3' at row 7
49
Warning 1265 Data truncated for column 'f3' at row 8
50
Warning 1265 Data truncated for column 'f3' at row 9
51
Warning 1265 Data truncated for column 'f3' at row 10
52
create table t9(f1 int, f2 char(25), f3 int) engine = memory;
53
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table t9;
54
create table t10(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 t10;
56
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) engine = memory;
57
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' into table t11;
59
Section 3.1.3 - Syntax checks for the stored procedure-specific flow
60
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
61
--------------------------------------------------------------------------------
66
Ensure that the IF statement acts correctly for all variants, including cases
67
where statements are nested.
68
--------------------------------------------------------------------------------
69
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
70
DROP PROCEDURE IF EXISTS sp9;
71
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
72
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
74
if action = 'action' then
75
if subaction = 'subaction' then
76
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
78
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
81
if subaction = 'subaction'
83
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
84
elseif subaction = 'subaction1'
87
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
90
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
94
CALL sp9( 'action', 'subaction' );
95
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
98
CALL sp9( 'temp', 'subaction' );
99
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
102
CALL sp9( 'temp', 'subaction1' );
103
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
106
CALL sp9( 'action', 'temp' );
107
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
110
CALL sp9( 'temp', 'temp' );
111
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
115
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
120
Ensure that the CASE statement acts correctly for all variants, including cases
121
where statements are nested.
122
--------------------------------------------------------------------------------
123
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
124
DROP PROCEDURE IF EXISTS sp10;
125
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
126
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
131
when subaction = 'subaction_1' then
132
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
133
when subaction = 'subaction_2' then
134
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
136
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
140
when subaction = 'subaction_1' then
141
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
142
when subaction = 'subaction_2' then
143
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
145
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
149
CALL sp10( 'action', 'subaction_1' );
150
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
153
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
154
CALL sp10( 'action', 'subaction_2' );
155
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
158
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
159
CALL sp10( 'temp', 'subaction_1' );
160
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
163
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
164
CALL sp10( 'temp', 'subaction_2' );
165
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
168
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
169
CALL sp10( 'action', 'temp' );
170
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
173
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
174
CALL sp10( 'temp', 'temp' );
175
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
179
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
181
Testcase 3.1.3.9 + 3.1.3.15:
182
----------------------------
184
09. Ensure that the LOOP statement acts correctly for all variants, including
185
. cases where statements are nested.
186
15. Ensure that the LEAVE statement acts correctly for all variants, including
187
. cases where statements are nested.
188
--------------------------------------------------------------------------------
189
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
190
DROP PROCEDURE IF EXISTS sp11;
191
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
192
CREATE PROCEDURE sp11( )
194
declare count1 integer default 1;
195
declare count2 integer default 1;
197
if count2 > 3 then leave label1;
201
if count1 > 4 then leave label2;
203
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
204
set count1 = count1 + 1;
207
set count2 = count2 + 1;
212
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
227
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
232
Ensure that the ITERATE statement acts correctly for all variants, including
233
cases where statements are nested.
234
(tests for this testcase are also included in other testcases)
235
--------------------------------------------------------------------------------
236
DROP PROCEDURE IF EXISTS sp31316;
237
CREATE PROCEDURE sp31316( )
239
declare count1 integer default 1;
240
declare count2 integer default 1;
242
if count2 > 3 then leave label1;
246
if count1 > 4 then leave label2;
248
insert into temp values( count1, count2);
249
set count1 = count1 + 1;
252
set count2 = count2 + 1;
256
ERROR 42000: ITERATE with no matching label: label3
261
Ensure that the REPEAT statement acts correctly for all variants, including
262
cases where statements are nested.
263
--------------------------------------------------------------------------------
264
DROP PROCEDURE IF EXISTS sp17;
265
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
266
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
267
CREATE PROCEDURE sp17( )
269
declare count1 integer default 1;
270
declare count2 integer default 1;
272
set count1 = count1 + 1;
275
set count2 = count2 + 1;
276
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
283
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
295
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
300
Ensure that the WHILE statement acts correctly for all variants, including cases
301
where statements are nested.
302
--------------------------------------------------------------------------------
303
drop table IF EXISTS res_t21;
304
DROP PROCEDURE IF EXISTS sp21;
305
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
306
insert into res_t21 values('ashwin', 'mokadam', 25);
307
CREATE PROCEDURE sp21( )
309
declare count1 integer default 0;
310
declare count2 integer default 0;
313
declare ithisissamevariablename int default 100;
314
SELECT ithisissamevariablename;
316
declare ithisissamevariablename int default 200;
317
SELECT ithisissamevariablename;
320
label1: while count2 < 3 do
322
declare count1 integer default 7;
323
set count2 = count2 + 1;
324
insert into res_t21 values( 'xyz' , 'pqr', count2);
325
label2: while count1 < 10 do
326
set count1 = count1 + 1;
327
insert into res_t21 values( 'xyz' , 'pqr', count1);
331
set count1 = count1 + 1;
336
ithisissamevariablename
338
ithisissamevariablename
340
ithisissamevariablename
342
ithisissamevariablename
344
ithisissamevariablename
346
ithisissamevariablename
348
SELECT * from res_t21;
349
name surname age_averylongfieldname_averylongname_1234569
393
Ensure that multiple cases of all possible combinations of the control flow
394
statements, nested within multiple compound statements within a stored
395
procedure, always act correctly and return the expected result.
396
--------------------------------------------------------------------------------
397
DROP TABLE IF EXISTS res_tbl;
398
DROP PROCEDURE IF EXISTS sp31330;
399
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
400
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
401
CREATE PROCEDURE sp31330 (path int)
403
declare count int default 1;
407
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
410
set var3 = '2000-11-09';
411
set var1 = 'flowing through case 1';
415
set var2 = 'exiting out of case 1 - invalid SET';
418
set var2 = 'exiting out of case 1';
420
insert into res_tbl values (1, var1, var2, var3, (count-2));
422
elseif count = 5 then
423
set count= count + 2;
427
set count= count + 1;
432
set var3 = '1989-11-09';
433
set var1 = 'flowing through case 2';
438
while @count2 <= 5 do
439
set @count2 = @count2 + 1;
442
set @count3=@count3 + @count2;
445
set var2 = 'exiting out of case 2';
448
insert into res_tbl values (2, var1, var2, var3, var4);
450
set @error_opt='undefined path specified';
456
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
458
SELECT * from res_tbl;
460
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
474
SELECT * from res_tbl;
476
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
477
2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
480
undefined path specified
481
DROP PROCEDURE sp31330;
484
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
485
--------------------------------------------------------------------------------
486
DROP DATABASE IF EXISTS db_storedproc;
487
DROP DATABASE IF EXISTS db_storedproc_1;
489
. +++ END OF SCRIPT +++
490
--------------------------------------------------------------------------------