1create table t0(a int);
2insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3create table t1(a int);
4insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
5create table t2 (
6a int,
7b int,
8key (a)
9);
10insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B;
11#
12# Try an UPDATE that uses filesort:
13#
14explain
15update t2 set b=b+1 order by b limit 5;
16id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
171	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10000	Using filesort
18explain format=json
19update t2 set b=b+1 order by b limit 5;
20EXPLAIN
21{
22  "query_block": {
23    "select_id": 1,
24    "filesort": {
25      "table": {
26        "update": 1,
27        "table_name": "t2",
28        "access_type": "ALL",
29        "rows": 10000
30      }
31    }
32  }
33}
34analyze format=json
35update t2 set b=b+1 order by b limit 5;
36ANALYZE
37{
38  "query_block": {
39    "select_id": 1,
40    "r_total_time_ms": "REPLACED",
41    "filesort": {
42      "r_loops": 1,
43      "r_total_time_ms": "REPLACED",
44      "r_limit": 5,
45      "r_used_priority_queue": true,
46      "r_output_rows": 6,
47      "r_sort_mode": "sort_key,rowid",
48      "table": {
49        "update": 1,
50        "table_name": "t2",
51        "access_type": "ALL",
52        "rows": 10000,
53        "r_rows": 10000,
54        "r_filtered": 100,
55        "r_total_time_ms": "REPLACED"
56      }
57    }
58  }
59}
60#
61# Try an UPDATE that uses buffering:
62#
63explain
64update t2 set a=a+1 where a<10;
65id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
661	SIMPLE	t2	range	a	a	5	NULL	9	Using where; Using buffer
67explain format=json
68update t2 set a=a+1 where a<10;
69EXPLAIN
70{
71  "query_block": {
72    "select_id": 1,
73    "buffer": {
74      "table": {
75        "update": 1,
76        "table_name": "t2",
77        "access_type": "range",
78        "possible_keys": ["a"],
79        "key": "a",
80        "key_length": "5",
81        "used_key_parts": ["a"],
82        "rows": 9,
83        "attached_condition": "t2.a < 10"
84      }
85    }
86  }
87}
88analyze format=json
89update t2 set a=a+1 where a<10;
90ANALYZE
91{
92  "query_block": {
93    "select_id": 1,
94    "r_total_time_ms": "REPLACED",
95    "buffer": {
96      "table": {
97        "update": 1,
98        "table_name": "t2",
99        "access_type": "range",
100        "possible_keys": ["a"],
101        "key": "a",
102        "key_length": "5",
103        "used_key_parts": ["a"],
104        "rows": 9,
105        "r_rows": 10,
106        "r_filtered": 100,
107        "r_total_time_ms": "REPLACED",
108        "attached_condition": "t2.a < 10"
109      }
110    }
111  }
112}
113#
114# Try a DELETE that uses filesort:
115#
116explain
117delete from t2 order by b limit 5;
118id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1191	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10000	Using filesort
120explain format=json
121delete from t2 order by b limit 5;
122EXPLAIN
123{
124  "query_block": {
125    "select_id": 1,
126    "filesort": {
127      "table": {
128        "delete": 1,
129        "table_name": "t2",
130        "access_type": "ALL",
131        "rows": 10000
132      }
133    }
134  }
135}
136analyze format=json
137delete from t2 order by b limit 5;
138ANALYZE
139{
140  "query_block": {
141    "select_id": 1,
142    "r_total_time_ms": "REPLACED",
143    "filesort": {
144      "r_loops": 1,
145      "r_total_time_ms": "REPLACED",
146      "r_used_priority_queue": false,
147      "r_output_rows": 10000,
148      "r_buffer_size": "REPLACED",
149      "r_sort_mode": "sort_key,rowid",
150      "table": {
151        "delete": 1,
152        "table_name": "t2",
153        "access_type": "ALL",
154        "rows": 10000,
155        "r_rows": 10000,
156        "r_filtered": 100,
157        "r_total_time_ms": "REPLACED"
158      }
159    }
160  }
161}
162#
163# Try a SELECT with QEP in form: filesort { tmp_table { join } }
164#
165explain
166select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
167id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1681	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
1691	SIMPLE	t2	ref	a	a	5	test.t0.a	1
170explain format=json
171select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
172EXPLAIN
173{
174  "query_block": {
175    "select_id": 1,
176    "filesort": {
177      "sort_key": "t2.b",
178      "temporary_table": {
179        "table": {
180          "table_name": "t0",
181          "access_type": "ALL",
182          "rows": 10,
183          "filtered": 100,
184          "attached_condition": "t0.a is not null"
185        },
186        "table": {
187          "table_name": "t2",
188          "access_type": "ref",
189          "possible_keys": ["a"],
190          "key": "a",
191          "key_length": "5",
192          "used_key_parts": ["a"],
193          "ref": ["test.t0.a"],
194          "rows": 1,
195          "filtered": 100
196        }
197      }
198    }
199  }
200}
201analyze format=json
202select * from t0,t2 where t2.a=t0.a order by t2.b limit 4;
203ANALYZE
204{
205  "query_block": {
206    "select_id": 1,
207    "r_loops": 1,
208    "r_total_time_ms": "REPLACED",
209    "filesort": {
210      "sort_key": "t2.b",
211      "r_loops": 1,
212      "r_total_time_ms": "REPLACED",
213      "r_limit": 4,
214      "r_used_priority_queue": true,
215      "r_output_rows": 4,
216      "r_sort_mode": "sort_key,rowid",
217      "temporary_table": {
218        "table": {
219          "table_name": "t0",
220          "access_type": "ALL",
221          "r_loops": 1,
222          "rows": 10,
223          "r_rows": 10,
224          "r_table_time_ms": "REPLACED",
225          "r_other_time_ms": "REPLACED",
226          "filtered": 100,
227          "r_filtered": 100,
228          "attached_condition": "t0.a is not null"
229        },
230        "table": {
231          "table_name": "t2",
232          "access_type": "ref",
233          "possible_keys": ["a"],
234          "key": "a",
235          "key_length": "5",
236          "used_key_parts": ["a"],
237          "ref": ["test.t0.a"],
238          "r_loops": 10,
239          "rows": 1,
240          "r_rows": 0.4,
241          "r_table_time_ms": "REPLACED",
242          "r_other_time_ms": "REPLACED",
243          "filtered": 100,
244          "r_filtered": 100
245        }
246      }
247    }
248  }
249}
250#
251# Try a SELECT with QEP in form: join { filesort { table0 }, table2 }
252#
253explain
254select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
255id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
2561	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using filesort
2571	SIMPLE	t2	ref	a	a	5	test.t0.a	1
258explain format=json
259select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
260EXPLAIN
261{
262  "query_block": {
263    "select_id": 1,
264    "read_sorted_file": {
265      "filesort": {
266        "sort_key": "t0.a",
267        "table": {
268          "table_name": "t0",
269          "access_type": "ALL",
270          "rows": 10,
271          "filtered": 100,
272          "attached_condition": "t0.a is not null"
273        }
274      }
275    },
276    "table": {
277      "table_name": "t2",
278      "access_type": "ref",
279      "possible_keys": ["a"],
280      "key": "a",
281      "key_length": "5",
282      "used_key_parts": ["a"],
283      "ref": ["test.t0.a"],
284      "rows": 1,
285      "filtered": 100
286    }
287  }
288}
289analyze format=json
290select * from t0,t2 where t2.a=t0.a order by t0.a limit 4;
291ANALYZE
292{
293  "query_block": {
294    "select_id": 1,
295    "r_loops": 1,
296    "r_total_time_ms": "REPLACED",
297    "read_sorted_file": {
298      "r_rows": 10,
299      "filesort": {
300        "sort_key": "t0.a",
301        "r_loops": 1,
302        "r_total_time_ms": "REPLACED",
303        "r_used_priority_queue": false,
304        "r_output_rows": 10,
305        "r_buffer_size": "REPLACED",
306        "r_sort_mode": "sort_key,addon_fields",
307        "table": {
308          "table_name": "t0",
309          "access_type": "ALL",
310          "r_loops": 1,
311          "rows": 10,
312          "r_rows": 10,
313          "r_table_time_ms": "REPLACED",
314          "r_other_time_ms": "REPLACED",
315          "filtered": 100,
316          "r_filtered": 100,
317          "attached_condition": "t0.a is not null"
318        }
319      }
320    },
321    "table": {
322      "table_name": "t2",
323      "access_type": "ref",
324      "possible_keys": ["a"],
325      "key": "a",
326      "key_length": "5",
327      "used_key_parts": ["a"],
328      "ref": ["test.t0.a"],
329      "r_loops": 10,
330      "rows": 1,
331      "r_rows": 0.4,
332      "r_table_time_ms": "REPLACED",
333      "r_other_time_ms": "REPLACED",
334      "filtered": 100,
335      "r_filtered": 100
336    }
337  }
338}
339drop table t2;
340create table t2 (
341a int,
342b int,
343c int
344);
345insert into t2
346select
347a.a+10*b.a+100*c.a,
348b.a+10*c.a,
349c.a
350from t0 a, t0 b, t0 c;
351analyze format=json
352select MAX(b) from t2 where mod(a,2)=0 group by c;
353ANALYZE
354{
355  "query_block": {
356    "select_id": 1,
357    "r_loops": 1,
358    "r_total_time_ms": "REPLACED",
359    "filesort": {
360      "sort_key": "t2.c",
361      "r_loops": 1,
362      "r_total_time_ms": "REPLACED",
363      "r_used_priority_queue": false,
364      "r_output_rows": 10,
365      "r_buffer_size": "REPLACED",
366      "r_sort_mode": "sort_key,rowid",
367      "temporary_table": {
368        "table": {
369          "table_name": "t2",
370          "access_type": "ALL",
371          "r_loops": 1,
372          "rows": 1000,
373          "r_rows": 1000,
374          "r_table_time_ms": "REPLACED",
375          "r_other_time_ms": "REPLACED",
376          "filtered": 100,
377          "r_filtered": 50,
378          "attached_condition": "t2.a MOD 2 = 0"
379        }
380      }
381    }
382  }
383}
384drop table t2;
385#
386#  MDEV-8282: crash in filesort() with simple ordered delete
387#
388create table t3(a int) engine=innodb;
389delete from t3 order by a;
390# EXPLAIN thinks it will use delete_all_rows():
391explain
392delete from t3 order by a;
393id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
3941	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	1	Deleting all rows
395# ANALYZE shows that delete_all_rows() didn't work and we deleted rows
396# one-by-one:
397analyze
398delete from t3 order by a;
399id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
4001	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	0.00	100.00	100.00	Using filesort
401drop table t3;
402#
403# A test for duplicate_removal()
404#
405create table t3 (a int, b int);
406insert into t3 select a, 123 from t0;
407analyze format=json
408select distinct max(t3.b) Q from t0, t3 where t0.a=t3.a group by t0.a order by null;
409ANALYZE
410{
411  "query_block": {
412    "select_id": 1,
413    "r_loops": 1,
414    "r_total_time_ms": "REPLACED",
415    "duplicate_removal": {
416      "temporary_table": {
417        "table": {
418          "table_name": "t0",
419          "access_type": "ALL",
420          "r_loops": 1,
421          "rows": 10,
422          "r_rows": 10,
423          "r_table_time_ms": "REPLACED",
424          "r_other_time_ms": "REPLACED",
425          "filtered": 100,
426          "r_filtered": 100
427        },
428        "block-nl-join": {
429          "table": {
430            "table_name": "t3",
431            "access_type": "ALL",
432            "r_loops": 1,
433            "rows": 10,
434            "r_rows": 10,
435            "r_table_time_ms": "REPLACED",
436            "r_other_time_ms": "REPLACED",
437            "filtered": 100,
438            "r_filtered": 100
439          },
440          "buffer_type": "flat",
441          "buffer_size": "65",
442          "join_type": "BNL",
443          "attached_condition": "t3.a = t0.a",
444          "r_filtered": 10
445        }
446      }
447    }
448  }
449}
450#
451# A query with two filesort calls:
452#   - first is needed to do group-by-group grouping to calculate COUNT(DISTINCT)
453#   - the second is need to produce ORDER BY.
454# (see MDEV-7836 for description of the query plan)
455create table t5 (a int , b int) ;
456create table t6 like t5 ;
457create table t7 like t5 ;
458insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
459(2, -1), (3, 10);
460insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);
461insert into t7 values (3, 3), (2, 2), (1, 1);
462# TODO: This ANALYZE output doesn't make it clear what is used for what.
463analyze format=json
464select count(distinct t5.b) as sum from t5, t6
465where t5.a=t6.a and t6.b > 0 and t5.a <= 5
466group by t5.a order by sum limit 1;
467ANALYZE
468{
469  "query_block": {
470    "select_id": 1,
471    "r_loops": 1,
472    "r_total_time_ms": "REPLACED",
473    "filesort": {
474      "sort_key": "count(distinct t5.b)",
475      "r_loops": 1,
476      "r_total_time_ms": "REPLACED",
477      "r_limit": 1,
478      "r_used_priority_queue": true,
479      "r_output_rows": 2,
480      "r_sort_mode": "sort_key,rowid",
481      "temporary_table": {
482        "filesort": {
483          "sort_key": "t5.a",
484          "r_loops": 1,
485          "r_total_time_ms": "REPLACED",
486          "r_used_priority_queue": false,
487          "r_output_rows": 6,
488          "r_buffer_size": "REPLACED",
489          "r_sort_mode": "sort_key,rowid",
490          "temporary_table": {
491            "table": {
492              "table_name": "t6",
493              "access_type": "ALL",
494              "r_loops": 1,
495              "rows": 5,
496              "r_rows": 5,
497              "r_table_time_ms": "REPLACED",
498              "r_other_time_ms": "REPLACED",
499              "filtered": 100,
500              "r_filtered": 80,
501              "attached_condition": "t6.b > 0 and t6.a <= 5"
502            },
503            "block-nl-join": {
504              "table": {
505                "table_name": "t5",
506                "access_type": "ALL",
507                "r_loops": 1,
508                "rows": 7,
509                "r_rows": 7,
510                "r_table_time_ms": "REPLACED",
511                "r_other_time_ms": "REPLACED",
512                "filtered": 100,
513                "r_filtered": 100
514              },
515              "buffer_type": "flat",
516              "buffer_size": "119",
517              "join_type": "BNL",
518              "attached_condition": "t5.a = t6.a",
519              "r_filtered": 21.42857143
520            }
521          }
522        }
523      }
524    }
525  }
526}
527explain format=json
528select count(distinct t5.b) as sum from t5, t6
529where t5.a=t6.a and t6.b > 0 and t5.a <= 5
530group by t5.a order by sum limit 1;
531EXPLAIN
532{
533  "query_block": {
534    "select_id": 1,
535    "filesort": {
536      "sort_key": "count(distinct t5.b)",
537      "temporary_table": {
538        "filesort": {
539          "sort_key": "t5.a",
540          "temporary_table": {
541            "table": {
542              "table_name": "t6",
543              "access_type": "ALL",
544              "rows": 5,
545              "filtered": 100,
546              "attached_condition": "t6.b > 0 and t6.a <= 5"
547            },
548            "block-nl-join": {
549              "table": {
550                "table_name": "t5",
551                "access_type": "ALL",
552                "rows": 7,
553                "filtered": 100
554              },
555              "buffer_type": "flat",
556              "buffer_size": "119",
557              "join_type": "BNL",
558              "attached_condition": "t5.a = t6.a"
559            }
560          }
561        }
562      }
563    }
564  }
565}
566drop table t5,t6,t7;
567drop table t3;
568#
569# Tabular ANALYZE must get its data from execution tracker (and not from
570#   the query plan)
571#
572CREATE TABLE t2(
573col1 int,
574col2 int,
575UNIQUE INDEX idx (col1, col2)) engine=myisam;
576INSERT INTO t2(col1, col2) VALUES
577(1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11),
578(11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1);
579flush status;
580explain
581select col1 f1, col2 f2, col1 f3 from t2 group by f1;
582id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
5831	SIMPLE	t2	range	NULL	idx	5	NULL	7	Using index for group-by
584analyze
585select col1 f1, col2 f2, col1 f3 from t2 group by f1;
586id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
5871	SIMPLE	t2	range	NULL	idx	5	NULL	7	20.00	100.00	100.00	Using index for group-by
588analyze format=json
589select col1 f1, col2 f2, col1 f3 from t2 group by f1;
590ANALYZE
591{
592  "query_block": {
593    "select_id": 1,
594    "r_loops": 1,
595    "r_total_time_ms": "REPLACED",
596    "table": {
597      "table_name": "t2",
598      "access_type": "range",
599      "key": "idx",
600      "key_length": "5",
601      "used_key_parts": ["col1"],
602      "r_loops": 1,
603      "rows": 7,
604      "r_rows": 20,
605      "r_table_time_ms": "REPLACED",
606      "r_other_time_ms": "REPLACED",
607      "filtered": 100,
608      "r_filtered": 100,
609      "using_index_for_group_by": true
610    }
611  }
612}
613drop table t2;
614drop table t0,t1;
615