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 = ; load data infile '/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 = ; load data infile '/std_data/funcs_1/t4.txt' into table t2; create table t3(f1 char(20),f2 char(20),f3 integer) engine = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/std_data/funcs_1/t7.txt' ignore 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 = ; load data infile '/std_data/funcs_1/t7.txt' ignore 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 = ; load data infile '/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 = ; load data infile '/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 = ; load data infile '/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; --source suite/funcs_1/storedproc/cleanup_sp_tb.inc -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS db_storedproc; DROP DATABASE IF EXISTS db_storedproc_1; . +++ END OF SCRIPT +++ --------------------------------------------------------------------------------