Debian Codegraph @

mysql-test/suite/opt_trace/r/temp_table.result

raw
1 SET optimizer_trace_max_mem_size=1048576;
2 SET optimizer_trace="enabled=on,one_line=off";
3 SET end_markers_in_json="on";
4 CREATE TABLE tmp (
5 pk INT PRIMARY KEY AUTO_INCREMENT,
6 col1 CHAR (1)
7 );
8 INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
9 CREATE TABLE t1 (
10 uniq VARCHAR(10),
11 col1 VARCHAR(10),
12 col2 VARCHAR(1024)
13 ) CHARSET utf8mb4;
14 INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
15 SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
16 uniq col1
17 1 a
18 2 b
19 3 c
20 SELECT * FROM information_schema.OPTIMIZER_TRACE;
21 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
22 SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3 {
23 "steps": [
24 {
25 "join_preparation": {
26 "select#": 1,
27 "steps": [
28 {
29 "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1` from `t1` group by `t1`.`col2`,`t1`.`uniq` limit 3"
30 }
31 ] /* steps */
32 } /* join_preparation */
33 },
34 {
35 "join_optimization": {
36 "select#": 1,
37 "steps": [
38 {
39 "substitute_generated_columns": {
40 } /* substitute_generated_columns */
41 },
42 {
43 "table_dependencies": [
44 {
45 "table": "`t1`",
46 "row_may_be_null": false,
47 "map_bit": 0,
48 "depends_on_map_bits": [
49 ] /* depends_on_map_bits */
50 }
51 ] /* table_dependencies */
52 },
53 {
54 "rows_estimation": [
55 {
56 "table": "`t1`",
57 "table_scan": {
58 "rows": 8,
59 "cost": 0.25
60 } /* table_scan */
61 }
62 ] /* rows_estimation */
63 },
64 {
65 "considered_execution_plans": [
66 {
67 "plan_prefix": [
68 ] /* plan_prefix */,
69 "table": "`t1`",
70 "best_access_path": {
71 "considered_access_paths": [
72 {
73 "rows_to_scan": 8,
74 "access_type": "scan",
75 "resulting_rows": 8,
76 "cost": 1.05,
77 "chosen": true
78 }
79 ] /* considered_access_paths */
80 } /* best_access_path */,
81 "condition_filtering_pct": 100,
82 "rows_for_plan": 8,
83 "cost_for_plan": 1.05,
84 "chosen": true
85 }
86 ] /* considered_execution_plans */
87 },
88 {
89 "attaching_conditions_to_tables": {
90 "original_condition": null,
91 "attached_conditions_computation": [
92 ] /* attached_conditions_computation */,
93 "attached_conditions_summary": [
94 {
95 "table": "`t1`",
96 "attached": null
97 }
98 ] /* attached_conditions_summary */
99 } /* attaching_conditions_to_tables */
100 },
101 {
102 "optimizing_distinct_group_by_order_by": {
103 "simplifying_group_by": {
104 "original_clause": "`t1`.`col2`,`t1`.`uniq`",
105 "items": [
106 {
107 "item": "`t1`.`col2`"
108 },
109 {
110 "item": "`t1`.`uniq`"
111 }
112 ] /* items */,
113 "resulting_clause_is_simple": true,
114 "resulting_clause": "`t1`.`col2`,`t1`.`uniq`"
115 } /* simplifying_group_by */
116 } /* optimizing_distinct_group_by_order_by */
117 },
118 {
119 "finalizing_table_conditions": [
120 ] /* finalizing_table_conditions */
121 },
122 {
123 "refine_plan": [
124 {
125 "table": "`t1`"
126 }
127 ] /* refine_plan */
128 },
129 {
130 "considering_tmp_tables": [
131 {
132 "adding_tmp_table_in_plan_at_position": 1,
133 "write_method": "write_all_rows"
134 }
135 ] /* considering_tmp_tables */
136 }
137 ] /* steps */
138 } /* join_optimization */
139 },
140 {
141 "join_execution": {
142 "select#": 1,
143 "steps": [
144 {
145 "creating_tmp_table": {
146 "tmp_table_info": {
147 "table": "<temporary>",
148 "in_plan_at_position": 1,
149 "columns": 4,
150 "row_length": 4190,
151 "key_length": 8,
152 "unique_constraint": true,
153 "makes_grouped_rows": true,
154 "cannot_insert_duplicates": false,
155 "location": "TempTable"
156 } /* tmp_table_info */
157 } /* creating_tmp_table */
158 },
159 {
160 "materialize": {
161 "select#": 1,
162 "steps": [
163 ] /* steps */
164 } /* materialize */
165 }
166 ] /* steps */
167 } /* join_execution */
168 }
169 ] /* steps */
170 } 0 0
171 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
172 uniq col1 col2
173 1 a a
174 2 b b
175 3 c c
176 4 d d
177 5 e e
178 6 f f
179 7 g g
180 8 h h
181 SELECT * FROM information_schema.OPTIMIZER_TRACE;
182 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
183 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
184 "steps": [
185 {
186 "join_preparation": {
187 "select#": 1,
188 "steps": [
189 {
190 "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`"
191 }
192 ] /* steps */
193 } /* join_preparation */
194 },
195 {
196 "join_optimization": {
197 "select#": 1,
198 "steps": [
199 {
200 "substitute_generated_columns": {
201 } /* substitute_generated_columns */
202 },
203 {
204 "table_dependencies": [
205 {
206 "table": "`t1`",
207 "row_may_be_null": false,
208 "map_bit": 0,
209 "depends_on_map_bits": [
210 ] /* depends_on_map_bits */
211 }
212 ] /* table_dependencies */
213 },
214 {
215 "rows_estimation": [
216 {
217 "table": "`t1`",
218 "table_scan": {
219 "rows": 8,
220 "cost": 0.25
221 } /* table_scan */
222 }
223 ] /* rows_estimation */
224 },
225 {
226 "considered_execution_plans": [
227 {
228 "plan_prefix": [
229 ] /* plan_prefix */,
230 "table": "`t1`",
231 "best_access_path": {
232 "considered_access_paths": [
233 {
234 "rows_to_scan": 8,
235 "access_type": "scan",
236 "resulting_rows": 8,
237 "cost": 1.05,
238 "chosen": true,
239 "use_tmp_table": true
240 }
241 ] /* considered_access_paths */
242 } /* best_access_path */,
243 "condition_filtering_pct": 100,
244 "rows_for_plan": 8,
245 "cost_for_plan": 1.05,
246 "sort_cost": 8,
247 "new_cost_for_plan": 9.05,
248 "chosen": true
249 }
250 ] /* considered_execution_plans */
251 },
252 {
253 "attaching_conditions_to_tables": {
254 "original_condition": null,
255 "attached_conditions_computation": [
256 ] /* attached_conditions_computation */,
257 "attached_conditions_summary": [
258 {
259 "table": "`t1`",
260 "attached": null
261 }
262 ] /* attached_conditions_summary */
263 } /* attaching_conditions_to_tables */
264 },
265 {
266 "optimizing_distinct_group_by_order_by": {
267 "simplifying_group_by": {
268 "original_clause": "`t1`.`uniq`",
269 "items": [
270 {
271 "item": "`t1`.`uniq`"
272 }
273 ] /* items */,
274 "resulting_clause_is_simple": true,
275 "resulting_clause": "`t1`.`uniq`"
276 } /* simplifying_group_by */
277 } /* optimizing_distinct_group_by_order_by */
278 },
279 {
280 "finalizing_table_conditions": [
281 ] /* finalizing_table_conditions */
282 },
283 {
284 "refine_plan": [
285 {
286 "table": "`t1`"
287 }
288 ] /* refine_plan */
289 },
290 {
291 "considering_tmp_tables": [
292 {
293 "adding_tmp_table_in_plan_at_position": 1,
294 "write_method": "write_all_rows"
295 }
296 ] /* considering_tmp_tables */
297 }
298 ] /* steps */
299 } /* join_optimization */
300 },
301 {
302 "join_execution": {
303 "select#": 1,
304 "steps": [
305 {
306 "creating_tmp_table": {
307 "tmp_table_info": {
308 "table": "<temporary>",
309 "in_plan_at_position": 1,
310 "columns": 3,
311 "row_length": 4182,
312 "key_length": 43,
313 "unique_constraint": false,
314 "makes_grouped_rows": true,
315 "cannot_insert_duplicates": false,
316 "location": "TempTable"
317 } /* tmp_table_info */
318 } /* creating_tmp_table */
319 },
320 {
321 "materialize": {
322 "select#": 1,
323 "steps": [
324 ] /* steps */
325 } /* materialize */
326 }
327 ] /* steps */
328 } /* join_execution */
329 }
330 ] /* steps */
331 } 0 0
332 select @@tmp_table_size;
333 @@tmp_table_size
334 16777216
335 SET @old_size= @@tmp_table_size;
336 SET SESSION tmp_table_size= 1024;
337 SET SESSION internal_tmp_mem_storage_engine='memory';
338 INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
339 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
340 uniq col1 col2
341 1 a a
342 10 b b
343 11 c c
344 12 d d
345 13 e e
346 14 f f
347 15 g g
348 16 h h
349 2 b b
350 3 c c
351 4 d d
352 5 e e
353 6 f f
354 7 g g
355 8 h h
356 9 a a
357 SELECT * FROM information_schema.OPTIMIZER_TRACE;
358 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
359 SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
360 "steps": [
361 {
362 "join_preparation": {
363 "select#": 1,
364 "steps": [
365 {
366 "expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`"
367 }
368 ] /* steps */
369 } /* join_preparation */
370 },
371 {
372 "join_optimization": {
373 "select#": 1,
374 "steps": [
375 {
376 "substitute_generated_columns": {
377 } /* substitute_generated_columns */
378 },
379 {
380 "table_dependencies": [
381 {
382 "table": "`t1`",
383 "row_may_be_null": false,
384 "map_bit": 0,
385 "depends_on_map_bits": [
386 ] /* depends_on_map_bits */
387 }
388 ] /* table_dependencies */
389 },
390 {
391 "rows_estimation": [
392 {
393 "table": "`t1`",
394 "table_scan": {
395 "rows": 16,
396 "cost": 0.25
397 } /* table_scan */
398 }
399 ] /* rows_estimation */
400 },
401 {
402 "considered_execution_plans": [
403 {
404 "plan_prefix": [
405 ] /* plan_prefix */,
406 "table": "`t1`",
407 "best_access_path": {
408 "considered_access_paths": [
409 {
410 "rows_to_scan": 16,
411 "access_type": "scan",
412 "resulting_rows": 16,
413 "cost": 1.85,
414 "chosen": true,
415 "use_tmp_table": true
416 }
417 ] /* considered_access_paths */
418 } /* best_access_path */,
419 "condition_filtering_pct": 100,
420 "rows_for_plan": 16,
421 "cost_for_plan": 1.85,
422 "sort_cost": 16,
423 "new_cost_for_plan": 17.85,
424 "chosen": true
425 }
426 ] /* considered_execution_plans */
427 },
428 {
429 "attaching_conditions_to_tables": {
430 "original_condition": null,
431 "attached_conditions_computation": [
432 ] /* attached_conditions_computation */,
433 "attached_conditions_summary": [
434 {
435 "table": "`t1`",
436 "attached": null
437 }
438 ] /* attached_conditions_summary */
439 } /* attaching_conditions_to_tables */
440 },
441 {
442 "optimizing_distinct_group_by_order_by": {
443 "simplifying_group_by": {
444 "original_clause": "`t1`.`uniq`",
445 "items": [
446 {
447 "item": "`t1`.`uniq`"
448 }
449 ] /* items */,
450 "resulting_clause_is_simple": true,
451 "resulting_clause": "`t1`.`uniq`"
452 } /* simplifying_group_by */
453 } /* optimizing_distinct_group_by_order_by */
454 },
455 {
456 "finalizing_table_conditions": [
457 ] /* finalizing_table_conditions */
458 },
459 {
460 "refine_plan": [
461 {
462 "table": "`t1`"
463 }
464 ] /* refine_plan */
465 },
466 {
467 "considering_tmp_tables": [
468 {
469 "adding_tmp_table_in_plan_at_position": 1,
470 "write_method": "write_all_rows"
471 }
472 ] /* considering_tmp_tables */
473 }
474 ] /* steps */
475 } /* join_optimization */
476 },
477 {
478 "join_execution": {
479 "select#": 1,
480 "steps": [
481 {
482 "creating_tmp_table": {
483 "tmp_table_info": {
484 "table": "<temporary>",
485 "in_plan_at_position": 1,
486 "columns": 3,
487 "row_length": 4182,
488 "key_length": 43,
489 "unique_constraint": false,
490 "makes_grouped_rows": true,
491 "cannot_insert_duplicates": false,
492 "location": "memory (heap)",
493 "row_limit_estimate": 1
494 } /* tmp_table_info */
495 } /* creating_tmp_table */
496 },
497 {
498 "materialize": {
499 "select#": 1,
500 "steps": [
501 {
502 "converting_tmp_table_to_ondisk": {
503 "cause": "memory_table_size_exceeded",
504 "tmp_table_info": {
505 "table": "<temporary>",
506 "in_plan_at_position": 1,
507 "columns": 3,
508 "row_length": 4182,
509 "key_length": 43,
510 "unique_constraint": false,
511 "makes_grouped_rows": true,
512 "cannot_insert_duplicates": false,
513 "location": "disk (TMP_TABLE_ENGINE)",
514 "record_format": "packed"
515 } /* tmp_table_info */
516 } /* converting_tmp_table_to_ondisk */
517 }
518 ] /* steps */
519 } /* materialize */
520 }
521 ] /* steps */
522 } /* join_execution */
523 }
524 ] /* steps */
525 } 0 0
526 SET SESSION internal_tmp_mem_storage_engine=default;
527 SET GLOBAL tmp_table_size= @old_size;
528 SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1;
529 pool_id
530 0
531 SELECT * FROM information_schema.OPTIMIZER_TRACE;
532 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
533 SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1 {
534 "steps": [
535 {
536 "creating_tmp_table": {
537 "tmp_table_info": {
538 "table": "INNODB_BUFFER_PAGE",
539 "columns": 21,
540 "row_length": 6863,
541 "key_length": 0,
542 "unique_constraint": false,
543 "makes_grouped_rows": false,
544 "cannot_insert_duplicates": false,
545 "location": "memory (heap)",
546 "row_limit_estimate": 1
547 } /* tmp_table_info */
548 } /* creating_tmp_table */
549 },
550 {
551 "join_preparation": {
552 "select#": 1,
553 "steps": [
554 {
555 "expanded_query": "/* select#1 */ select `information_schema`.`INNODB_BUFFER_PAGE`.`POOL_ID` AS `pool_id` from `information_schema`.`INNODB_BUFFER_PAGE` limit 1"
556 }
557 ] /* steps */
558 } /* join_preparation */
559 },
560 {
561 "join_optimization": {
562 "select#": 1,
563 "steps": [
564 {
565 "table_dependencies": [
566 {
567 "table": "`information_schema`.`INNODB_BUFFER_PAGE`",
568 "row_may_be_null": false,
569 "map_bit": 0,
570 "depends_on_map_bits": [
571 ] /* depends_on_map_bits */
572 }
573 ] /* table_dependencies */
574 },
575 {
576 "rows_estimation": [
577 {
578 "table": "`information_schema`.`INNODB_BUFFER_PAGE`",
579 "table_scan": {
580 "rows": 2,
581 "cost": 2.525
582 } /* table_scan */
583 }
584 ] /* rows_estimation */
585 },
586 {
587 "considered_execution_plans": [
588 {
589 "plan_prefix": [
590 ] /* plan_prefix */,
591 "table": "`information_schema`.`INNODB_BUFFER_PAGE`",
592 "best_access_path": {
593 "considered_access_paths": [
594 {
595 "rows_to_scan": 2,
596 "access_type": "scan",
597 "resulting_rows": 2,
598 "cost": 2.725,
599 "chosen": true
600 }
601 ] /* considered_access_paths */
602 } /* best_access_path */,
603 "condition_filtering_pct": 100,
604 "rows_for_plan": 2,
605 "cost_for_plan": 2.725,
606 "chosen": true
607 }
608 ] /* considered_execution_plans */
609 },
610 {
611 "attaching_conditions_to_tables": {
612 "original_condition": null,
613 "attached_conditions_computation": [
614 ] /* attached_conditions_computation */,
615 "attached_conditions_summary": [
616 {
617 "table": "`information_schema`.`INNODB_BUFFER_PAGE`",
618 "attached": null
619 }
620 ] /* attached_conditions_summary */
621 } /* attaching_conditions_to_tables */
622 },
623 {
624 "finalizing_table_conditions": [
625 ] /* finalizing_table_conditions */
626 },
627 {
628 "refine_plan": [
629 {
630 "table": "`information_schema`.`INNODB_BUFFER_PAGE`"
631 }
632 ] /* refine_plan */
633 }
634 ] /* steps */
635 } /* join_optimization */
636 },
637 {
638 "join_execution": {
639 "select#": 1,
640 "steps": [
641 {
642 "converting_tmp_table_to_ondisk": {
643 "cause": "memory_table_size_exceeded",
644 "tmp_table_info": {
645 "table": "`information_schema`.`INNODB_BUFFER_PAGE`",
646 "in_plan_at_position": 0,
647 "columns": 21,
648 "row_length": 6863,
649 "key_length": 0,
650 "unique_constraint": false,
651 "makes_grouped_rows": false,
652 "cannot_insert_duplicates": false,
653 "location": "disk (TMP_TABLE_ENGINE)",
654 "record_format": "packed"
655 } /* tmp_table_info */
656 } /* converting_tmp_table_to_ondisk */
657 }
658 ] /* steps */
659 } /* join_execution */
660 }
661 ] /* steps */
662 } 0 0
663 DROP TABLE t1, tmp;
664 #
665 # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES
666 # ON DISK
667 #
668 CREATE TABLE t1 (
669 c1 INT AUTO_INCREMENT PRIMARY KEY,
670 c2 VARCHAR(250)
671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
672 Warnings:
673 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
674 INSERT INTO t1(c2) VALUES ('b'),('b');
675 INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
676 SET @@max_heap_table_size=1;
677 Warnings:
678 Warning 1292 Truncated incorrect max_heap_table_size value: '1'
679 SET @@group_concat_max_len= 500;
680 SET SESSION internal_tmp_mem_storage_engine='memory';
681 SELECT c1,GROUP_CONCAT(c2) cc FROM
682 (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
683 GROUP BY c1
684 ORDER BY cc;
685 c1 cc
686 1 a,b
687 10 a,b
688 11 a,b
689 12 a,b
690 13 a,b
691 14 a,b
692 15 a,b
693 16 a,b
694 17 a,b
695 18 a,b
696 19 a,b
697 2 a,b
698 20 a,b
699 21 a,b
700 22 a,b
701 23 a,b
702 24 a,b
703 25 a,b
704 26 a,b
705 27 a,b
706 28 a,b
707 29 a,b
708 3 a,b
709 30 a,b
710 31 a,b
711 32 a,b
712 33 a,b
713 34 a,b
714 35 a,b
715 36 a,b
716 37 a,b
717 38 a,b
718 39 a,b
719 4 a,b
720 40 a,b
721 41 a,b
722 42 a,b
723 43 a,b
724 44 a,b
725 45 a,b
726 46 a,b
727 47 a,b
728 48 a,b
729 49 a,b
730 5 a,b
731 50 a,b
732 51 a,b
733 52 a,b
734 53 a,b
735 54 a,b
736 55 a,b
737 56 a,b
738 57 a,b
739 58 a,b
740 59 a,b
741 6 a,b
742 60 a,b
743 61 a,b
744 62 a,b
745 63 a,b
746 64 a,b
747 65 a,b
748 66 a,b
749 7 a,b
750 8 a,b
751 9 a,b
752 SELECT * FROM information_schema.OPTIMIZER_TRACE;
753 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
754 SELECT c1,GROUP_CONCAT(c2) cc FROM
755 (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
756 GROUP BY c1
757 ORDER BY cc {
758 "steps": [
759 {
760 "join_preparation": {
761 "select#": 1,
762 "steps": [
763 {
764 "join_preparation": {
765 "select#": 2,
766 "steps": [
767 {
768 "expanded_query": "/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1`"
769 }
770 ] /* steps */
771 } /* join_preparation */
772 },
773 {
774 "join_preparation": {
775 "select#": 3,
776 "steps": [
777 {
778 "expanded_query": "/* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`"
779 }
780 ] /* steps */
781 } /* join_preparation */
782 },
783 {
784 "creating_tmp_table": {
785 "tmp_table_info": {
786 "table": "<union temporary>",
787 "columns": 2,
788 "row_length": 757,
789 "key_length": 757,
790 "unique_constraint": false,
791 "makes_grouped_rows": false,
792 "cannot_insert_duplicates": true,
793 "location": "memory (heap)",
794 "row_limit_estimate": 1
795 } /* tmp_table_info */
796 } /* creating_tmp_table */
797 },
798 {
799 "join_preparation": {
800 "select#": 4,
801 "steps": [
802 {
803 "expanded_query": "/* select#4 */ select `c1` AS `c1`,`c2` AS `c2` from dual"
804 }
805 ] /* steps */
806 } /* join_preparation */
807 },
808 {
809 "derived": {
810 "table": " `tt`",
811 "select#": 2,
812 "materialized": true
813 } /* derived */
814 },
815 {
816 "expanded_query": "/* select#1 */ select `tt`.`c1` AS `c1`,group_concat(`tt`.`c2` separator ',') AS `cc` from (/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` union /* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`) `tt` group by `tt`.`c1` order by `cc`"
817 }
818 ] /* steps */
819 } /* join_preparation */
820 },
821 {
822 "join_optimization": {
823 "select#": 1,
824 "steps": [
825 {
826 "join_optimization": {
827 "select#": 2,
828 "steps": [
829 {
830 "table_dependencies": [
831 {
832 "table": "`t1`",
833 "row_may_be_null": false,
834 "map_bit": 0,
835 "depends_on_map_bits": [
836 ] /* depends_on_map_bits */
837 }
838 ] /* table_dependencies */
839 },
840 {
841 "rows_estimation": [
842 {
843 "table": "`t1`",
844 "table_scan": {
845 "rows": 66,
846 "cost": 0.25
847 } /* table_scan */
848 }
849 ] /* rows_estimation */
850 },
851 {
852 "considered_execution_plans": [
853 {
854 "plan_prefix": [
855 ] /* plan_prefix */,
856 "table": "`t1`",
857 "best_access_path": {
858 "considered_access_paths": [
859 {
860 "rows_to_scan": 66,
861 "access_type": "scan",
862 "resulting_rows": 66,
863 "cost": 6.85,
864 "chosen": true
865 }
866 ] /* considered_access_paths */
867 } /* best_access_path */,
868 "condition_filtering_pct": 100,
869 "rows_for_plan": 66,
870 "cost_for_plan": 6.85,
871 "chosen": true
872 }
873 ] /* considered_execution_plans */
874 },
875 {
876 "attaching_conditions_to_tables": {
877 "original_condition": null,
878 "attached_conditions_computation": [
879 ] /* attached_conditions_computation */,
880 "attached_conditions_summary": [
881 {
882 "table": "`t1`",
883 "attached": null
884 }
885 ] /* attached_conditions_summary */
886 } /* attaching_conditions_to_tables */
887 },
888 {
889 "finalizing_table_conditions": [
890 ] /* finalizing_table_conditions */
891 },
892 {
893 "refine_plan": [
894 {
895 "table": "`t1`"
896 }
897 ] /* refine_plan */
898 }
899 ] /* steps */
900 } /* join_optimization */
901 },
902 {
903 "join_optimization": {
904 "select#": 3,
905 "steps": [
906 {
907 "table_dependencies": [
908 {
909 "table": "`t1`",
910 "row_may_be_null": false,
911 "map_bit": 0,
912 "depends_on_map_bits": [
913 ] /* depends_on_map_bits */
914 }
915 ] /* table_dependencies */
916 },
917 {
918 "rows_estimation": [
919 {
920 "table": "`t1`",
921 "table_scan": {
922 "rows": 66,
923 "cost": 0.25
924 } /* table_scan */
925 }
926 ] /* rows_estimation */
927 },
928 {
929 "considered_execution_plans": [
930 {
931 "plan_prefix": [
932 ] /* plan_prefix */,
933 "table": "`t1`",
934 "best_access_path": {
935 "considered_access_paths": [
936 {
937 "rows_to_scan": 66,
938 "access_type": "scan",
939 "resulting_rows": 66,
940 "cost": 6.85,
941 "chosen": true
942 }
943 ] /* considered_access_paths */
944 } /* best_access_path */,
945 "condition_filtering_pct": 100,
946 "rows_for_plan": 66,
947 "cost_for_plan": 6.85,
948 "chosen": true
949 }
950 ] /* considered_execution_plans */
951 },
952 {
953 "attaching_conditions_to_tables": {
954 "original_condition": null,
955 "attached_conditions_computation": [
956 ] /* attached_conditions_computation */,
957 "attached_conditions_summary": [
958 {
959 "table": "`t1`",
960 "attached": null
961 }
962 ] /* attached_conditions_summary */
963 } /* attaching_conditions_to_tables */
964 },
965 {
966 "finalizing_table_conditions": [
967 ] /* finalizing_table_conditions */
968 },
969 {
970 "refine_plan": [
971 {
972 "table": "`t1`"
973 }
974 ] /* refine_plan */
975 }
976 ] /* steps */
977 } /* join_optimization */
978 },
979 {
980 "join_optimization": {
981 "select#": 4,
982 "steps": [
983 {
984 "table_dependencies": [
985 {
986 "table": "``",
987 "row_may_be_null": false,
988 "map_bit": 0,
989 "depends_on_map_bits": [
990 ] /* depends_on_map_bits */
991 }
992 ] /* table_dependencies */
993 },
994 {
995 "rows_estimation": [
996 {
997 "table": "``",
998 "table_scan": {
999 "rows": 0,
1000 "cost": 2.5
1001 } /* table_scan */
1002 }
1003 ] /* rows_estimation */
1004 },
1005 {
1006 "considered_execution_plans": [
1007 {
1008 "plan_prefix": [
1009 ] /* plan_prefix */,
1010 "table": "``",
1011 "best_access_path": {
1012 "considered_access_paths": [
1013 {
1014 "rows_to_scan": 0,
1015 "access_type": "scan",
1016 "resulting_rows": 0,
1017 "cost": 2.5,
1018 "chosen": true
1019 }
1020 ] /* considered_access_paths */
1021 } /* best_access_path */,
1022 "condition_filtering_pct": 100,
1023 "rows_for_plan": 0,
1024 "cost_for_plan": 2.5,
1025 "chosen": true
1026 }
1027 ] /* considered_execution_plans */
1028 },
1029 {
1030 "attaching_conditions_to_tables": {
1031 "original_condition": null,
1032 "attached_conditions_computation": [
1033 ] /* attached_conditions_computation */,
1034 "attached_conditions_summary": [
1035 {
1036 "table": "``",
1037 "attached": null
1038 }
1039 ] /* attached_conditions_summary */
1040 } /* attaching_conditions_to_tables */
1041 },
1042 {
1043 "finalizing_table_conditions": [
1044 ] /* finalizing_table_conditions */
1045 },
1046 {
1047 "refine_plan": [
1048 {
1049 "table": "``"
1050 }
1051 ] /* refine_plan */
1052 }
1053 ] /* steps */
1054 } /* join_optimization */
1055 },
1056 {
1057 "substitute_generated_columns": {
1058 } /* substitute_generated_columns */
1059 },
1060 {
1061 "table_dependencies": [
1062 {
1063 "table": " `tt`",
1064 "row_may_be_null": false,
1065 "map_bit": 0,
1066 "depends_on_map_bits": [
1067 ] /* depends_on_map_bits */
1068 }
1069 ] /* table_dependencies */
1070 },
1071 {
1072 "rows_estimation": [
1073 {
1074 "table": " `tt`",
1075 "table_scan": {
1076 "rows": 132,
1077 "cost": 4.15
1078 } /* table_scan */
1079 }
1080 ] /* rows_estimation */
1081 },
1082 {
1083 "considered_execution_plans": [
1084 {
1085 "plan_prefix": [
1086 ] /* plan_prefix */,
1087 "table": " `tt`",
1088 "best_access_path": {
1089 "considered_access_paths": [
1090 {
1091 "rows_to_scan": 132,
1092 "access_type": "scan",
1093 "resulting_rows": 132,
1094 "cost": 17.35,
1095 "chosen": true
1096 }
1097 ] /* considered_access_paths */
1098 } /* best_access_path */,
1099 "condition_filtering_pct": 100,
1100 "rows_for_plan": 132,
1101 "cost_for_plan": 17.35,
1102 "chosen": true
1103 }
1104 ] /* considered_execution_plans */
1105 },
1106 {
1107 "attaching_conditions_to_tables": {
1108 "original_condition": null,
1109 "attached_conditions_computation": [
1110 ] /* attached_conditions_computation */,
1111 "attached_conditions_summary": [
1112 {
1113 "table": " `tt`",
1114 "attached": null
1115 }
1116 ] /* attached_conditions_summary */
1117 } /* attaching_conditions_to_tables */
1118 },
1119 {
1120 "optimizing_distinct_group_by_order_by": {
1121 "simplifying_order_by": {
1122 "original_clause": "`cc`",
1123 "items": [
1124 {
1125 "item": "group_concat(`tt`.`c2` separator ',')"
1126 }
1127 ] /* items */,
1128 "resulting_clause_is_simple": false,
1129 "resulting_clause": "`cc`"
1130 } /* simplifying_order_by */,
1131 "simplifying_group_by": {
1132 "original_clause": "`tt`.`c1`",
1133 "items": [
1134 {
1135 "item": "`tt`.`c1`"
1136 }
1137 ] /* items */,
1138 "resulting_clause_is_simple": true,
1139 "resulting_clause": "`tt`.`c1`"
1140 } /* simplifying_group_by */
1141 } /* optimizing_distinct_group_by_order_by */
1142 },
1143 {
1144 "finalizing_table_conditions": [
1145 ] /* finalizing_table_conditions */
1146 },
1147 {
1148 "refine_plan": [
1149 {
1150 "table": " `tt`"
1151 }
1152 ] /* refine_plan */
1153 },
1154 {
1155 "considering_tmp_tables": [
1156 {
1157 "adding_tmp_table_in_plan_at_position": 1,
1158 "filesort": {
1159 "adding_sort_to_table": "tt"
1160 } /* filesort */,
1161 "unknown_key_1": {
1162 "creating_tmp_table": {
1163 "tmp_table_info": {
1164 "table": "intermediate_tmp_table",
1165 "columns": 1,
1166 "row_length": 753,
1167 "key_length": 0,
1168 "unique_constraint": false,
1169 "makes_grouped_rows": false,
1170 "cannot_insert_duplicates": false,
1171 "location": "memory (heap)",
1172 "row_limit_estimate": 1
1173 } /* tmp_table_info */
1174 } /* creating_tmp_table */
1175 },
1176 "write_method": "write_group_row_when_complete"
1177 },
1178 {
1179 "adding_sort_to_table": ""
1180 } /* filesort */
1181 ] /* considering_tmp_tables */
1182 }
1183 ] /* steps */
1184 } /* join_optimization */
1185 },
1186 {
1187 "join_execution": {
1188 "select#": 1,
1189 "steps": [
1190 {
1191 "creating_tmp_table": {
1192 "tmp_table_info": {
1193 "table": "tt",
1194 "in_plan_at_position": 0,
1195 "columns": 2,
1196 "row_length": 757,
1197 "key_length": 757,
1198 "unique_constraint": false,
1199 "makes_grouped_rows": false,
1200 "cannot_insert_duplicates": true,
1201 "location": "memory (heap)",
1202 "row_limit_estimate": 1
1203 } /* tmp_table_info */
1204 } /* creating_tmp_table */
1205 },
1206 {
1207 "materialize": {
1208 "select#": 2,
1209 "steps": [
1210 {
1211 "converting_tmp_table_to_ondisk": {
1212 "cause": "memory_table_size_exceeded",
1213 "tmp_table_info": {
1214 "table": "tt",
1215 "in_plan_at_position": 0,
1216 "columns": 2,
1217 "row_length": 757,
1218 "key_length": 757,
1219 "unique_constraint": false,
1220 "makes_grouped_rows": false,
1221 "cannot_insert_duplicates": true,
1222 "location": "disk (TMP_TABLE_ENGINE)",
1223 "record_format": "packed"
1224 } /* tmp_table_info */
1225 } /* converting_tmp_table_to_ondisk */
1226 }
1227 ] /* steps */
1228 } /* materialize */
1229 },
1230 {
1231 "materialize": {
1232 "select#": 3,
1233 "steps": [
1234 ] /* steps */
1235 } /* materialize */
1236 },
1237 {
1238 "sorting_table": "tt",
1239 "filesort_information": [
1240 {
1241 "direction": "asc",
1242 "expression": "`tt`.`c1`"
1243 }
1244 ] /* filesort_information */,
1245 "filesort_priority_queue_optimization": {
1246 "usable": false,
1247 "cause": "not applicable (no LIMIT)"
1248 } /* filesort_priority_queue_optimization */,
1249 "filesort_execution": [
1250 ] /* filesort_execution */,
1251 "filesort_summary": {
1252 "memory_available": 262144,
1253 "key_size": 8,
1254 "row_size": 769,
1255 "max_rows_per_buffer": 132,
1256 "num_rows_estimate": 132,
1257 "num_rows_found": 132,
1258 "num_initial_chunks_spilled_to_disk": 0,
1259 "peak_memory_used": "NNN",
1260 "sort_algorithm": "std::stable_sort",
1261 "sort_mode": "<fixed_sort_key, packed_additional_fields>"
1262 } /* filesort_summary */
1263 },
1264 {
1265 "sorting_table": "<temporary>",
1266 "filesort_information": [
1267 {
1268 "direction": "asc",
1269 "expression": "`cc`"
1270 }
1271 ] /* filesort_information */,
1272 "filesort_priority_queue_optimization": {
1273 "usable": false,
1274 "cause": "not applicable (no LIMIT)"
1275 } /* filesort_priority_queue_optimization */,
1276 "filesort_execution": [
1277 ] /* filesort_execution */,
1278 "filesort_summary": {
1279 "memory_available": 262144,
1280 "key_size": 1001,
1281 "row_size": 2512,
1282 "max_rows_per_buffer": 15,
1283 "num_rows_estimate": 15,
1284 "num_rows_found": 66,
1285 "num_initial_chunks_spilled_to_disk": 0,
1286 "peak_memory_used": "NNN",
1287 "sort_algorithm": "std::sort",
1288 "sort_mode": "<fixed_sort_key, packed_additional_fields>"
1289 } /* filesort_summary */
1290 }
1291 ] /* steps */
1292 } /* join_execution */
1293 }
1294 ] /* steps */
1295 } 0 0
1296 DROP TABLE t1;
1297 #
1298 SET SESSION internal_tmp_mem_storage_engine=default;
1299 #
1300 # Bug #30773218 ASSERTION IN SQL_TMP_TABLE.CC::TRACE_TMP_TABLE
1301 #
1302 CREATE TABLE t1(name VARCHAR(64), type TEXT);
1303 INSERT INTO t1(name, type) VALUES
1304 ('t1', 'table'),
1305 ('t2', 'table'),
1306 ('t3', 'table'),
1307 ('t4', 'table'),
1308 ('t5', 'table'),
1309 ('t6', 'table'),
1310 ('t7', 'table'),
1311 ('t8', 'table'),
1312 ('t9', 'table'),
1313 ('t10', 'table'),
1314 ('t11', 'table');
1315 CREATE TABLE t2(name VARCHAR(64), data TEXT);
1316 INSERT INTO t2(name, data) VALUES
1317 ('t1', 'data'),
1318 ('t2', 'data'),
1319 ('t3', 'data'),
1320 ('t4', 'data'),
1321 ('t5', 'data'),
1322 ('t6', 'data'),
1323 ('t7', 'data'),
1324 ('t8', 'data');
1325 SET SESSION internal_tmp_mem_storage_engine=MEMORY;
1326 SET @@session.optimizer_trace='enabled=on';
1327 SET @@session.tmp_table_size = 1024;
1328 SET @@optimizer_switch='semijoin=off';
1329 SELECT name, data FROM t2 WHERE name IN
1330 ( SELECT name FROM t1 WHERE type='table');
1331 name data
1332 t1 data
1333 t2 data
1334 t3 data
1335 t4 data
1336 t5 data
1337 t6 data
1338 t7 data
1339 t8 data
1340 DROP TABLE t1, t2;
1341 SET SESSION internal_tmp_mem_storage_engine=default;
1342 SET @@session.optimizer_trace='enabled=default';
1343 SET @@session.tmp_table_size = default;
1344 SET @@optimizer_switch='semijoin=default';