Gecko [ 122.154.134.11 ]

Name	Size	Permission
charset_collation.result	1.32 KB	-rw-r--r--
innodb_bitdata.result	117 B	-rw-r--r--
innodb_cursors.result	116 B	-rw-r--r--
innodb_func_view.result	301.02 KB	-rw-r--r--
innodb_storedproc_02.result	36.28 KB	-rw-r--r--
innodb_storedproc_03.result	15.3 KB	-rw-r--r--
innodb_storedproc_06.result	13.28 KB	-rw-r--r--
innodb_storedproc_07.result	7.77 KB	-rw-r--r--
innodb_storedproc_08.result	20.03 KB	-rw-r--r--
innodb_storedproc_10.result	13.21 KB	-rw-r--r--
innodb_trig_0102.result	15.05 KB	-rw-r--r--
innodb_trig_03.result	25.65 KB	-rw-r--r--
innodb_trig_03e.result	51.01 KB	-rw-r--r--
innodb_trig_0407.result	15.4 KB	-rw-r--r--
innodb_trig_08.result	18.39 KB	-rw-r--r--
innodb_trig_09.result	9.97 KB	-rw-r--r--
innodb_trig_1011ext.result	11.75 KB	-rw-r--r--
innodb_trig_frkey.result	2.23 KB	-rw-r--r--
innodb_views.result	580.67 KB	-rw-r--r--
is_basics_mixed.result	30.01 KB	-rw-r--r--
is_character_sets.result	3.96 KB	-rw-r--r--
is_cml_innodb.result	4.65 KB	-rw-r--r--
is_cml_memory.result	3.69 KB	-rw-r--r--
is_cml_myisam.result	4.7 KB	-rw-r--r--
is_cml_ndb.result	4.67 KB	-rw-r--r--
is_coll_char_set_appl.result	4.34 KB	-rw-r--r--
is_collations.result	4.43 KB	-rw-r--r--
is_column_privileges.result	19.22 KB	-rw-r--r--
is_column_privileges_is_mysql_...	1.44 KB	-rw-r--r--
is_columns.result	19.88 KB	-rw-r--r--
is_columns_innodb.result	76.58 KB	-rw-r--r--
is_columns_is.result	66.43 KB	-rw-r--r--
is_columns_is_embedded.result	64.69 KB	-rw-r--r--
is_columns_memory.result	73.43 KB	-rw-r--r--
is_columns_myisam.result	81.7 KB	-rw-r--r--
is_columns_myisam_embedded.res...	71.07 KB	-rw-r--r--
is_columns_mysql.result	47.6 KB	-rw-r--r--
is_columns_mysql_embedded.resu...	41.16 KB	-rw-r--r--
is_columns_ndb.result	13.46 KB	-rw-r--r--
is_engines.result	3.88 KB	-rw-r--r--
is_engines_archive.result	161 B	-rw-r--r--
is_engines_blackhole.result	205 B	-rw-r--r--
is_engines_csv.result	149 B	-rw-r--r--
is_engines_federated.result	173 B	-rw-r--r--
is_engines_innodb.result	198 B	-rw-r--r--
is_engines_memory.result	194 B	-rw-r--r--
is_engines_merge.result	182 B	-rw-r--r--
is_engines_myisam.result	195 B	-rw-r--r--
is_engines_ndb.result	178 B	-rw-r--r--
is_events.result	6.37 KB	-rw-r--r--
is_key_column_usage.result	16.8 KB	-rw-r--r--
is_key_column_usage_embedded.r...	16.94 KB	-rw-r--r--
is_routines.result	23.09 KB	-rw-r--r--
is_routines_embedded.result	23.94 KB	-rw-r--r--
is_schema_privileges.result	14.65 KB	-rw-r--r--
is_schema_privileges_is_mysql_...	1.86 KB	-rw-r--r--
is_schemata.result	8.57 KB	-rw-r--r--
is_schemata_embedded.result	8.75 KB	-rw-r--r--
is_schemata_is_mysql_test.resu...	1.59 KB	-rw-r--r--
is_statistics.result	17.47 KB	-rw-r--r--
is_statistics_is.result	1004 B	-rw-r--r--
is_statistics_mysql.result	4.47 KB	-rw-r--r--
is_statistics_mysql_embedded.r...	7.98 KB	-rw-r--r--
is_table_constraints.result	14.35 KB	-rw-r--r--
is_table_constraints_is.result	828 B	-rw-r--r--
is_table_constraints_mysql.res...	1.9 KB	-rw-r--r--
is_table_constraints_mysql_emb...	3.02 KB	-rw-r--r--
is_table_privileges.result	15.08 KB	-rw-r--r--
is_tables.result	17.26 KB	-rw-r--r--
is_tables_embedded.result	17.48 KB	-rw-r--r--
is_tables_innodb.result	3.98 KB	-rw-r--r--
is_tables_is.result	26.86 KB	-rw-r--r--
is_tables_memory.result	4.02 KB	-rw-r--r--
is_tables_myisam.result	4.03 KB	-rw-r--r--
is_tables_myisam_embedded.resu...	4.43 KB	-rw-r--r--
is_tables_mysql.result	12.05 KB	-rw-r--r--
is_tables_mysql_embedded.resul...	22.96 KB	-rw-r--r--
is_tables_ndb.result	4 KB	-rw-r--r--
is_triggers.result	13.86 KB	-rw-r--r--
is_triggers_embedded.result	11.97 KB	-rw-r--r--
is_user_privileges.result	23.96 KB	-rw-r--r--
is_views.result	11.22 KB	-rw-r--r--
is_views_embedded.result	10.98 KB	-rw-r--r--
memory_bitdata.result	168 B	-rw-r--r--
memory_cursors.result	167 B	-rw-r--r--
memory_func_view.result	301.07 KB	-rw-r--r--
memory_storedproc_02.result	36.33 KB	-rw-r--r--
memory_storedproc_03.result	15.35 KB	-rw-r--r--
memory_storedproc_06.result	13.35 KB	-rw-r--r--
memory_storedproc_07.result	7.82 KB	-rw-r--r--
memory_storedproc_08.result	20.59 KB	-rw-r--r--
memory_storedproc_10.result	13.26 KB	-rw-r--r--
memory_trig_0102.result	15.1 KB	-rw-r--r--
memory_trig_03.result	25.7 KB	-rw-r--r--
memory_trig_03e.result	49.44 KB	-rw-r--r--
memory_trig_0407.result	15.45 KB	-rw-r--r--
memory_trig_08.result	18.44 KB	-rw-r--r--
memory_trig_09.result	10.02 KB	-rw-r--r--
memory_trig_1011ext.result	11.81 KB	-rw-r--r--
memory_views.result	580.77 KB	-rw-r--r--
myisam_bitdata.result	168 B	-rw-r--r--
myisam_cursors.result	167 B	-rw-r--r--
myisam_func_view.result	301.07 KB	-rw-r--r--
myisam_storedproc_02.result	36.33 KB	-rw-r--r--
myisam_storedproc_03.result	15.35 KB	-rw-r--r--
myisam_storedproc_06.result	13.35 KB	-rw-r--r--
myisam_storedproc_07.result	7.82 KB	-rw-r--r--
myisam_storedproc_08.result	20.59 KB	-rw-r--r--
myisam_storedproc_10.result	13.26 KB	-rw-r--r--
myisam_trig_0102.result	15.1 KB	-rw-r--r--
myisam_trig_03.result	25.7 KB	-rw-r--r--
myisam_trig_03e.result	49.44 KB	-rw-r--r--
myisam_trig_0407.result	15.45 KB	-rw-r--r--
myisam_trig_08.result	18.44 KB	-rw-r--r--
myisam_trig_09.result	10.02 KB	-rw-r--r--
myisam_trig_1011ext.result	11.81 KB	-rw-r--r--
myisam_views-big.result	595.72 KB	-rw-r--r--
ndb_bitdata.result	117 B	-rw-r--r--
ndb_cursors.result	116 B	-rw-r--r--
ndb_func_view.result	301.02 KB	-rw-r--r--
ndb_storedproc_02.result	36.28 KB	-rw-r--r--
ndb_storedproc_03.result	15.3 KB	-rw-r--r--
ndb_storedproc_06.result	13.38 KB	-rw-r--r--
ndb_storedproc_07.result	7.77 KB	-rw-r--r--
ndb_storedproc_08.result	20.03 KB	-rw-r--r--
ndb_storedproc_10.result	13.21 KB	-rw-r--r--
ndb_trig_0102.result	15.05 KB	-rw-r--r--
ndb_trig_03.result	25.65 KB	-rw-r--r--
ndb_trig_03e.result	50.97 KB	-rw-r--r--
ndb_trig_0407.result	15.4 KB	-rw-r--r--
ndb_trig_08.result	18.39 KB	-rw-r--r--
ndb_trig_09.result	9.97 KB	-rw-r--r--
ndb_trig_1011ext.result	11.77 KB	-rw-r--r--
ndb_views.result	580.63 KB	-rw-r--r--
processlist_priv_no_prot.resul...	28.85 KB	-rw-r--r--
processlist_priv_ps.result	28.88 KB	-rw-r--r--
processlist_val_no_prot.result	91.88 KB	-rw-r--r--
processlist_val_ps.result	91.89 KB	-rw-r--r--
storedproc.result	927.84 KB	-rw-r--r--

Code Editor : memory_storedproc_03.result

SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';

--source suite/funcs_1/storedproc/load_sp_tb.inc
--------------------------------------------------------------------------------

--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS db_storedproc;
DROP DATABASE IF EXISTS db_storedproc_1;
CREATE DATABASE db_storedproc;
CREATE DATABASE db_storedproc_1;
USE db_storedproc;
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
USE db_storedproc_1;
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
USE db_storedproc;
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
Warnings:
Warning	1265	Data truncated for column 'f3' at row 1
Warning	1265	Data truncated for column 'f3' at row 2
Warning	1265	Data truncated for column 'f3' at row 3
Warning	1265	Data truncated for column 'f3' at row 4
Warning	1265	Data truncated for column 'f3' at row 5
Warning	1265	Data truncated for column 'f3' at row 6
Warning	1265	Data truncated for column 'f3' at row 7
Warning	1265	Data truncated for column 'f3' at row 8
Warning	1265	Data truncated for column 'f3' at row 9
Warning	1265	Data truncated for column 'f3' at row 10
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
Warnings:
Warning	1265	Data truncated for column 'f3' at row 1
Warning	1265	Data truncated for column 'f3' at row 2
Warning	1265	Data truncated for column 'f3' at row 3
Warning	1265	Data truncated for column 'f3' at row 4
Warning	1265	Data truncated for column 'f3' at row 5
Warning	1265	Data truncated for column 'f3' at row 6
Warning	1265	Data truncated for column 'f3' at row 7
Warning	1265	Data truncated for column 'f3' at row 8
Warning	1265	Data truncated for column 'f3' at row 9
Warning	1265	Data truncated for column 'f3' at row 10
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
engine = <engine_to_be_tested>;
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;

Section 3.1.3 - Syntax checks for the stored procedure-specific flow
control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
--------------------------------------------------------------------------------

Testcase 3.1.3.7:
-----------------

Ensure that the IF statement acts correctly for all variants, including cases
where statements are nested.
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp9;
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
CREATE PROCEDURE sp9( action char(20), subaction char(20) )
BEGIN
if action = 'action' then
if subaction = 'subaction' then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
else
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
END if;
else
if subaction = 'subaction'
      then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
elseif subaction = 'subaction1'
      then
BEGIN
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
END;
else
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
END if;
END if;
END//
CALL sp9( 'action', 'subaction' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
f1	f2	f3
action	subaction	1
CALL sp9( 'temp', 'subaction' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
f1	f2	f3
none	subaction	3
CALL sp9( 'temp', 'subaction1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
f1	f2	f3
none	subaction1	4
CALL sp9( 'action', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
f1	f2	f3
action	none	2
CALL sp9( 'temp', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
f1	f2	f3
none	none	5
DROP PROCEDURE sp9;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;

Testcase 3.1.3.8.:
------------------

Ensure that the CASE statement acts correctly for all variants, including cases
where statements are nested.
--------------------------------------------------------------------------------
drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp10;
create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
CREATE PROCEDURE sp10( action char(20), subaction char(20) )
BEGIN
case action
when 'action' then
case
when subaction = 'subaction_1' then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
when subaction = 'subaction_2' then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
else
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
END case;
else
case
when subaction = 'subaction_1' then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
when subaction = 'subaction_2' then
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
else
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
END case;
END case;
END//
CALL sp10( 'action', 'subaction_1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
action	subaction_2	1
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
CALL sp10( 'action', 'subaction_2' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
action	subaction_2	2
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
CALL sp10( 'temp', 'subaction_1' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
none	subaction_1	4
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
CALL sp10( 'temp', 'subaction_2' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
none	subaction_2	5
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
CALL sp10( 'action', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
action	none	3
delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
CALL sp10( 'temp', 'temp' );
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
none	none	6
DROP PROCEDURE sp10;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;

Testcase 3.1.3.9 + 3.1.3.15:
----------------------------

09. Ensure that the LOOP statement acts correctly for all variants, including
.   cases where statements are nested.
15. Ensure that the LEAVE statement acts correctly for all variants, including
.   cases where statements are nested.
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
DROP PROCEDURE IF EXISTS sp11;
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
CREATE PROCEDURE sp11( )
BEGIN
declare count1 integer default 1;
declare count2 integer default 1;
label1: loop
if count2 > 3 then leave label1;
END if;
set count1 = 1;
label2: loop
if count1 > 4 then leave label2;
END if;
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
set count1 = count1 + 1;
iterate label2;
END loop label2;
set count2 = count2 + 1;
iterate label1;
END loop label1;
END//
CALL sp11();
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
xyz	pqr	1
xyz	pqr	2
xyz	pqr	3
xyz	pqr	4
xyz	pqr	1
xyz	pqr	2
xyz	pqr	3
xyz	pqr	4
xyz	pqr	1
xyz	pqr	2
xyz	pqr	3
xyz	pqr	4
DROP PROCEDURE sp11;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;

Testcase 3.1.3.16:
------------------

Ensure that the ITERATE statement acts correctly for all variants, including
cases where statements are nested.
(tests for this testcase are also included in other testcases)
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp31316;
CREATE PROCEDURE sp31316( )
BEGIN
declare count1 integer default 1;
declare count2 integer default 1;
label1: loop
if count2 > 3 then leave label1;
END if;
set count1 = 1;
label2: loop
if count1 > 4 then leave label2;
END if;
insert into temp values( count1, count2);
set count1 = count1 + 1;
iterate label3;
END loop label2;
set count2 = count2 + 1;
iterate label1;
END loop label1;
END//
ERROR 42000: ITERATE with no matching label: label3

Testcase 3.1.3.18:
------------------

Ensure that the REPEAT statement acts correctly for all variants, including
cases where statements are nested.
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp17;
DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
CREATE PROCEDURE sp17( )
BEGIN
declare count1 integer default 1;
declare count2 integer default 1;
repeat
set count1 = count1 + 1;
set count2 = 1;
label1: repeat
set count2 = count2 + 1;
insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
until count2 > 3
END repeat label1;
until count1 > 3
END repeat;
END//
CALL sp17();
SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
f1	f2	f3
xyz	pqr	2
xyz	pqr	2
xyz	pqr	2
xyz	pqr	3
xyz	pqr	3
xyz	pqr	3
xyz	pqr	4
xyz	pqr	4
xyz	pqr	4
DROP PROCEDURE sp17;
DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;

Testcase 3.1.3.24:
------------------

Ensure that the WHILE statement acts correctly for all variants, including cases
where statements are nested.
--------------------------------------------------------------------------------
drop table IF EXISTS res_t21;
DROP PROCEDURE IF EXISTS sp21;
create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
insert into res_t21 values('ashwin', 'mokadam', 25);
CREATE PROCEDURE sp21( )
BEGIN
declare count1 integer default 0;
declare count2 integer default 0;
while count1 < 3 do
BEGIN
declare ithisissamevariablename int default 100;
SELECT ithisissamevariablename;
BEGIN
declare ithisissamevariablename int default 200;
SELECT ithisissamevariablename;
END;
set count2 = 0;
label1: while count2 < 3 do
BEGIN
declare count1 integer default 7;
set count2 = count2 + 1;
insert into res_t21 values( 'xyz' , 'pqr', count2);
label2: while count1 < 10 do
set count1 = count1 + 1;
insert into res_t21 values( 'xyz' , 'pqr', count1);
END while label2;
END;
END while label1;
set count1 = count1 + 1;
END;
END while;
END//
CALL sp21();
ithisissamevariablename
100
ithisissamevariablename
200
ithisissamevariablename
100
ithisissamevariablename
200
ithisissamevariablename
100
ithisissamevariablename
200
SELECT * from res_t21;
name	surname	age_averylongfieldname_averylongname_1234569
ashwin	mokadam	25
xyz	pqr	1
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	2
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	3
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	1
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	2
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	3
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	1
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	2
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
xyz	pqr	3
xyz	pqr	8
xyz	pqr	9
xyz	pqr	10
DROP PROCEDURE sp21;
drop table res_t21;

Testcase 3.1.3.30:
------------------

Ensure that multiple cases of all possible combinations of the control flow
statements, nested within multiple compound statements within a stored
procedure, always act correctly and return the expected result.
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS res_tbl;
DROP PROCEDURE IF EXISTS sp31330;
create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
f5 set('one', 'two', 'three', 'four', 'five') default 'one');
CREATE PROCEDURE sp31330 (path int)
BEGIN
declare count int default 1;
declare var1 text;
declare var2 blob;
declare var3 date;
declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
case
when path=1 then
set var3 = '2000-11-09';
set var1 = 'flowing through case 1';
label1: loop
if count > 5 then
if var4=1000 then
set var2 = 'exiting out of case 1 - invalid SET';
END if;
if var4='two' then
set var2 = 'exiting out of case 1';
END if;
insert into res_tbl values (1, var1, var2, var3, (count-2));
leave label1;
elseif count = 5 then
set count= count + 2;
set var4='two';
iterate label1;
else
set count= count + 1;
END if;
set var4='one';
END loop label1;
when path=2 then
set var3 = '1989-11-09';
set var1 = 'flowing through case 2';
set @count3=0;
label2: repeat
set count=count + 1;
set @count2=1;
while @count2 <= 5 do
set @count2 = @count2 + 1;
END while;
SELECT @count2;
set @count3=@count3 + @count2;
until count > 5
END repeat label2;
set var2 = 'exiting out of case 2';
set var4 = count-3;
SELECT @count3;
insert into res_tbl values (2, var1, var2, var3, var4);
ELSE BEGIN
set @error_opt='undefined path specified';
SELECT @error_opt;
END;
END case;
END//
CALL sp31330();
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp31330; expected 1, got 0
CALL sp31330(1);
SELECT * from res_tbl;
f1	f2	f3	f4	f5
1	flowing through case 1	exiting out of case 1	2000-11-09	one,three
CALL sp31330(2);
@count2
6
@count2
6
@count2
6
@count2
6
@count2
6
@count3
30
SELECT * from res_tbl;
f1	f2	f3	f4	f5
1	flowing through case 1	exiting out of case 1	2000-11-09	one,three
2	flowing through case 2	exiting out of case 2	1989-11-09	one,two
CALL sp31330(4);
@error_opt
undefined path specified
DROP PROCEDURE sp31330;
drop table res_tbl;

.                               +++ END OF SCRIPT +++
--------------------------------------------------------------------------------

${this.title}

Code Editor : memory_storedproc_03.result