diff options
Diffstat (limited to 'mysql-test/t/max_sp_recursion_depth_func.test')
| -rw-r--r-- | mysql-test/t/max_sp_recursion_depth_func.test | 182 |
1 files changed, 182 insertions, 0 deletions
diff --git a/mysql-test/t/max_sp_recursion_depth_func.test b/mysql-test/t/max_sp_recursion_depth_func.test new file mode 100644 index 00000000000..ab43536457b --- /dev/null +++ b/mysql-test/t/max_sp_recursion_depth_func.test @@ -0,0 +1,182 @@ +############# mysql-test\t\max_sp_recursion_depth_func.test ################### +# # +# Variable Name: max_sp_recursion_depth # +# Scope: SESSION # +# Access Type: Dynamic # +# Data Type: NUMERIC # +# Default Value: 0 # +# Max Value: 25 # +# # +# # +# Creation Date: 2008-03-02 # +# Author: Sharique Abdullah # +# # +# Description: Test Cases of Dynamic System Variable "max_sp_recursion_depth # +# that checks behavior of this variable in the following ways # +# * Functionality based on different values # +# # +#Reference:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html# +# option_mysqld_max_sp_recursion_depth # +# # +############################################################################### + + +# +# Setup +# + +SET @session_max_recursion_depth = @@SESSION.max_sp_recursion_depth; + + +--echo '#--------------------FN_DYNVARS_099_01-------------------------#' +########################################################## +# Test behavior of variable on new connection # 01 # +########################################################## +--echo ** Connecting test_con1 using username 'root' ** +CONNECT (test_con1,localhost,root,,); +--echo ** Connection test_con1 ** +CONNECTION test_con1; + + +# Setting session value of variable +SET @@session.max_sp_recursion_depth = 10; +SELECT @@session.max_sp_recursion_depth; +# create procedure to add rows + +--disable_warnings +DROP PROCEDURE IF EXISTS sp_addRecords; +--enable_warnings + +DELIMITER //; + +CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT) +BEGIN +SELECT var1,var2; +IF var1 < var2 THEN + CALL sp_addRecords(var1+1,var2); + SELECT var1,var2; +END IF; +END // + +DELIMITER ;// + + +CALL sp_addRecords(0,8); + +--echo '#--------------------FN_DYNVARS_099_02-------------------------#' +########################################################## +# Test behavior of variable on new connection # 02 # +########################################################## + +--echo ** Connecting test_con2 using username 'root' ** +CONNECT (test_con2,localhost,root,,); +--echo ** Connection test_con2 ** +connection test_con2; + + +# Setting session value of variable and inserting data in table +SET @@session.max_sp_recursion_depth = 4; +SELECT @@session.max_sp_recursion_depth; +# create procedure to add rows + +--disable_warnings +DROP PROCEDURE IF EXISTS sp_addRecords1; +--enable_warnings + +DELIMITER //; + +CREATE PROCEDURE sp_addRecords1 (IN var1 INT,IN var2 INT) +BEGIN +SELECT var1,var2; +IF var1 < var2 THEN + CALL sp_addRecords1(var1+1,var2); + SELECT var1,var2; +END IF; +END // + +DELIMITER ;// + + +CALL sp_addRecords1(0,4); + + + + +--echo '#---------------------FN_DYNVARS_99_03----------------------#' +#################################################################### +# Check if max_sp_recursion_depth value is set to 10 # +#################################################################### +SET @@max_sp_recursion_depth = 10; +# create procedure to add rows + +--disable_warnings +DROP PROCEDURE IF EXISTS sp_addRecords2; +--enable_warnings + +DELIMITER //; + +CREATE PROCEDURE sp_addRecords2(IN var1 INT,IN var2 INT) +BEGIN +SELECT var1,var2; +IF var1 < var2 THEN + CALL sp_addRecords2(var1+1,var2); + SELECT var1,var2; +END IF; +END // + +DELIMITER ;// + + +CALL sp_addRecords2(0,8); + + +--echo '#---------------------FN_DYNVARS_99_04----------------------#' +############################################################################### +#Check if max_sp_recursion_depth value is set lower then called recursion value +############################################################################### + + +SET @@max_sp_recursion_depth = 4; +# create procedure to add rows +#DROP PROCEDURE IF EXISTS sp_addRecords1; + +--disable_warnings +DROP PROCEDURE IF EXISTS sp_addRecords3; +--enable_warnings + +DELIMITER //; + +CREATE PROCEDURE sp_addRecords3 (IN var1 INT,IN var2 INT) +BEGIN +SELECT var1,var2; +IF var1 < var2 THEN + CALL sp_addRecords3(var1+1,var2); + SELECT var1,var2; +END IF; +END // + +DELIMITER ;// + +--echo Expected error 'SP Recursion limit' +--ERROR ER_SP_RECURSION_LIMIT +CALL sp_addRecords3(0,8); + +# +# Cleanup +# + +--echo ** Connection default ** +connection default; + +--echo ** Disconnecting test_con1, test_con2 ** +disconnect test_con1; +disconnect test_con2; + +--disable_warnings +DROP PROCEDURE IF EXISTS sp_addRecords; +DROP PROCEDURE IF EXISTS sp_addRecords1; +DROP PROCEDURE IF EXISTS sp_addRecords2; +DROP PROCEDURE IF EXISTS sp_addRecords3; +--enable_warnings + +SET @@SESSION.max_sp_recursion_depth = @session_max_recursion_depth; |