Debian Codegraph @

mysql-test/suite/sys_vars/t/max_sp_recursion_depth_func.test

raw
1 ############# mysql-test\t\max_sp_recursion_depth_func.test ###################
2 # #
3 # Variable Name: max_sp_recursion_depth #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: NUMERIC #
7 # Default Value: 0 #
8 # Max Value: 25 #
9 # #
10 # #
11 # Creation Date: 2008-03-02 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable "max_sp_recursion_depth #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 #Reference:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19 # option_mysqld_max_sp_recursion_depth #
20 # #
21 ###############################################################################
22
23
24 #
25 # Setup
26 #
27
28 SET @session_max_recursion_depth = @@SESSION.max_sp_recursion_depth;
29
30
31 --echo '#--------------------FN_DYNVARS_099_01-------------------------#'
32 ##########################################################
33 # Test behavior of variable on new connection # 01 #
34 ##########################################################
35 CONNECT (test_con1,localhost,root,,);
36 CONNECTION test_con1;
37
38
39 # Setting session value of variable
40 SET @@session.max_sp_recursion_depth = 10;
41 SELECT @@session.max_sp_recursion_depth;
42 # create procedure to add rows
43
44 --disable_warnings
45 DROP PROCEDURE IF EXISTS sp_addRecords;
46 --enable_warnings
47
48 DELIMITER //;
49
50 CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
51 BEGIN
52 SELECT var1,var2;
53 IF var1 < var2 THEN
54 CALL sp_addRecords(var1+1,var2);
55 SELECT var1,var2;
56 END IF;
57 END //
58
59 DELIMITER ;//
60
61
62 CALL sp_addRecords(0,8);
63
64 --echo '#--------------------FN_DYNVARS_099_02-------------------------#'
65 ##########################################################
66 # Test behavior of variable on new connection # 02 #
67 ##########################################################
68
69 CONNECT (test_con2,localhost,root,,);
70 connection test_con2;
71
72
73 # Setting session value of variable and inserting data in table
74 SET @@session.max_sp_recursion_depth = 4;
75 SELECT @@session.max_sp_recursion_depth;
76 # create procedure to add rows
77
78 --disable_warnings
79 DROP PROCEDURE IF EXISTS sp_addRecords1;
80 --enable_warnings
81
82 DELIMITER //;
83
84 CREATE PROCEDURE sp_addRecords1 (IN var1 INT,IN var2 INT)
85 BEGIN
86 SELECT var1,var2;
87 IF var1 < var2 THEN
88 CALL sp_addRecords1(var1+1,var2);
89 SELECT var1,var2;
90 END IF;
91 END //
92
93 DELIMITER ;//
94
95
96 CALL sp_addRecords1(0,4);
97
98
99
100
101 --echo '#---------------------FN_DYNVARS_99_03----------------------#'
102 ####################################################################
103 # Check if max_sp_recursion_depth value is set to 10 #
104 ####################################################################
105 SET @@max_sp_recursion_depth = 10;
106 # create procedure to add rows
107
108 --disable_warnings
109 DROP PROCEDURE IF EXISTS sp_addRecords2;
110 --enable_warnings
111
112 DELIMITER //;
113
114 CREATE PROCEDURE sp_addRecords2(IN var1 INT,IN var2 INT)
115 BEGIN
116 SELECT var1,var2;
117 IF var1 < var2 THEN
118 CALL sp_addRecords2(var1+1,var2);
119 SELECT var1,var2;
120 END IF;
121 END //
122
123 DELIMITER ;//
124
125
126 CALL sp_addRecords2(0,8);
127
128
129 --echo '#---------------------FN_DYNVARS_99_04----------------------#'
130 ###############################################################################
131 #Check if max_sp_recursion_depth value is set lower then called recursion value
132 ###############################################################################
133
134
135 SET @@max_sp_recursion_depth = 4;
136 # create procedure to add rows
137 #DROP PROCEDURE IF EXISTS sp_addRecords1;
138
139 --disable_warnings
140 DROP PROCEDURE IF EXISTS sp_addRecords3;
141 --enable_warnings
142
143 DELIMITER //;
144
145 CREATE PROCEDURE sp_addRecords3 (IN var1 INT,IN var2 INT)
146 BEGIN
147 SELECT var1,var2;
148 IF var1 < var2 THEN
149 CALL sp_addRecords3(var1+1,var2);
150 SELECT var1,var2;
151 END IF;
152 END //
153
154 DELIMITER ;//
155
156 --echo Expected error 'SP Recursion limit'
157 --ERROR ER_SP_RECURSION_LIMIT
158 CALL sp_addRecords3(0,8);
159
160 #
161 # Cleanup
162 #
163
164 connection default;
165
166 disconnect test_con1;
167 disconnect test_con2;
168
169 --disable_warnings
170 DROP PROCEDURE IF EXISTS sp_addRecords;
171 DROP PROCEDURE IF EXISTS sp_addRecords1;
172 DROP PROCEDURE IF EXISTS sp_addRecords2;
173 DROP PROCEDURE IF EXISTS sp_addRecords3;
174 --enable_warnings
175
176 SET @@SESSION.max_sp_recursion_depth = @session_max_recursion_depth;