Debian Codegraph @

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

raw
1 ############# mysql-test\t\max_sort_length_func.test ###########################
2 # #
3 # Variable Name: sql_warnings #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: NUMERIC #
7 # Default Value: 1024 #
8 # Values: 4-8388608 #
9 # #
10 # #
11 # Creation Date: 2008-03-02 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable max_sort_length #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 # Reference: #
19 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
20 # #
21 ################################################################################
22
23 #
24 # Setup
25 #
26
27 SET @start_value= @@global.max_sort_length;
28
29 SET @session_max_sort_length = @@Session.max_sort_length;
30
31
32 --disable_warnings
33 DROP TABLE IF EXISTS t;
34 --enable_warnings
35
36 #########################
37 # Creating new table #
38 #########################
39
40
41 --echo ** creating tables **
42 CREATE TABLE t
43 (
44 id INT AUTO_INCREMENT PRIMARY KEY,
45 c TEXT(30)
46 ) charset latin1;
47
48 CREATE TABLE t1
49 (
50 id INT AUTO_INCREMENT PRIMARY KEY,
51 c BLOB(30)
52 ); # Charset doesn't matter for blob nor int.
53
54 CREATE TABLE t2
55 (
56 id INT AUTO_INCREMENT PRIMARY KEY,
57 c TEXT(30)
58 ) charset latin1;
59
60
61
62 --echo '#--------------------FN_DYNVARS_098_01-------------------------#'
63 ##########################################################
64 # Test behavior of variable on new connection # 01 #
65 ##########################################################
66
67
68 --echo ** Connecting test_con1 using username 'root' **
69 connect (test_con1,localhost,root,,);
70 --echo ** Connection test_con1 **
71 connection test_con1;
72
73 # Value of session & global vairable here should be 10
74 SELECT @@global.max_sort_length = 10;
75 SELECT @@session.max_sort_length = 10;
76
77 # Setting global value of variable and inserting data in table
78 --echo ** Setting value to 30 and inserting data **
79 SET @@global.max_sort_length = 30;
80 SELECT @@global.max_sort_length;
81 INSERT INTO t set c = repeat('x',29);
82 INSERT INTO t set c = concat(repeat('x',28),'r','x');
83 INSERT INTO t set c = concat(repeat('x',28),'s','y');
84 INSERT INTO t set c = concat(repeat('x',28),'g','w');
85 SELECT c from t ORDER BY c, id;
86 --echo ** Results should be sorted **
87
88 # Setting session value of variable and inserting data in table
89 SET @@session.max_sort_length = 29;
90 SELECT @@session.max_sort_length;
91 INSERT INTO t set c = repeat('x',29);
92 INSERT INTO t set c = concat(repeat('x',28),'r','x');
93 INSERT INTO t set c = concat(repeat('x',28),'s','y');
94 INSERT INTO t set c = concat(repeat('x',28),'g','w');
95 SELECT c from t ORDER BY c, id;
96 --echo ** Results should be sorted **
97
98 --echo '#--------------------FN_DYNVARS_098_02-------------------------#'
99 ##########################################################
100 # Test behavior of variable on new connection # 02 #
101 ##########################################################
102
103 --echo ** Connecting test_con2 using username 'root' **
104 connect (test_con2,localhost,root,,);
105 --echo ** Connection test_con2 **
106 connection test_con2;
107
108
109 ## Setting global value of variable and inserting data in table
110 SET @@global.max_sort_length = 30;
111 SELECT @@global.max_sort_length;
112 INSERT INTO t set c = repeat('x',29);
113 INSERT INTO t set c = concat(repeat('x',28),'r','x');
114 INSERT INTO t set c = concat(repeat('x',28),'s','y');
115 INSERT INTO t set c = concat(repeat('x',28),'g','w');
116 SELECT c from t ORDER BY c, id;
117
118 --echo ** Results should be sorted **
119
120 # Setting session value of variable and inserting data in table
121 SET @@session.max_sort_length = 20;
122 SELECT @@session.max_sort_length;
123 INSERT INTO t set c = repeat('x',29);
124 INSERT INTO t set c = concat(repeat('x',28),'r','x');
125 INSERT INTO t set c = concat(repeat('x',28),'s','y');
126 INSERT INTO t set c = concat(repeat('x',28),'g','w');
127 SELECT c from t ORDER BY c, id;
128
129 --echo ** Results should not be sorted **
130
131 --echo '#--------------------FN_DYNVARS_098_03-------------------------#'
132 #########################################################
133 #Check if sorting is applied with the max_sort_length=20#
134 #########################################################
135
136 ###########################################
137 # Setting new value for max_sort_length #
138 ###########################################
139
140 SET max_sort_length=20;
141
142 ###################################
143 # Inserting values in table t #
144 ###################################
145
146 INSERT INTO t set c = repeat('x',29);
147 INSERT INTO t set c = concat(repeat('x',28),'r','x');
148 INSERT INTO t set c = concat(repeat('x',28),'s','y');
149 INSERT INTO t set c = concat(repeat('x',28),'g','w');
150
151 SELECT c from t ORDER BY c, id;
152 --echo ** Results should not be sorted **
153
154
155 --echo '#--------------------FN_DYNVARS_098_04-------------------------#'
156 #########################################################
157 #Check if sorting is applied with the max_sort_length=29#
158 #########################################################
159
160 SET max_sort_length=29;
161 SELECT c from t ORDER BY c, id;
162 --echo ** Results should be sorted **
163
164 --echo '#--------------------FN_DYNVARS_098_05-------------------------#'
165 #########################################################
166 #Check if sorting is applied with the max_sort_length=30#
167 #########################################################
168
169 SET max_sort_length=30;
170 SELECT c from t ORDER BY c, id;
171 --echo ** Results should be sorted **
172
173 --echo '#--------------------FN_DYNVARS_098_06-------------------------#'
174 ##############################################################
175 #Check if sorting is applied with the max_sort_length=Default#
176 ##############################################################
177
178
179 SET max_sort_length=default;
180 SELECT c from t ORDER BY c, id;
181 --echo ** Results should be sorted **
182
183
184 --echo '#--------------------FN_DYNVARS_098_07-------------------------#'
185 ###########################################
186 #Check if sorting is applied on BLOB type #
187 ###########################################
188 --echo Testing type BLOB
189 # Setting global value of variable and inserting data in table
190 # Note that after subtracting the length byte for the tinyblob, it will be
191 # rounded down to the closest even value, which is 28.
192 SET @@global.max_sort_length = 30;
193 SELECT @@global.max_sort_length;
194 INSERT INTO t1 set c = repeat('x',28);
195 INSERT INTO t1 set c = concat(repeat('x',27),'r','x');
196 INSERT INTO t1 set c = concat(repeat('x',27),'s','y');
197 INSERT INTO t1 set c = concat(repeat('x',27),'g','w');
198 SELECT c from t1 ORDER BY c, id;
199 --echo ** Results should be sorted **
200
201 # Setting session value of variable and inserting data in table
202 SET @@session.max_sort_length = 20;
203 SELECT @@session.max_sort_length;
204 INSERT INTO t1 set c = repeat('x',29);
205 INSERT INTO t1 set c = concat(repeat('x',28),'r','x');
206 INSERT INTO t1 set c = concat(repeat('x',28),'s','y');
207 INSERT INTO t1 set c = concat(repeat('x',28),'g','w');
208 SELECT c from t1 ORDER BY c, id;
209 --echo ** Results should still be sorted **
210
211 --echo '#--------------------FN_DYNVARS_098_08-------------------------#'
212 ###########################################
213 #Check if sorting is applied on TEXT type #
214 ###########################################
215 --echo Testing type TEXT
216 # Setting global value of variable and inserting data in table
217 SET @@global.max_sort_length = 30;
218 SELECT @@global.max_sort_length;
219 INSERT INTO t2 set c = repeat('x',29);
220 INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
221 INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
222 INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
223 SELECT c from t2 ORDER BY c, id;
224 --echo ** Results should not be sorted **
225
226 # Setting session value of variable and inserting data in table
227 SET @@session.max_sort_length = 20;
228 SELECT @@session.max_sort_length;
229 INSERT INTO t2 set c = repeat('x',29);
230 INSERT INTO t2 set c = concat(repeat('x',28),'r','x');
231 INSERT INTO t2 set c = concat(repeat('x',28),'s','y');
232 INSERT INTO t2 set c = concat(repeat('x',28),'g','w');
233 SELECT c from t2 ORDER BY c, id;
234 --echo ** Results should not be sorted **
235
236
237 #
238 # Cleanup
239 #
240
241 --echo ** Connection default **
242 connection default;
243
244 --echo ** Disconnecting test_con1, test_con2 **
245 disconnect test_con1;
246 disconnect test_con2;
247
248 SET @@SESSION.max_sort_length = @session_max_sort_length;
249
250 --disable_warnings
251 DROP TABLE IF EXISTS t;
252 DROP TABLE IF EXISTS t1;
253 DROP TABLE IF EXISTS t2;
254 --enable_warnings
255
256 SET @@global.max_sort_length= @start_value;