1
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
3
--source suite/funcs_1/storedproc/load_sp_tb.inc
4
--------------------------------------------------------------------------------
6
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
7
--------------------------------------------------------------------------------
8
DROP DATABASE IF EXISTS db_storedproc;
9
DROP DATABASE IF EXISTS db_storedproc_1;
10
CREATE DATABASE db_storedproc;
11
CREATE DATABASE db_storedproc_1;
13
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
14
engine = <engine_to_be_tested>;
15
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
16
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
17
engine = <engine_to_be_tested>;
18
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
19
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
20
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
21
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
22
engine = <engine_to_be_tested>;
23
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
25
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
26
engine = <engine_to_be_tested>;
27
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
29
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
30
engine = <engine_to_be_tested>;
31
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
33
Warning 1265 Data truncated for column 'f3' at row 1
34
Warning 1265 Data truncated for column 'f3' at row 2
35
Warning 1265 Data truncated for column 'f3' at row 3
36
Warning 1265 Data truncated for column 'f3' at row 4
37
Warning 1265 Data truncated for column 'f3' at row 5
38
Warning 1265 Data truncated for column 'f3' at row 6
39
Warning 1265 Data truncated for column 'f3' at row 7
40
Warning 1265 Data truncated for column 'f3' at row 8
41
Warning 1265 Data truncated for column 'f3' at row 9
42
Warning 1265 Data truncated for column 'f3' at row 10
43
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
44
engine = <engine_to_be_tested>;
45
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
47
Warning 1265 Data truncated for column 'f3' at row 1
48
Warning 1265 Data truncated for column 'f3' at row 2
49
Warning 1265 Data truncated for column 'f3' at row 3
50
Warning 1265 Data truncated for column 'f3' at row 4
51
Warning 1265 Data truncated for column 'f3' at row 5
52
Warning 1265 Data truncated for column 'f3' at row 6
53
Warning 1265 Data truncated for column 'f3' at row 7
54
Warning 1265 Data truncated for column 'f3' at row 8
55
Warning 1265 Data truncated for column 'f3' at row 9
56
Warning 1265 Data truncated for column 'f3' at row 10
57
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
58
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
59
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
60
engine = <engine_to_be_tested>;
61
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
62
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
63
engine = <engine_to_be_tested>;
64
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
66
Section 3.1.3 - Syntax checks for the stored procedure-specific flow
67
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
68
--------------------------------------------------------------------------------
73
Ensure that the IF statement acts correctly for all variants, including cases
74
where statements are nested.
75
--------------------------------------------------------------------------------
76
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
77
DROP PROCEDURE IF EXISTS sp9;
78
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
79
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
81
if action = 'action' then
82
if subaction = 'subaction' then
83
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
85
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
88
if subaction = 'subaction'
90
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
91
elseif subaction = 'subaction1'
94
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
97
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
101
CALL sp9( 'action', 'subaction' );
102
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
105
CALL sp9( 'temp', 'subaction' );
106
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
109
CALL sp9( 'temp', 'subaction1' );
110
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
113
CALL sp9( 'action', 'temp' );
114
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
117
CALL sp9( 'temp', 'temp' );
118
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
122
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
127
Ensure that the CASE statement acts correctly for all variants, including cases
128
where statements are nested.
129
--------------------------------------------------------------------------------
130
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
131
DROP PROCEDURE IF EXISTS sp10;
132
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
133
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
138
when subaction = 'subaction_1' then
139
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
140
when subaction = 'subaction_2' then
141
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
143
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
147
when subaction = 'subaction_1' then
148
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
149
when subaction = 'subaction_2' then
150
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
152
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
156
CALL sp10( 'action', 'subaction_1' );
157
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
160
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
161
CALL sp10( 'action', 'subaction_2' );
162
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
165
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
166
CALL sp10( 'temp', 'subaction_1' );
167
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
170
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
171
CALL sp10( 'temp', 'subaction_2' );
172
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
175
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
176
CALL sp10( 'action', 'temp' );
177
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
180
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
181
CALL sp10( 'temp', 'temp' );
182
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
186
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
188
Testcase 3.1.3.9 + 3.1.3.15:
189
----------------------------
191
09. Ensure that the LOOP statement acts correctly for all variants, including
192
. cases where statements are nested.
193
15. Ensure that the LEAVE statement acts correctly for all variants, including
194
. cases where statements are nested.
195
--------------------------------------------------------------------------------
196
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
197
DROP PROCEDURE IF EXISTS sp11;
198
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
199
CREATE PROCEDURE sp11( )
201
declare count1 integer default 1;
202
declare count2 integer default 1;
204
if count2 > 3 then leave label1;
208
if count1 > 4 then leave label2;
210
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
211
set count1 = count1 + 1;
214
set count2 = count2 + 1;
219
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
234
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
239
Ensure that the ITERATE statement acts correctly for all variants, including
240
cases where statements are nested.
241
(tests for this testcase are also included in other testcases)
242
--------------------------------------------------------------------------------
243
DROP PROCEDURE IF EXISTS sp31316;
244
CREATE PROCEDURE sp31316( )
246
declare count1 integer default 1;
247
declare count2 integer default 1;
249
if count2 > 3 then leave label1;
253
if count1 > 4 then leave label2;
255
insert into temp values( count1, count2);
256
set count1 = count1 + 1;
259
set count2 = count2 + 1;
263
ERROR 42000: ITERATE with no matching label: label3
268
Ensure that the REPEAT statement acts correctly for all variants, including
269
cases where statements are nested.
270
--------------------------------------------------------------------------------
271
DROP PROCEDURE IF EXISTS sp17;
272
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
273
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
274
CREATE PROCEDURE sp17( )
276
declare count1 integer default 1;
277
declare count2 integer default 1;
279
set count1 = count1 + 1;
282
set count2 = count2 + 1;
283
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
290
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
302
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
307
Ensure that the WHILE statement acts correctly for all variants, including cases
308
where statements are nested.
309
--------------------------------------------------------------------------------
310
drop table IF EXISTS res_t21;
311
DROP PROCEDURE IF EXISTS sp21;
312
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
313
insert into res_t21 values('ashwin', 'mokadam', 25);
314
CREATE PROCEDURE sp21( )
316
declare count1 integer default 0;
317
declare count2 integer default 0;
320
declare ithisissamevariablename int default 100;
321
SELECT ithisissamevariablename;
323
declare ithisissamevariablename int default 200;
324
SELECT ithisissamevariablename;
327
label1: while count2 < 3 do
329
declare count1 integer default 7;
330
set count2 = count2 + 1;
331
insert into res_t21 values( 'xyz' , 'pqr', count2);
332
label2: while count1 < 10 do
333
set count1 = count1 + 1;
334
insert into res_t21 values( 'xyz' , 'pqr', count1);
338
set count1 = count1 + 1;
343
ithisissamevariablename
345
ithisissamevariablename
347
ithisissamevariablename
349
ithisissamevariablename
351
ithisissamevariablename
353
ithisissamevariablename
355
SELECT * from res_t21;
356
name surname age_averylongfieldname_averylongname_1234569
400
Ensure that multiple cases of all possible combinations of the control flow
401
statements, nested within multiple compound statements within a stored
402
procedure, always act correctly and return the expected result.
403
--------------------------------------------------------------------------------
404
DROP TABLE IF EXISTS res_tbl;
405
DROP PROCEDURE IF EXISTS sp31330;
406
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
407
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
408
CREATE PROCEDURE sp31330 (path int)
410
declare count int default 1;
414
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
417
set var3 = '2000-11-09';
418
set var1 = 'flowing through case 1';
422
set var2 = 'exiting out of case 1 - invalid SET';
425
set var2 = 'exiting out of case 1';
427
insert into res_tbl values (1, var1, var2, var3, (count-2));
429
elseif count = 5 then
430
set count= count + 2;
434
set count= count + 1;
439
set var3 = '1989-11-09';
440
set var1 = 'flowing through case 2';
445
while @count2 <= 5 do
446
set @count2 = @count2 + 1;
449
set @count3=@count3 + @count2;
452
set var2 = 'exiting out of case 2';
455
insert into res_tbl values (2, var1, var2, var3, var4);
457
set @error_opt='undefined path specified';
463
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
465
SELECT * from res_tbl;
467
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
481
SELECT * from res_tbl;
483
1 flowing through case 1 exiting out of case 1 2000-11-09 one,three
484
2 flowing through case 2 exiting out of case 2 1989-11-09 one,two
487
undefined path specified
488
DROP PROCEDURE sp31330;
491
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
492
--------------------------------------------------------------------------------
493
DROP DATABASE IF EXISTS db_storedproc;
494
DROP DATABASE IF EXISTS db_storedproc_1;
496
. +++ END OF SCRIPT +++
497
--------------------------------------------------------------------------------