--source suite/funcs_1/storedproc/load_sp_tb.inc -------------------------------------------------------------------------------- SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; --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 charset utf8mb4; CREATE DATABASE db_storedproc_1 charset utf8mb4; 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' 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' 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; SET sql_mode = default; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Section 3.1.3 - Syntax checks for the stored procedure-specific flow control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE: -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.7: ----------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Ensure that the IF statement acts correctly for all variants, including cases where statements are nested. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.8.: ------------------ Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Ensure that the CASE statement acts correctly for all variants, including cases where statements are nested. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.9 + 3.1.3.15: ---------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.16: ------------------ Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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) -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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 Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.18: ------------------ Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Ensure that the REPEAT statement acts correctly for all variants, including cases where statements are nested. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.24: ------------------ Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Ensure that the WHILE statement acts correctly for all variants, including cases where statements are nested. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Testcase 3.1.3.30: ------------------ Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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. -------------------------------------------------------------------------------- Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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 +++ --------------------------------------------------------------------------------