Debian Codegraph @

mysql-test/suite/opt_trace/include/filesort_pq.inc

raw
1 --source include/have_optimizer_trace.inc
2 --source include/have_64bit.inc
3
4 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
5 --source include/no_protocol.inc
6
7 SET optimizer_trace_max_mem_size=1048576; # 1MB
8 SET end_markers_in_json=off;
9 SET optimizer_trace="enabled=on,one_line=off";
10
11 --echo #
12 --echo # WL#1393 - Optimizing filesort with small limit
13 --echo # WL#5834 - Add optimizer traces for sorting
14 --echo #
15
16
17 CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200))
18 charset latin1;
19 INSERT INTO t1(f1, f2) VALUES
20 (0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
21 (6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
22 (11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
23 (16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
24 (21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
25 (26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
26 (31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
27 (36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
28 (41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
29 (46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
30 (51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
31 (56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
32 (61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
33 (66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
34 (71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
35 (76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
36 (81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
37 (86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
38 (91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
39 (96,"96"),(97,"97"),(98,"98"),(99,"99");
40
41 ################
42 ## Test sort when source data fits in memory
43
44 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100;
45 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
46 SELECT * FROM information_schema.OPTIMIZER_TRACE;
47 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
48 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
49 SELECT * FROM information_schema.OPTIMIZER_TRACE;
50 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
51 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
52 SELECT * FROM information_schema.OPTIMIZER_TRACE;
53 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
54 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
55 SELECT * FROM information_schema.OPTIMIZER_TRACE;
56 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
57 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
58 SELECT * FROM information_schema.OPTIMIZER_TRACE;
59 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
60 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
61 SELECT * FROM information_schema.OPTIMIZER_TRACE;
62 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
63 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
64 SELECT * FROM information_schema.OPTIMIZER_TRACE;
65 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
66 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
67 SELECT * FROM information_schema.OPTIMIZER_TRACE;
68 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
69 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
70 SELECT * FROM information_schema.OPTIMIZER_TRACE;
71
72 SELECT CONCAT("hello ", f2) AS foo FROM t1 ORDER BY foo LIMIT 2;
73 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
74 SELECT * FROM information_schema.OPTIMIZER_TRACE;
75
76 --disable_result_log
77 SELECT * from t1 ORDER BY rand(2) LIMIT 2;
78 --enable_result_log
79 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
80 SELECT * FROM information_schema.OPTIMIZER_TRACE;
81
82 ################
83 ## Test sort when source data does not fit in memory
84 set sort_buffer_size= 32768;
85
86 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
87 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
88 SELECT * FROM information_schema.OPTIMIZER_TRACE;
89 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
90 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
91 SELECT * FROM information_schema.OPTIMIZER_TRACE;
92 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
93 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
94 SELECT * FROM information_schema.OPTIMIZER_TRACE;
95 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
96 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
97 SELECT * FROM information_schema.OPTIMIZER_TRACE;
98 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
99 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
100 SELECT * FROM information_schema.OPTIMIZER_TRACE;
101 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
102 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
103 SELECT * FROM information_schema.OPTIMIZER_TRACE;
104 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
105 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
106 SELECT * FROM information_schema.OPTIMIZER_TRACE;
107 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
108 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
109 SELECT * FROM information_schema.OPTIMIZER_TRACE;
110
111 ################
112 ## Test with SQL_CALC_FOUND_ROWS
113 set sort_buffer_size= 32768;
114 set optimizer_trace_limit=1;
115 set optimizer_trace_offset=-2;
116
117 SELECT SQL_CALC_FOUND_ROWS * FROM t1
118 ORDER BY f1, f0 LIMIT 30;
119 SELECT FOUND_ROWS();
120 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
121 SELECT * FROM information_schema.OPTIMIZER_TRACE;
122
123 SELECT SQL_CALC_FOUND_ROWS * FROM t1
124 ORDER BY f1, f0 LIMIT 0;
125 SELECT FOUND_ROWS();
126 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
127 SELECT * FROM information_schema.OPTIMIZER_TRACE;
128
129 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
130 ORDER BY f2, f0 LIMIT 20;
131 SELECT FOUND_ROWS();
132 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
133 SELECT * FROM information_schema.OPTIMIZER_TRACE;
134
135 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
136 ORDER BY f2, f0 LIMIT 0;
137 SELECT FOUND_ROWS();
138 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
139 SELECT * FROM information_schema.OPTIMIZER_TRACE;
140
141 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
142 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
143 SELECT FOUND_ROWS();
144 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
145 SELECT * FROM information_schema.OPTIMIZER_TRACE;
146
147 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
148 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
149 SELECT FOUND_ROWS();
150 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
151 SELECT * FROM information_schema.OPTIMIZER_TRACE;
152
153 ################
154 ## Test sorting with join
155 ## These are re-written to use PQ during execution.
156 set sort_buffer_size= 327680;
157 set optimizer_trace_limit=1;
158 set optimizer_trace_offset=-1;
159
160 CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)) charset latin1;
161 INSERT INTO tmp SELECT f1, f2 FROM t1;
162 INSERT INTO t1(f1, f2) SELECT * FROM tmp;
163 INSERT INTO tmp SELECT f1, f2 FROM t1;
164 INSERT INTO t1(f1, f2) SELECT * FROM tmp;
165
166 SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
167 ORDER BY tmp.f1, f0 LIMIT 30;
168 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
169 SELECT * FROM information_schema.OPTIMIZER_TRACE;
170
171 SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
172 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
173 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
174 SELECT * FROM information_schema.OPTIMIZER_TRACE;
175
176 set optimizer_trace_limit=2;
177 set optimizer_trace_offset=-2;
178
179 SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
180 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
181 SELECT FOUND_ROWS();
182 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
183 SELECT * FROM information_schema.OPTIMIZER_TRACE;
184
185 SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
186 WHERE t1.f2>20
187 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
188 SELECT FOUND_ROWS();
189 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
190 SELECT * FROM information_schema.OPTIMIZER_TRACE;
191
192 DROP TABLE tmp;
193
194 ################
195 ## Test views
196 set optimizer_trace_limit=1;
197 set optimizer_trace_offset=-1;
198
199 CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30;
200 SELECT * FROM v1;
201 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
202 SELECT * FROM information_schema.OPTIMIZER_TRACE;
203 drop view v1;
204
205 CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100;
206 SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
207 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
208 SELECT * FROM information_schema.OPTIMIZER_TRACE;
209
210 # Tests printing of MaterializeIterator with multiple iterators
211 # on top of the table iterator.
212 --skip_if_hypergraph # Does not support pushing LIMIT into the sorts yet.
213 EXPLAIN FORMAT=tree SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
214
215 CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100;
216 SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0
217 LIMIT 30;
218 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
219 SELECT * FROM information_schema.OPTIMIZER_TRACE;
220
221 ################
222 ## Test group & having
223 SELECT floor(f1/10) f3, count(f2) FROM t1
224 GROUP BY 1 ORDER BY 2,1 LIMIT 5;
225 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
226 SELECT * FROM information_schema.OPTIMIZER_TRACE;
227
228 SELECT floor(f1/10) f3, count(f2) FROM t1
229 GROUP BY 1 ORDER BY 2,1 LIMIT 0;
230 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
231 SELECT * FROM information_schema.OPTIMIZER_TRACE;
232
233 ################
234 ## Test SP
235 delimiter |;
236 CREATE PROCEDURE wl1393_sp_test()
237 BEGIN
238 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30;
239 SELECT * FROM information_schema.OPTIMIZER_TRACE;
240 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15;
241 SELECT * FROM information_schema.OPTIMIZER_TRACE;
242 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
243 ORDER BY f2, f0 LIMIT 15 OFFSET 15;
244 SELECT * FROM information_schema.OPTIMIZER_TRACE;
245 SELECT FOUND_ROWS();
246 SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
247 SELECT * FROM information_schema.OPTIMIZER_TRACE;
248 END|
249 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
250 CALL wl1393_sp_test()|
251 DROP PROCEDURE wl1393_sp_test|
252 delimiter ;|
253
254 ################
255 ## Test with subqueries
256 SELECT d1.f1, d1.f2 FROM t1
257 LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1
258 ORDER BY d1.f2 DESC LIMIT 30;
259 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
260 SELECT * FROM information_schema.OPTIMIZER_TRACE;
261
262 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1);
263 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
264 SELECT * FROM information_schema.OPTIMIZER_TRACE;
265
266 --error ER_SUBQUERY_NO_1_ROW
267 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2);
268 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
269 SELECT * FROM information_schema.OPTIMIZER_TRACE;
270
271 DROP TABLE t1;
272 DROP VIEW v1, v2;
273
274 --echo # end of WL#1393 - Optimizing filesort with small limit
275 --echo # end of WL#5834 - Add optimizer traces for sorting
276
277 --echo # WL#8741 Varlen keys for sorting JSON values
278 --echo # Note that JSON keys can't use the priority queue optimization for LIMIT,
279 --echo # since they can be of unbounded length.
280
281 set sort_buffer_size= 32768;
282
283 CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
284
285 INSERT INTO t(j) VALUES
286 (NULL), ('true'), ('false'),
287 ('"abc"'), ('""'), ('"abcd"'), ('"bc"'),
288 ('"abc\\u0000\\u0000"'),
289 ('"abc\\u0000"'),
290 ('0.0'), ('-0.0'), ('9223372036854776000'),
291 ('1.0e-1'), ('1.0e-2')
292 ;
293
294 SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j, id;
295 SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j, id limit 2 offset 2;
296 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
297 SELECT * FROM information_schema.OPTIMIZER_TRACE;
298
299 SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j DESC, id;
300 SELECT j, JSON_TYPE(j) AS tp FROM t ORDER BY j DESC, id limit 2 offset 2;
301 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
302 SELECT * FROM information_schema.OPTIMIZER_TRACE;
303
304 SELECT JSON_EXTRACT(j, '$') AS je, JSON_TYPE(j) AS tp FROM t ORDER BY je, id;
305 SELECT JSON_EXTRACT(j, '$') AS je, JSON_TYPE(j) AS tp FROM t ORDER BY je, id
306 limit 2 offset 2;
307 --replace_regex /("peak_memory_used":) [0-9]+/\1 "NNN"/
308 SELECT * FROM information_schema.OPTIMIZER_TRACE;
309
310 DROP TABLE t;
311
312 --echo # --end of WL#8741 Varlen keys for sorting JSON values