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 #########################
30 # Creating new table #
31 #########################
32
33 --echo ** creating tables **
34 CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT) CHARSET=latin1;
35 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, c BLOB) CHARSET=latin1;
36 CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY, c TEXT) CHARSET=latin1;
37
38 --echo '#--------------------FN_DYNVARS_098_01-------------------------#'
39 ##########################################################
40 # Test behavior of variable on new connection # 01 #
41 ##########################################################
42
43 connect (test_con1,localhost,root,,);
44
45 # Setting global value of variable and inserting data in table
46 --echo ** Setting value to 70 and inserting data **
47 SET @@global.max_sort_length = 70;
48 SELECT @@global.max_sort_length;
49 INSERT INTO t set c = repeat('x',69);
50 INSERT INTO t set c = concat(repeat('x',68),'r','x');
51 INSERT INTO t set c = concat(repeat('x',68),'s','y');
52 INSERT INTO t set c = concat(repeat('x',68),'g','w');
53 SELECT c from t ORDER BY c, id;
54 --echo ** Results should be sorted **
55
56 # Setting session value of variable and inserting data in table
57 SET @@session.max_sort_length = 69;
58 SELECT @@session.max_sort_length;
59 INSERT INTO t set c = repeat('x',69);
60 INSERT INTO t set c = concat(repeat('x',68),'r','x');
61 INSERT INTO t set c = concat(repeat('x',68),'s','y');
62 INSERT INTO t set c = concat(repeat('x',68),'g','w');
63 SELECT c from t ORDER BY c, id;
64 --echo ** Results should be sorted **
65
66 --echo '#--------------------FN_DYNVARS_098_02-------------------------#'
67 ##########################################################
68 # Test behavior of variable on new connection # 02 #
69 ##########################################################
70
71 connect (test_con2,localhost,root,,);
72
73 ## Setting global value of variable and inserting data in table
74 SET @@global.max_sort_length = 70;
75 SELECT @@global.max_sort_length;
76 INSERT INTO t set c = repeat('x',69);
77 INSERT INTO t set c = concat(repeat('x',68),'r','x');
78 INSERT INTO t set c = concat(repeat('x',68),'s','y');
79 INSERT INTO t set c = concat(repeat('x',68),'g','w');
80 SELECT c from t ORDER BY c, id;
81
82 --echo ** Results should be sorted **
83
84 # Setting session value of variable and inserting data in table
85 SET @@session.max_sort_length = 64;
86 SELECT @@session.max_sort_length;
87 INSERT INTO t set c = repeat('x',69);
88 INSERT INTO t set c = concat(repeat('x',68),'r','x');
89 INSERT INTO t set c = concat(repeat('x',68),'s','y');
90 INSERT INTO t set c = concat(repeat('x',68),'g','w');
91 SELECT c from t ORDER BY c, id;
92
93 --echo ** Results should not be sorted **
94
95 --echo '#--------------------FN_DYNVARS_098_03-------------------------#'
96 #########################################################
97 #Check if sorting is applied with the max_sort_length=64#
98 #########################################################
99
100 ###########################################
101 # Setting new value for max_sort_length #
102 ###########################################
103
104 SET max_sort_length=64;
105
106 ###################################
107 # Inserting values in table t #
108 ###################################
109
110 INSERT INTO t set c = repeat('x',69);
111 INSERT INTO t set c = concat(repeat('x',68),'r','x');
112 INSERT INTO t set c = concat(repeat('x',68),'s','y');
113 INSERT INTO t set c = concat(repeat('x',68),'g','w');
114
115 SELECT c from t ORDER BY c, id;
116 --echo ** Results should not be sorted **
117
118 RESET QUERY CACHE;
119
120 --echo '#--------------------FN_DYNVARS_098_04-------------------------#'
121 #########################################################
122 #Check if sorting is applied with the max_sort_length=69#
123 #########################################################
124
125 SET max_sort_length=69;
126 SELECT c from t ORDER BY c, id;
127 --echo ** Results should be sorted **
128
129 --echo '#--------------------FN_DYNVARS_098_05-------------------------#'
130 #########################################################
131 #Check if sorting is applied with the max_sort_length=70#
132 #########################################################
133
134 SET max_sort_length=70;
135 SELECT c from t ORDER BY c, id;
136 --echo ** Results should be sorted **
137
138 --echo '#--------------------FN_DYNVARS_098_06-------------------------#'
139 ##############################################################
140 #Check if sorting is applied with the max_sort_length=Default#
141 ##############################################################
142
143 SET max_sort_length=default;
144 SELECT @@max_sort_length;
145 SELECT c from t ORDER BY c, id;
146 --echo ** Results should be sorted **
147
148 --echo '#--------------------FN_DYNVARS_098_07-------------------------#'
149 ###########################################
150 #Check if sorting is applied on BLOB type #
151 ###########################################
152 --echo Testing type BLOB
153 # Setting global value of variable and inserting data in table
154 SET @@max_sort_length = 70;
155 SELECT @@max_sort_length;
156 INSERT INTO t1 set c = repeat('x',69);
157 INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
158 INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
159 INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
160 SELECT c from t1 ORDER BY c, id DESC;
161 --echo ** Results should be sorted **
162
163 # Setting session value of variable and inserting data in table
164 SET @@session.max_sort_length = 64;
165 SELECT @@session.max_sort_length;
166 INSERT INTO t1 set c = repeat('x',69);
167 INSERT INTO t1 set c = concat(repeat('x',68),'r','x');
168 INSERT INTO t1 set c = concat(repeat('x',68),'s','y');
169 INSERT INTO t1 set c = concat(repeat('x',68),'g','w');
170 SELECT c from t1 ORDER BY c, id;
171 --echo ** Results should not be sorted **
172
173 --echo '#--------------------FN_DYNVARS_098_08-------------------------#'
174 ###########################################
175 #Check if sorting is applied on CHAR type #
176 ###########################################
177 --echo Testing type CHAR
178 # Setting global value of variable and inserting data in table
179 SET @@global.max_sort_length = 70;
180 SELECT @@global.max_sort_length;
181 INSERT INTO t2 set c = repeat('x',69);
182 INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
183 INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
184 INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
185 SELECT c from t2 ORDER BY c, id;
186 --echo ** Results should not be sorted **
187
188 # Setting session value of variable and inserting data in table
189 SET @@session.max_sort_length = 64;
190 SELECT @@session.max_sort_length;
191 INSERT INTO t2 set c = repeat('x',69);
192 INSERT INTO t2 set c = concat(repeat('x',68),'r','x');
193 INSERT INTO t2 set c = concat(repeat('x',68),'s','y');
194 INSERT INTO t2 set c = concat(repeat('x',68),'g','w');
195 SELECT c from t2 ORDER BY c, id;
196 --echo ** Results should not be sorted **
197
198 DROP TABLE t, t1, t2;
199
200 --echo #
201 --echo # MDEV-27277 Add a warning when max_sort_length is reached
202 --echo #
203 set max_sort_length = 70;
204
205 --echo # Table having a fixed-length string field
206 create table t1(a char(100));
207
208 insert into t1 values
209 ('ShortStr1'), ('ShortStr2'),
210 (concat(repeat('Str', 25), 'zzz')), (concat(repeat('Str', 25), 'yyy')),
211 (concat(repeat('Str', 25), 'xxx'));
212
213 --echo # Strings are not sorted properly due to max_sort_length limitation
214 select a from t1 order by a;
215
216 --echo # Make sure there are warnings when a string function is used:
217 select a from t1 order by coalesce(a);
218 select a from t1 order by concat(a, '1');
219
220 select a from t1 order by binary(a);
221
222 --echo # Table having a variable-length string field and UTF16 encoding (2 bytes per char):
223 create table t2(a varchar(100)) character set 'utf16';
224
225 insert into t2 values
226 (concat(repeat('Str', 15), 'zzz')), (concat(repeat('Str', 15), 'yyy')),
227 (concat(repeat('Str', 15), 'xxx')),
228 ('shortString89'),
229 ('shortString51');
230
231 set max_sort_length = 64;
232
233 select * from t2 order by a;
234
235 --echo # Table having text blobs
236 create table t3(a text, b mediumtext, c longtext) character set 'utf16';
237 insert into t3 values
238 (concat(repeat('Text', 20), '999'), concat(repeat('Medium', 15), '99'), concat(repeat('Long', 20), '99')),
239 (concat(repeat('Text', 20), '888'), concat(repeat('Medium', 15), '88'), concat(repeat('Long', 20), '88')),
240 (concat(repeat('Text', 20), '777'), concat(repeat('Medium', 15), '77'), concat(repeat('Long', 20), '77')),
241 ('shortString89', 'short123', 'short456'),
242 ('shortString51', 'short777', 'short897');
243
244 select * from t3 order by a;
245
246 select * from t3 order by b desc;
247 select * from t3 order by coalesce(b) desc;
248
249 select * from t3 order by c;
250
251 select * from t3 order by c, a desc;
252
253 --echo # Packing of sort keys will be applied here:
254 select * from t3 order by a, c, b;
255
256 select * from t3 order by a;
257
258 --echo # Test a prepared statement re-execution (expecting warnings at both executions)
259 prepare stmt from "select * from t2 order by a";
260 execute stmt;
261 execute stmt;
262
263 --echo # Test a stored procedure
264 create procedure p1() select * from t2 order by a;
265 call p1();
266 call p1();
267 drop procedure p1;
268
269 --echo # Test a stored function
270 create function f1 () returns char(100) return (select a from t1 order by a limit 1);
271
272 --disable_view_protocol # Because warnings display different number of strings
273 select f1() as f1_res from t1 order by f1_res;
274 --enable_view_protocol
275
276 --echo # Test a view
277 create view v1 as select f1() as f1_res from t1 order by f1_res;
278 select * from v1;
279
280 drop function f1;
281
282 drop view v1;
283 drop table t1, t2, t3;
284
285 set max_sort_length = default;
286
287 --echo #
288 --echo # MDEV-35275 Unexpected WARN_SORTING_ON_TRUNCATED_LENGTH or assertion
289 --echo # failure in diagnostics area
290 --echo #
291
292 create table t1 (a int, b varchar(1024));
293 insert into t1 values (1,repeat('x',513)), (2,repeat('y',650));
294
295 insert into t1 select * from t1 order by b limit 1;
296
297 update t1 set a = 3 order by b limit 1;
298
299 delete from t1 where a < 3 order by b;
300
301 --echo # Make sure warnings from previous statements do not overflow to next ones
302 select a from t1;
303
304 drop table t1;
305
306 --echo #
307 --echo # MDEV-35307 Unexpected WARN_SORTING_ON_TRUNCATED_LENGTH or assertion
308 --echo # failure in diagnostics area #2
309 --echo #
310
311 --source include/have_innodb.inc
312
313 create table t1 (a varchar(1024)) engine=innodb;
314 insert into t1 values (repeat('a',1000)),(repeat('b',1000));
315 insert into t1 (a) select a from t1 order by a;
316
317 drop table t1;
318
319 #
320 # Cleanup
321 #
322
323 connection default;
324
325 disconnect test_con1;
326 disconnect test_con2;
327
328 SET @@global.max_sort_length= @start_value;