Debian Codegraph @

mysql-test/suite/sys_vars/inc/min_examined_row_limit_basic.inc

raw
1 ################ mysql-test\t\min_examined_row_limit_basic.test #################
2 # #
3 # Variable Name: min_examined_row_limit #
4 # Scope: GLOBAL | SESSION #
5 # Access Type: Dynamic #
6 # Data Type: numeric #
7 # Default Value: 0 #
8 # Range: 0-4294967295/18446744073709551615 #
9 # #
10 # #
11 # Creation Date: 2008-02-07 #
12 # Author: Salman #
13 # #
14 # Description: Test Cases of Dynamic System Variable min_examined_row_limit #
15 # that checks the behavior of this variable in the following ways #
16 # * Default Value #
17 # * Valid & Invalid values #
18 # * Scope & Access method #
19 # * Data Integrity #
20 # #
21 # Modified: Pavan Naik, 2015-11-24 #
22 # Added boundary value test cases according to the type of the #
23 # server binary. #
24 # #
25 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
26 # server-system-variables.html #
27 # #
28 #################################################################################
29
30 --source include/load_sysvars.inc
31
32 ######################################################################
33 # START OF min_examined_row_limit TESTS #
34 ######################################################################
35
36
37 #############################################################
38 # Save initial value #
39 #############################################################
40
41 SET @start_global_value = @@global.min_examined_row_limit;
42 SELECT @start_global_value;
43 SET @start_session_value = @@session.min_examined_row_limit;
44 SELECT @start_session_value;
45
46
47 --echo '#--------------------FN_DYNVARS_089_01-------------------------#'
48 ######################################################################
49 # Display the DEFAULT value of min_examined_row_limit #
50 ######################################################################
51
52 SET @@global.min_examined_row_limit = 100;
53 SET @@global.min_examined_row_limit = DEFAULT;
54 SELECT @@global.min_examined_row_limit;
55
56 SET @@session.min_examined_row_limit = 200;
57 SET @@session.min_examined_row_limit = DEFAULT;
58 SELECT @@session.min_examined_row_limit;
59
60
61 --echo '#--------------------FN_DYNVARS_089_02-------------------------#'
62 ########################################################################
63 # Check the DEFAULT value of min_examined_row_limit #
64 ########################################################################
65
66 SET @@global.min_examined_row_limit = DEFAULT;
67 SELECT @@global.min_examined_row_limit = 0;
68
69 SET @@session.min_examined_row_limit = DEFAULT;
70 SELECT @@session.min_examined_row_limit = 0;
71
72
73 --echo '#--------------------FN_DYNVARS_089_03-------------------------#'
74 ################################################################################
75 # Change the value of min_examined_row_limit to a valid value for GLOBAL Scope #
76 ################################################################################
77
78 SET @@global.min_examined_row_limit = 0;
79 SELECT @@global.min_examined_row_limit;
80 SET @@global.min_examined_row_limit = 1;
81 SELECT @@global.min_examined_row_limit;
82 SET @@global.min_examined_row_limit = 60020;
83 SELECT @@global.min_examined_row_limit;
84 SET @@global.min_examined_row_limit = 65535;
85 SELECT @@global.min_examined_row_limit;
86 SET @@global.min_examined_row_limit = 4294967295;
87 SELECT @@global.min_examined_row_limit;
88 SET @@global.min_examined_row_limit = 4294967294;
89 SELECT @@global.min_examined_row_limit;
90
91 # Test cases for 64-bit machines
92 if($mach64)
93 {
94 --disable_warnings
95 SET @@global.min_examined_row_limit = 18446744073709551615;
96 --enable_warnings
97
98 # Bug#11752618 - 64-BIT WINDOWS VARIABLES LIMITED TO DIFFERENT VALUES THAN 64-BIT LINUX/SO
99 let $machine_name = `SELECT CONVERT(@@version_compile_os using latin1) IN ("Win64", "Windows")`;
100
101 if($machine_name)
102 {
103 --replace_result 4294967295 18446744073709551615
104 }
105 SELECT @@global.min_examined_row_limit;
106 }
107
108
109 --echo '#--------------------FN_DYNVARS_089_04-------------------------#'
110 ###################################################################################
111 # Change the value of min_examined_row_limit to a valid value for SESSION Scope #
112 ###################################################################################
113
114 SET @@session.min_examined_row_limit = 0;
115 SELECT @@session.min_examined_row_limit;
116 SET @@session.min_examined_row_limit = 1;
117 SELECT @@session.min_examined_row_limit;
118 SET @@session.min_examined_row_limit = 50050;
119 SELECT @@session.min_examined_row_limit;
120 SET @@session.min_examined_row_limit = 65535;
121 SELECT @@session.min_examined_row_limit;
122 SET @@session.min_examined_row_limit = 4294967295;
123 SELECT @@session.min_examined_row_limit;
124 SET @@session.min_examined_row_limit = 4294967294;
125 SELECT @@session.min_examined_row_limit;
126
127 # Test cases for 64-bit machines
128 if($mach64)
129 {
130 --disable_warnings
131 SET @@session.min_examined_row_limit = 18446744073709551615;
132 --enable_warnings
133
134 # Bug#11752618 - 64-BIT WINDOWS VARIABLES LIMITED TO DIFFERENT VALUES THAN 64-BIT LINUX/SO
135 if($machine_name)
136 {
137 --replace_result 4294967295 18446744073709551615
138 }
139 SELECT @@session.min_examined_row_limit;
140 }
141
142 --echo '#------------------FN_DYNVARS_089_05-----------------------#'
143 ####################################################################
144 # Change the value of min_examined_row_limit to an invalid value #
145 ####################################################################
146
147 SET @@global.min_examined_row_limit = -1024;
148 SELECT @@global.min_examined_row_limit;
149 --Error ER_WRONG_TYPE_FOR_VAR
150 SET @@global.min_examined_row_limit = 65530.34;
151 SELECT @@global.min_examined_row_limit;
152 --Error ER_WRONG_TYPE_FOR_VAR
153 SET @@global.min_examined_row_limit = test;
154 SELECT @@global.min_examined_row_limit;
155
156 if($mach32)
157 {
158 SET @@global.min_examined_row_limit = 4294967296;
159 SELECT @@global.min_examined_row_limit;
160
161 SET @@global.min_examined_row_limit = 42949672950;
162 SELECT @@global.min_examined_row_limit;
163 }
164
165 if($mach64)
166 {
167 --error ER_WRONG_TYPE_FOR_VAR
168 SET @@global.min_examined_row_limit = 18446744073709551616;
169 SELECT @@global.min_examined_row_limit;
170 }
171
172 SET @@session.min_examined_row_limit = -1;
173 SELECT @@session.min_examined_row_limit;
174 --Error ER_WRONG_TYPE_FOR_VAR
175 SET @@session.min_examined_row_limit = 65530.34;
176
177 --Error ER_WRONG_TYPE_FOR_VAR
178 SET @@session.min_examined_row_limit = test;
179 SELECT @@session.min_examined_row_limit;
180
181 if($mach32)
182 {
183 SET @@session.min_examined_row_limit = 4294967296;
184 SELECT @@session.min_examined_row_limit;
185
186 SET @@session.min_examined_row_limit = 42949672950;
187 SELECT @@session.min_examined_row_limit;
188 }
189
190 if($mach64)
191 {
192 --error ER_WRONG_TYPE_FOR_VAR
193 SET @@session.min_examined_row_limit = 18446744073709551616;
194 SELECT @@session.min_examined_row_limit;
195 }
196
197 --echo '#------------------FN_DYNVARS_089_06-----------------------#'
198 ####################################################################
199 # Check if the value in GLOBAL Table matches value in variable #
200 ####################################################################
201
202 --disable_warnings
203 SELECT @@global.min_examined_row_limit = VARIABLE_VALUE
204 FROM performance_schema.global_variables
205 WHERE VARIABLE_NAME='min_examined_row_limit';
206 --enable_warnings
207
208 --echo '#------------------FN_DYNVARS_089_07-----------------------#'
209 ####################################################################
210 # Check if the value in SESSION Table matches value in variable #
211 ####################################################################
212
213 --disable_warnings
214 SELECT @@session.min_examined_row_limit = VARIABLE_VALUE
215 FROM performance_schema.session_variables
216 WHERE VARIABLE_NAME='min_examined_row_limit';
217 --enable_warnings
218
219 --echo '#------------------FN_DYNVARS_089_08-----------------------#'
220 ####################################################################
221 # Check if TRUE and FALSE values can be used on variable #
222 ####################################################################
223
224 SET @@global.min_examined_row_limit = TRUE;
225 SELECT @@global.min_examined_row_limit;
226 SET @@global.min_examined_row_limit = FALSE;
227 SELECT @@global.min_examined_row_limit;
228
229
230 --echo '#---------------------FN_DYNVARS_089_09----------------------#'
231 ####################################################################################
232 # Check if accessing variable with and without GLOBAL point to same variable #
233 ####################################################################################
234
235 SET @@global.min_examined_row_limit = 10;
236 SELECT @@min_examined_row_limit = @@global.min_examined_row_limit;
237
238
239 --echo '#---------------------FN_DYNVARS_089_10----------------------#'
240 ########################################################################################################
241 # Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable #
242 ########################################################################################################
243
244 SET @@min_examined_row_limit = 100;
245 SELECT @@min_examined_row_limit = @@local.min_examined_row_limit;
246 SELECT @@local.min_examined_row_limit = @@session.min_examined_row_limit;
247
248
249 --echo '#---------------------FN_DYNVARS_089_11----------------------#'
250 ###################################################################################
251 # Check if min_examined_row_limit can be accessed with and without @@ sign #
252 ###################################################################################
253
254 SET min_examined_row_limit = 1;
255 SELECT @@min_examined_row_limit;
256 --Error ER_UNKNOWN_TABLE
257 SELECT local.min_examined_row_limit;
258 --Error ER_UNKNOWN_TABLE
259 SELECT session.min_examined_row_limit;
260 --Error ER_BAD_FIELD_ERROR
261 SELECT min_examined_row_limit = @@session.min_examined_row_limit;
262
263 --echo '#---------------------FN_DYNVARS_089_12----------------------#'
264 ######################################################################################
265 # Check if changing session variable throws error without sufficient privileges #
266 ######################################################################################
267 CREATE USER foo, bar;
268 GRANT SESSION_VARIABLES_ADMIN ON *.* TO bar;
269 --let $conn_name = foo_connection
270 --connect($conn_name, localhost, foo,,,)
271 SELECT @@session.min_examined_row_limit;
272 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
273 SET @@session.min_examined_row_limit = 1;
274 --connection default
275 disconnect $conn_name;
276 --let $conn_name = bar_connection
277 --connect($conn_name, localhost, bar,,,)
278 SELECT @@session.min_examined_row_limit;
279 SET @@session.min_examined_row_limit = 1;
280 SELECT @@session.min_examined_row_limit;
281 --connection default
282 disconnect $conn_name;
283 DROP USER foo, bar;
284
285 ####################################
286 # Restore initial value #
287 ####################################
288
289 SET @@global.min_examined_row_limit = @start_global_value;
290 SELECT @@global.min_examined_row_limit;
291 SET @@session.min_examined_row_limit = @start_session_value;
292 SELECT @@session.min_examined_row_limit;
293
294
295 #############################################################
296 # END OF min_examined_row_limit TESTS #
297 #############################################################
298