Debian Codegraph @

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

raw
1 set optimizer_trace_max_mem_size=1048576;
2 set end_markers_in_json=on;
3 set optimizer_trace="enabled=on";
4 # check that if a sub-statement should not be traced,
5 # it is not traced even if inside a traced top statement
6
7 set optimizer_trace_offset=0, optimizer_trace_limit=100;
8 create function f1(arg char(1)) returns int
9 begin
10 declare res int;
11 declare dummy varchar(1);
12 select 1 into res from dual;
13 select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
14 select 2 into res from dual;
15 return 3;
16 end|
17 select f1("c")|
18 f1("c")
19 3
20
21 select * from information_schema.OPTIMIZER_TRACE|
22 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
23 select f1("c") {
24 "steps": [
25 {
26 "join_preparation": {
27 "select#": 1,
28 "steps": [
29 {
30 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
31 }
32 ] /* steps */
33 } /* join_preparation */
34 }
35 ] /* steps */
36 } 0 0
37 select f1("c") {
38 "steps": [
39 {
40 "join_optimization": {
41 "select#": 1,
42 "steps": [
43 ] /* steps */
44 } /* join_optimization */
45 },
46 {
47 "join_execution": {
48 "select#": 1,
49 "steps": [
50 ] /* steps */
51 } /* join_execution */
52 }
53 ] /* steps */
54 } 0 0
55 set res@1 NULL {
56 "steps": [
57 ] /* steps */
58 } 0 0
59 set dummy@2 NULL {
60 "steps": [
61 ] /* steps */
62 } 0 0
63 select 1 into res from dual {
64 "steps": [
65 {
66 "join_preparation": {
67 "select#": 1,
68 "steps": [
69 {
70 "expanded_query": "/* select#1 */ select 1 AS `1`"
71 }
72 ] /* steps */
73 } /* join_preparation */
74 },
75 {
76 "join_optimization": {
77 "select#": 1,
78 "steps": [
79 ] /* steps */
80 } /* join_optimization */
81 },
82 {
83 "join_execution": {
84 "select#": 1,
85 "steps": [
86 ] /* steps */
87 } /* join_execution */
88 }
89 ] /* steps */
90 } 0 0
91 select 2 into res from dual {
92 "steps": [
93 {
94 "join_preparation": {
95 "select#": 1,
96 "steps": [
97 {
98 "expanded_query": "/* select#1 */ select 2 AS `2`"
99 }
100 ] /* steps */
101 } /* join_preparation */
102 },
103 {
104 "join_optimization": {
105 "select#": 1,
106 "steps": [
107 ] /* steps */
108 } /* join_optimization */
109 },
110 {
111 "join_execution": {
112 "select#": 1,
113 "steps": [
114 ] /* steps */
115 } /* join_execution */
116 }
117 ] /* steps */
118 } 0 0
119 freturn 3 3 {
120 "steps": [
121 ] /* steps */
122 } 0 0
123 set optimizer_trace_offset=default, optimizer_trace_limit=default;
124 drop function f1;
125 # check that if a tracing gets disabled in a routine's body,
126 # substatements are not traced
127
128 set optimizer_trace_offset=0, optimizer_trace_limit=100;
129 create function f1(arg char(1)) returns int
130 begin
131 declare res int;
132 declare dummy varchar(1);
133 set optimizer_trace="enabled=off";
134 select 1 into res from dual;
135 select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
136 select 2 into res from dual;
137 return 3;
138 end|
139 select f1("c")|
140 f1("c")
141 3
142
143 select * from information_schema.OPTIMIZER_TRACE|
144 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
145 select f1("c") {
146 "steps": [
147 {
148 "join_preparation": {
149 "select#": 1,
150 "steps": [
151 {
152 "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
153 }
154 ] /* steps */
155 } /* join_preparation */
156 }
157 ] /* steps */
158 } 0 0
159 select f1("c") {
160 "steps": [
161 {
162 "join_optimization": {
163 "select#": 1,
164 "steps": [
165 ] /* steps */
166 } /* join_optimization */
167 },
168 {
169 "join_execution": {
170 "select#": 1,
171 "steps": [
172 ] /* steps */
173 } /* join_execution */
174 }
175 ] /* steps */
176 } 0 0
177 set res@1 NULL {
178 "steps": [
179 ] /* steps */
180 } 0 0
181 set dummy@2 NULL {
182 "steps": [
183 ] /* steps */
184 } 0 0
185 set optimizer_trace_offset=default, optimizer_trace_limit=default;
186 select @@optimizer_trace;
187 @@optimizer_trace
188 enabled=off,one_line=off
189 set optimizer_trace="enabled=on";
190 drop function f1;
191
192 # Check that if a sub-statement reads OPTIMIZER_TRACE,
193 # thus reading the unfinished trace of its caller statement,
194 # there is no crash.
195
196 create temporary table optt
197 (id int primary key auto_increment,
198 QUERY varchar(200),
199 TRACE text);
200 create table t1 (a int, key(a));
201 insert into t1 values(2);
202 set optimizer_trace_offset=0, optimizer_trace_limit=100;
203 create function f1(arg char(1)) returns int
204 begin
205 declare res int;
206 insert into optt select NULL, QUERY, TRACE from information_schema.OPTIMIZER_TRACE;
207 return 3;
208 end|
209 select * from t1 where a in (select f1("c") from t1)|
210 a
211
212 set optimizer_trace="enabled=off";
213 this should find unfinished traces
214 select count(*) from optt where TRACE NOT LIKE "%] /* steps */\n}";
215 count(*)
216 1
217 select count(*)<>0 from optt;
218 count(*)<>0
219 1
220 this should not
221 select count(*) from information_schema.OPTIMIZER_TRACE where TRACE NOT LIKE "%] /* steps */\n}";
222 count(*)
223 0
224 select count(*)<>0 from information_schema.OPTIMIZER_TRACE;
225 count(*)<>0
226 1
227 set optimizer_trace_offset=default, optimizer_trace_limit=default;
228 drop temporary table optt;
229 drop function f1;
230 drop table t1;
231 set optimizer_trace="enabled=on";
232
233 # check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)
234
235 create table t1(a int, b int);
236 create view v1 as select a from t1;
237 select VIEW_DEFINITION from information_schema.VIEWS
238 where TABLE_SCHEMA="test" and TABLE_NAME="v1";
239 VIEW_DEFINITION
240 select `test`.`t1`.`a` AS `a` from `test`.`t1`
241 drop table t1;
242 drop view v1;
243
244 # check for readable display of BIT values
245
246 create table t1 (a bit(5), key(a));
247 insert into t1 values(b'00000'),(b'01101');
248 select cast(a as unsigned) from t1 where a > b'01100';
249 cast(a as unsigned)
250 13
251 select TRACE from information_schema.OPTIMIZER_TRACE;
252 TRACE
253 {
254 "steps": [
255 {
256 "join_optimization": {
257 "select#": 1,
258 "steps": [
259 {
260 "condition_processing": {
261 "condition": "WHERE",
262 "original_condition": "(`t1`.`a` > 0x0c)",
263 "steps": [
264 {
265 "transformation": "equality_propagation",
266 "resulting_condition": "(`t1`.`a` > 0x0c)"
267 },
268 {
269 "transformation": "constant_propagation",
270 "resulting_condition": "(`t1`.`a` > 0x0c)"
271 },
272 {
273 "transformation": "trivial_condition_removal",
274 "resulting_condition": "(`t1`.`a` > 0x0c)"
275 }
276 ] /* steps */
277 } /* condition_processing */
278 },
279 {
280 "substitute_generated_columns": {
281 } /* substitute_generated_columns */
282 },
283 {
284 "table_dependencies": [
285 {
286 "table": "`t1`",
287 "row_may_be_null": false,
288 "map_bit": 0,
289 "depends_on_map_bits": [
290 ] /* depends_on_map_bits */
291 }
292 ] /* table_dependencies */
293 },
294 {
295 "ref_optimizer_key_uses": [
296 ] /* ref_optimizer_key_uses */
297 },
298 {
299 "rows_estimation": [
300 {
301 "table": "`t1`",
302 "range_analysis": {
303 "table_scan": {
304 "rows": 2,
305 "cost": 2.80085
306 } /* table_scan */,
307 "potential_range_indexes": [
308 {
309 "index": "a",
310 "usable": true,
311 "key_parts": [
312 "a"
313 ] /* key_parts */
314 }
315 ] /* potential_range_indexes */,
316 "best_covering_index_scan": {
317 "index": "a",
318 "cost": 0.454386,
319 "chosen": true
320 } /* best_covering_index_scan */,
321 "setup_range_conditions": [
322 ] /* setup_range_conditions */,
323 "group_index_range": {
324 "chosen": false,
325 "cause": "not_group_by_or_distinct"
326 } /* group_index_range */,
327 "skip_scan_range": {
328 "potential_skip_scan_indexes": [
329 {
330 "index": "a",
331 "usable": false,
332 "cause": "prefix_not_const_equality"
333 }
334 ] /* potential_skip_scan_indexes */
335 } /* skip_scan_range */,
336 "analyzing_range_alternatives": {
337 "range_scan_alternatives": [
338 {
339 "index": "a",
340 "ranges": [
341 "12 < a"
342 ] /* ranges */,
343 "index_dives_for_eq_ranges": true,
344 "rowid_ordered": false,
345 "using_mrr": false,
346 "index_only": true,
347 "in_memory": -1,
348 "rows": 2,
349 "cost": 0.464386,
350 "chosen": false,
351 "cause": "cost"
352 }
353 ] /* range_scan_alternatives */,
354 "analyzing_roworder_intersect": {
355 "usable": false,
356 "cause": "too_few_roworder_scans"
357 } /* analyzing_roworder_intersect */
358 } /* analyzing_range_alternatives */
359 } /* range_analysis */
360 }
361 ] /* rows_estimation */
362 },
363 {
364 "considered_execution_plans": [
365 {
366 "plan_prefix": [
367 ] /* plan_prefix */,
368 "table": "`t1`",
369 "best_access_path": {
370 "considered_access_paths": [
371 {
372 "rows_to_scan": 2,
373 "access_type": "scan",
374 "resulting_rows": 2,
375 "cost": 0.700854,
376 "chosen": true
377 }
378 ] /* considered_access_paths */
379 } /* best_access_path */,
380 "condition_filtering_pct": 100,
381 "rows_for_plan": 2,
382 "cost_for_plan": 0.700854,
383 "chosen": true
384 }
385 ] /* considered_execution_plans */
386 },
387 {
388 "attaching_conditions_to_tables": {
389 "original_condition": "(`t1`.`a` > 0x0c)",
390 "attached_conditions_computation": [
391 ] /* attached_conditions_computation */,
392 "attached_conditions_summary": [
393 {
394 "table": "`t1`",
395 "attached": "(`t1`.`a` > 0x0c)"
396 }
397 ] /* attached_conditions_summary */
398 } /* attaching_conditions_to_tables */
399 },
400 {
401 "finalizing_table_conditions": [
402 {
403 "table": "`t1`",
404 "original_table_condition": "(`t1`.`a` > 0x0c)",
405 "final_table_condition ": "(`t1`.`a` > 0x0c)"
406 }
407 ] /* finalizing_table_conditions */
408 },
409 {
410 "refine_plan": [
411 {
412 "table": "`t1`"
413 }
414 ] /* refine_plan */
415 }
416 ] /* steps */
417 } /* join_optimization */
418 },
419 {
420 "join_execution": {
421 "select#": 1,
422 "steps": [
423 ] /* steps */
424 } /* join_execution */
425 }
426 ] /* steps */
427 }
428 drop table t1;
429
430 # check that trace lists all pushed down ON conditions
431
432 create table t1 (i int not null);
433 insert into t1 values (0), (2),(3),(4);
434 create table t2 (i int not null);
435 insert into t2 values (0),(1), (3),(4);
436 create table t3 (i int not null);
437 insert into t3 values (0),(1),(2), (4);
438 select * from
439 t1 LEFT JOIN
440 ( t2 LEFT JOIN
441 ( t3
442 )
443 ON t3.i = t2.i
444 )
445 ON t2.i = t1.i
446 WHERE t3.i IS NULL
447 ;
448 i i i
449 2 NULL NULL
450 3 3 NULL
451 select TRACE from information_schema.OPTIMIZER_TRACE;
452 TRACE
453 {
454 "steps": [
455 {
456 "join_optimization": {
457 "select#": 1,
458 "steps": [
459 {
460 "condition_processing": {
461 "condition": "WHERE",
462 "original_condition": "(`t3`.`i` is null)",
463 "steps": [
464 {
465 "transformation": "equality_propagation",
466 "resulting_condition": "(`t3`.`i` is null)"
467 },
468 {
469 "transformation": "constant_propagation",
470 "resulting_condition": "(`t3`.`i` is null)"
471 },
472 {
473 "transformation": "trivial_condition_removal",
474 "resulting_condition": "(`t3`.`i` is null)"
475 }
476 ] /* steps */
477 } /* condition_processing */
478 },
479 {
480 "substitute_generated_columns": {
481 } /* substitute_generated_columns */
482 },
483 {
484 "table_dependencies": [
485 {
486 "table": "`t1`",
487 "row_may_be_null": false,
488 "map_bit": 0,
489 "depends_on_map_bits": [
490 ] /* depends_on_map_bits */
491 },
492 {
493 "table": "`t2`",
494 "row_may_be_null": true,
495 "map_bit": 1,
496 "depends_on_map_bits": [
497 0
498 ] /* depends_on_map_bits */
499 },
500 {
501 "table": "`t3`",
502 "row_may_be_null": true,
503 "map_bit": 2,
504 "depends_on_map_bits": [
505 0,
506 1
507 ] /* depends_on_map_bits */
508 }
509 ] /* table_dependencies */
510 },
511 {
512 "ref_optimizer_key_uses": [
513 ] /* ref_optimizer_key_uses */
514 },
515 {
516 "rows_estimation": [
517 {
518 "table": "`t1`",
519 "table_scan": {
520 "rows": 4,
521 "cost": 0.501709
522 } /* table_scan */
523 },
524 {
525 "table": "`t2`",
526 "table_scan": {
527 "rows": 4,
528 "cost": 0.501709
529 } /* table_scan */
530 },
531 {
532 "table": "`t3`",
533 "table_scan": {
534 "rows": 4,
535 "cost": 0.501709
536 } /* table_scan */
537 }
538 ] /* rows_estimation */
539 },
540 {
541 "considered_execution_plans": [
542 {
543 "plan_prefix": [
544 ] /* plan_prefix */,
545 "table": "`t1`",
546 "best_access_path": {
547 "considered_access_paths": [
548 {
549 "rows_to_scan": 4,
550 "access_type": "scan",
551 "resulting_rows": 4,
552 "cost": 0.901709,
553 "chosen": true
554 }
555 ] /* considered_access_paths */
556 } /* best_access_path */,
557 "condition_filtering_pct": 100,
558 "rows_for_plan": 4,
559 "cost_for_plan": 0.901709,
560 "rest_of_plan": [
561 {
562 "plan_prefix": [
563 "`t1`"
564 ] /* plan_prefix */,
565 "table": "`t2`",
566 "best_access_path": {
567 "considered_access_paths": [
568 {
569 "rows_to_scan": 4,
570 "access_type": "scan",
571 "using_join_cache": true,
572 "buffers_needed": 1,
573 "resulting_rows": 4,
574 "cost": 2.10174,
575 "chosen": true
576 }
577 ] /* considered_access_paths */
578 } /* best_access_path */,
579 "condition_filtering_pct": 100,
580 "rows_for_plan": 16,
581 "cost_for_plan": 3.00345,
582 "rest_of_plan": [
583 {
584 "plan_prefix": [
585 "`t1`",
586 "`t2`"
587 ] /* plan_prefix */,
588 "table": "`t3`",
589 "best_access_path": {
590 "considered_access_paths": [
591 {
592 "rows_to_scan": 4,
593 "filtering_effect": [
594 ] /* filtering_effect */,
595 "final_filtering_effect": 0.25,
596 "access_type": "scan",
597 "using_join_cache": true,
598 "buffers_needed": 1,
599 "resulting_rows": 1,
600 "cost": 2.40215,
601 "chosen": true
602 }
603 ] /* considered_access_paths */
604 } /* best_access_path */,
605 "condition_filtering_pct": 100,
606 "rows_for_plan": 16,
607 "cost_for_plan": 5.4056,
608 "chosen": true
609 }
610 ] /* rest_of_plan */
611 }
612 ] /* rest_of_plan */
613 }
614 ] /* considered_execution_plans */
615 },
616 {
617 "attaching_conditions_to_tables": {
618 "original_condition": "(`t3`.`i` is null)",
619 "attached_conditions_computation": [
620 ] /* attached_conditions_computation */,
621 "attached_conditions_summary": [
622 {
623 "table": "`t1`",
624 "attached": null
625 },
626 {
627 "table": "`t2`",
628 "attached": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)"
629 },
630 {
631 "table": "`t3`",
632 "attached": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))"
633 }
634 ] /* attached_conditions_summary */
635 } /* attaching_conditions_to_tables */
636 },
637 {
638 "finalizing_table_conditions": [
639 {
640 "table": "`t2`",
641 "original_table_condition": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)",
642 "final_table_condition ": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)"
643 },
644 {
645 "table": "`t3`",
646 "original_table_condition": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))",
647 "final_table_condition ": "(<if>(found_match(t2..t3), <if>(found_match(t3), (`t3`.`i` is null), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))"
648 }
649 ] /* finalizing_table_conditions */
650 },
651 {
652 "refine_plan": [
653 {
654 "table": "`t1`"
655 },
656 {
657 "table": "`t2`"
658 },
659 {
660 "table": "`t3`"
661 }
662 ] /* refine_plan */
663 }
664 ] /* steps */
665 } /* join_optimization */
666 },
667 {
668 "join_execution": {
669 "select#": 1,
670 "steps": [
671 ] /* steps */
672 } /* join_execution */
673 }
674 ] /* steps */
675 }
676 drop table t1,t2,t3;
677
678 # test of tracing a query with an HAVING condition, in
679 # ps-protocol, does not crash
680
681 CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
682 INSERT INTO t1 VALUES (16,'f');
683 INSERT INTO t1 VALUES (16,'f');
684 CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
685 INSERT INTO t2 VALUES (13,'f');
686 INSERT INTO t2 VALUES (20,'f');
687 CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)) CHARSET utf8mb4;
688 INSERT INTO t3 VALUES (7,'f');
689 EXPLAIN SELECT t1.f2 FROM t1
690 STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
691 HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
692 ORDER BY f2;
693 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
694 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
695 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (hash join)
696 1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (hash join)
697 2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
698 Warnings:
699 Note 1003 /* select#1 */ select `test`.`t1`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = `test`.`t3`.`f2`) having true order by `test`.`t1`.`f2`
700 SELECT t1.f2 FROM t1
701 STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
702 HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
703 ORDER BY f2;
704 f2
705 f
706 f
707 f
708 f
709 select TRACE from information_schema.OPTIMIZER_TRACE;
710 TRACE
711 {
712 "steps": [
713 {
714 "join_optimization": {
715 "select#": 1,
716 "steps": [
717 {
718 "condition_processing": {
719 "condition": "WHERE",
720 "original_condition": "((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`))",
721 "steps": [
722 {
723 "transformation": "equality_propagation",
724 "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
725 },
726 {
727 "transformation": "constant_propagation",
728 "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
729 },
730 {
731 "transformation": "trivial_condition_removal",
732 "resulting_condition": "multiple equal(`t3`.`f2`, `t2`.`f2`)"
733 }
734 ] /* steps */
735 } /* condition_processing */
736 },
737 {
738 "condition_processing": {
739 "condition": "HAVING",
740 "original_condition": "<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))) is false)",
741 "steps": [
742 {
743 "transformation": "constant_propagation",
744 "subselect_evaluation": [
745 ] /* subselect_evaluation */,
746 "resulting_condition": "<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))) is false)"
747 },
748 {
749 "transformation": "trivial_condition_removal",
750 "subselect_evaluation": [
751 {
752 "subselect_execution": {
753 "select#": 2,
754 "steps": [
755 {
756 "join_optimization": {
757 "select#": 2,
758 "steps": [
759 {
760 "condition_processing": {
761 "condition": "HAVING",
762 "original_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))",
763 "steps": [
764 {
765 "transformation": "constant_propagation",
766 "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
767 },
768 {
769 "transformation": "trivial_condition_removal",
770 "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or (`t1`.`f2` is null)) and ((<cache>('i') = min(`t1`.`f2`)) or (min(`t1`.`f2`) is null)) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
771 }
772 ] /* steps */
773 } /* condition_processing */
774 },
775 {
776 "table_dependencies": [
777 {
778 "table": "`t1`",
779 "row_may_be_null": true,
780 "map_bit": 0,
781 "depends_on_map_bits": [
782 ] /* depends_on_map_bits */
783 }
784 ] /* table_dependencies */
785 },
786 {
787 "rows_estimation": [
788 {
789 "table": "`t1`",
790 "table_scan": {
791 "rows": 2,
792 "cost": 0.502441
793 } /* table_scan */
794 }
795 ] /* rows_estimation */
796 },
797 {
798 "considered_execution_plans": [
799 {
800 "plan_prefix": [
801 ] /* plan_prefix */,
802 "table": "`t1`",
803 "best_access_path": {
804 "considered_access_paths": [
805 {
806 "rows_to_scan": 2,
807 "access_type": "scan",
808 "resulting_rows": 2,
809 "cost": 0.702441,
810 "chosen": true
811 }
812 ] /* considered_access_paths */
813 } /* best_access_path */,
814 "condition_filtering_pct": 100,
815 "rows_for_plan": 2,
816 "cost_for_plan": 0.702441,
817 "chosen": true
818 }
819 ] /* considered_execution_plans */
820 },
821 {
822 "attaching_conditions_to_tables": {
823 "original_condition": null,
824 "attached_conditions_computation": [
825 ] /* attached_conditions_computation */,
826 "attached_conditions_summary": [
827 {
828 "table": "`t1`",
829 "attached": null
830 }
831 ] /* attached_conditions_summary */
832 } /* attaching_conditions_to_tables */
833 },
834 {
835 "optimizing_distinct_group_by_order_by": {
836 } /* optimizing_distinct_group_by_order_by */
837 },
838 {
839 "finalizing_table_conditions": [
840 ] /* finalizing_table_conditions */
841 },
842 {
843 "refine_plan": [
844 {
845 "table": "`t1`"
846 }
847 ] /* refine_plan */
848 },
849 {
850 "considering_tmp_tables": [
851 ] /* considering_tmp_tables */
852 }
853 ] /* steps */
854 } /* join_optimization */
855 },
856 {
857 "join_execution": {
858 "select#": 2,
859 "steps": [
860 ] /* steps */
861 } /* join_execution */
862 }
863 ] /* steps */
864 } /* subselect_execution */
865 }
866 ] /* subselect_evaluation */,
867 "resulting_condition": null
868 }
869 ] /* steps */
870 } /* condition_processing */
871 },
872 {
873 "substitute_generated_columns": {
874 } /* substitute_generated_columns */
875 },
876 {
877 "table_dependencies": [
878 {
879 "table": "`t1`",
880 "row_may_be_null": false,
881 "map_bit": 0,
882 "depends_on_map_bits": [
883 ] /* depends_on_map_bits */
884 },
885 {
886 "table": "`t2`",
887 "row_may_be_null": false,
888 "map_bit": 1,
889 "depends_on_map_bits": [
890 0
891 ] /* depends_on_map_bits */
892 },
893 {
894 "table": "`t3`",
895 "row_may_be_null": false,
896 "map_bit": 2,
897 "depends_on_map_bits": [
898 0
899 ] /* depends_on_map_bits */
900 }
901 ] /* table_dependencies */
902 },
903 {
904 "ref_optimizer_key_uses": [
905 ] /* ref_optimizer_key_uses */
906 },
907 {
908 "rows_estimation": [
909 {
910 "table": "`t1`",
911 "table_scan": {
912 "rows": 2,
913 "cost": 0.502441
914 } /* table_scan */
915 },
916 {
917 "table": "`t2`",
918 "table_scan": {
919 "rows": 2,
920 "cost": 0.502441
921 } /* table_scan */
922 },
923 {
924 "table": "`t3`",
925 "table_scan": {
926 "rows": 1,
927 "cost": 0.501221
928 } /* table_scan */
929 }
930 ] /* rows_estimation */
931 },
932 {
933 "considered_execution_plans": [
934 {
935 "plan_prefix": [
936 ] /* plan_prefix */,
937 "table": "`t1`",
938 "best_access_path": {
939 "considered_access_paths": [
940 {
941 "rows_to_scan": 2,
942 "access_type": "scan",
943 "resulting_rows": 2,
944 "cost": 0.702441,
945 "chosen": true,
946 "use_tmp_table": true
947 }
948 ] /* considered_access_paths */
949 } /* best_access_path */,
950 "condition_filtering_pct": 100,
951 "rows_for_plan": 2,
952 "cost_for_plan": 0.702441,
953 "rest_of_plan": [
954 {
955 "plan_prefix": [
956 "`t1`"
957 ] /* plan_prefix */,
958 "table": "`t3`",
959 "best_access_path": {
960 "considered_access_paths": [
961 {
962 "rows_to_scan": 1,
963 "filtering_effect": [
964 ] /* filtering_effect */,
965 "final_filtering_effect": 1,
966 "access_type": "scan",
967 "using_join_cache": true,
968 "buffers_needed": 1,
969 "resulting_rows": 1,
970 "cost": 0.701244,
971 "chosen": true
972 }
973 ] /* considered_access_paths */
974 } /* best_access_path */,
975 "condition_filtering_pct": 100,
976 "rows_for_plan": 2,
977 "cost_for_plan": 1.40369,
978 "rest_of_plan": [
979 {
980 "plan_prefix": [
981 "`t1`",
982 "`t3`"
983 ] /* plan_prefix */,
984 "table": "`t2`",
985 "best_access_path": {
986 "considered_access_paths": [
987 {
988 "rows_to_scan": 2,
989 "filtering_effect": [
990 ] /* filtering_effect */,
991 "final_filtering_effect": 1,
992 "access_type": "scan",
993 "using_join_cache": true,
994 "buffers_needed": 1,
995 "resulting_rows": 2,
996 "cost": 0.902487,
997 "chosen": true
998 }
999 ] /* considered_access_paths */
1000 } /* best_access_path */,
1001 "condition_filtering_pct": 100,
1002 "rows_for_plan": 4,
1003 "cost_for_plan": 2.30617,
1004 "sort_cost": 4,
1005 "new_cost_for_plan": 6.30617,
1006 "chosen": true
1007 }
1008 ] /* rest_of_plan */
1009 },
1010 {
1011 "plan_prefix": [
1012 "`t1`"
1013 ] /* plan_prefix */,
1014 "table": "`t2`",
1015 "best_access_path": {
1016 "considered_access_paths": [
1017 {
1018 "rows_to_scan": 2,
1019 "filtering_effect": [
1020 ] /* filtering_effect */,
1021 "final_filtering_effect": 1,
1022 "access_type": "scan",
1023 "using_join_cache": true,
1024 "buffers_needed": 1,
1025 "resulting_rows": 2,
1026 "cost": 0.902464,
1027 "chosen": true
1028 }
1029 ] /* considered_access_paths */
1030 } /* best_access_path */,
1031 "condition_filtering_pct": 100,
1032 "rows_for_plan": 4,
1033 "cost_for_plan": 1.60491,
1034 "pruned_by_heuristic": true
1035 }
1036 ] /* rest_of_plan */
1037 }
1038 ] /* considered_execution_plans */
1039 },
1040 {
1041 "attaching_conditions_to_tables": {
1042 "original_condition": "(`t2`.`f2` = `t3`.`f2`)",
1043 "attached_conditions_computation": [
1044 ] /* attached_conditions_computation */,
1045 "attached_conditions_summary": [
1046 {
1047 "table": "`t1`",
1048 "attached": null
1049 },
1050 {
1051 "table": "`t3`",
1052 "attached": null
1053 },
1054 {
1055 "table": "`t2`",
1056 "attached": "(`t2`.`f2` = `t3`.`f2`)"
1057 }
1058 ] /* attached_conditions_summary */
1059 } /* attaching_conditions_to_tables */
1060 },
1061 {
1062 "optimizing_distinct_group_by_order_by": {
1063 "simplifying_order_by": {
1064 "original_clause": "`t1`.`f2`",
1065 "items": [
1066 {
1067 "item": "`t1`.`f2`"
1068 }
1069 ] /* items */,
1070 "resulting_clause_is_simple": true,
1071 "resulting_clause": "`t1`.`f2`"
1072 } /* simplifying_order_by */
1073 } /* optimizing_distinct_group_by_order_by */
1074 },
1075 {
1076 "finalizing_table_conditions": [
1077 {
1078 "table": "`t2`",
1079 "original_table_condition": "(`t2`.`f2` = `t3`.`f2`)",
1080 "final_table_condition ": "(`t2`.`f2` = `t3`.`f2`)"
1081 }
1082 ] /* finalizing_table_conditions */
1083 },
1084 {
1085 "refine_plan": [
1086 {
1087 "table": "`t1`"
1088 },
1089 {
1090 "table": "`t3`"
1091 },
1092 {
1093 "table": "`t2`"
1094 }
1095 ] /* refine_plan */
1096 },
1097 {
1098 "considering_tmp_tables": [
1099 {
1100 "adding_tmp_table_in_plan_at_position": 3,
1101 "write_method": "write_all_rows"
1102 },
1103 {
1104 "adding_sort_to_table": ""
1105 } /* filesort */
1106 ] /* considering_tmp_tables */
1107 }
1108 ] /* steps */
1109 } /* join_optimization */
1110 },
1111 {
1112 "join_execution": {
1113 "select#": 1,
1114 "steps": [
1115 {
1116 "sorting_table": "<temporary>",
1117 "filesort_information": [
1118 {
1119 "direction": "asc",
1120 "expression": "`t1`.`f2`"
1121 }
1122 ] /* filesort_information */,
1123 "filesort_priority_queue_optimization": {
1124 "usable": false,
1125 "cause": "not applicable (no LIMIT)"
1126 } /* filesort_priority_queue_optimization */,
1127 "filesort_execution": [
1128 ] /* filesort_execution */,
1129 "filesort_summary": {
1130 "memory_available": 262144,
1131 "key_size": "XXX",
1132 "row_size": "XXX",
1133 "max_rows_per_buffer": "XXX",
1134 "num_rows_estimate": 15,
1135 "num_rows_found": 4,
1136 "num_initial_chunks_spilled_to_disk": 0,
1137 "peak_memory_used": "NNN",
1138 "sort_algorithm": "std::sort",
1139 "unpacked_addon_fields": "skip_heuristic",
1140 "sort_mode": "<varlen_sort_key, additional_fields>"
1141 } /* filesort_summary */
1142 }
1143 ] /* steps */
1144 } /* join_execution */
1145 }
1146 ] /* steps */
1147 }
1148 DROP TABLES t1,t2,t3;
1149
1150 # Test that tracing a query with a materialized FROM-clause
1151 # derived table using a GROUP BY, does not crash
1152
1153 create table t1 (a int, b int);
1154 insert into t1 values (1,1), (2,null), (3, 4);
1155 select max(x) from (select sum(a) as x from t1 group by b) as teeone;
1156 max(x)
1157 3
1158 select TRACE from information_schema.OPTIMIZER_TRACE;
1159 TRACE
1160 {
1161 "steps": [
1162 {
1163 "join_optimization": {
1164 "select#": 1,
1165 "steps": [
1166 {
1167 "join_optimization": {
1168 "select#": 2,
1169 "steps": [
1170 {
1171 "substitute_generated_columns": {
1172 } /* substitute_generated_columns */
1173 },
1174 {
1175 "table_dependencies": [
1176 {
1177 "table": "`t1`",
1178 "row_may_be_null": false,
1179 "map_bit": 0,
1180 "depends_on_map_bits": [
1181 ] /* depends_on_map_bits */
1182 }
1183 ] /* table_dependencies */
1184 },
1185 {
1186 "rows_estimation": [
1187 {
1188 "table": "`t1`",
1189 "table_scan": {
1190 "rows": 3,
1191 "cost": 0.501648
1192 } /* table_scan */
1193 }
1194 ] /* rows_estimation */
1195 },
1196 {
1197 "considered_execution_plans": [
1198 {
1199 "plan_prefix": [
1200 ] /* plan_prefix */,
1201 "table": "`t1`",
1202 "best_access_path": {
1203 "considered_access_paths": [
1204 {
1205 "rows_to_scan": 3,
1206 "access_type": "scan",
1207 "resulting_rows": 3,
1208 "cost": 0.801648,
1209 "chosen": true,
1210 "use_tmp_table": true
1211 }
1212 ] /* considered_access_paths */
1213 } /* best_access_path */,
1214 "condition_filtering_pct": 100,
1215 "rows_for_plan": 3,
1216 "cost_for_plan": 0.801648,
1217 "sort_cost": 3,
1218 "new_cost_for_plan": 3.80165,
1219 "chosen": true
1220 }
1221 ] /* considered_execution_plans */
1222 },
1223 {
1224 "attaching_conditions_to_tables": {
1225 "original_condition": null,
1226 "attached_conditions_computation": [
1227 ] /* attached_conditions_computation */,
1228 "attached_conditions_summary": [
1229 {
1230 "table": "`t1`",
1231 "attached": null
1232 }
1233 ] /* attached_conditions_summary */
1234 } /* attaching_conditions_to_tables */
1235 },
1236 {
1237 "optimizing_distinct_group_by_order_by": {
1238 "simplifying_group_by": {
1239 "original_clause": "`t1`.`b`",
1240 "items": [
1241 {
1242 "item": "`t1`.`b`"
1243 }
1244 ] /* items */,
1245 "resulting_clause_is_simple": true,
1246 "resulting_clause": "`t1`.`b`"
1247 } /* simplifying_group_by */
1248 } /* optimizing_distinct_group_by_order_by */
1249 },
1250 {
1251 "finalizing_table_conditions": [
1252 ] /* finalizing_table_conditions */
1253 },
1254 {
1255 "refine_plan": [
1256 {
1257 "table": "`t1`"
1258 }
1259 ] /* refine_plan */
1260 },
1261 {
1262 "considering_tmp_tables": [
1263 {
1264 "adding_tmp_table_in_plan_at_position": 1,
1265 "write_method": "continuously_update_group_row"
1266 }
1267 ] /* considering_tmp_tables */
1268 }
1269 ] /* steps */
1270 } /* join_optimization */
1271 },
1272 {
1273 "table_dependencies": [
1274 {
1275 "table": " `teeone`",
1276 "row_may_be_null": false,
1277 "map_bit": 0,
1278 "depends_on_map_bits": [
1279 ] /* depends_on_map_bits */
1280 }
1281 ] /* table_dependencies */
1282 },
1283 {
1284 "rows_estimation": [
1285 {
1286 "table": " `teeone`",
1287 "table_scan": {
1288 "rows": 3,
1289 "cost": 2.5375
1290 } /* table_scan */
1291 }
1292 ] /* rows_estimation */
1293 },
1294 {
1295 "considered_execution_plans": [
1296 {
1297 "plan_prefix": [
1298 ] /* plan_prefix */,
1299 "table": " `teeone`",
1300 "best_access_path": {
1301 "considered_access_paths": [
1302 {
1303 "rows_to_scan": 3,
1304 "access_type": "scan",
1305 "resulting_rows": 3,
1306 "cost": 2.8375,
1307 "chosen": true
1308 }
1309 ] /* considered_access_paths */
1310 } /* best_access_path */,
1311 "condition_filtering_pct": 100,
1312 "rows_for_plan": 3,
1313 "cost_for_plan": 2.8375,
1314 "chosen": true
1315 }
1316 ] /* considered_execution_plans */
1317 },
1318 {
1319 "attaching_conditions_to_tables": {
1320 "original_condition": null,
1321 "attached_conditions_computation": [
1322 ] /* attached_conditions_computation */,
1323 "attached_conditions_summary": [
1324 {
1325 "table": " `teeone`",
1326 "attached": null
1327 }
1328 ] /* attached_conditions_summary */
1329 } /* attaching_conditions_to_tables */
1330 },
1331 {
1332 "optimizing_distinct_group_by_order_by": {
1333 } /* optimizing_distinct_group_by_order_by */
1334 },
1335 {
1336 "finalizing_table_conditions": [
1337 ] /* finalizing_table_conditions */
1338 },
1339 {
1340 "refine_plan": [
1341 {
1342 "table": " `teeone`"
1343 }
1344 ] /* refine_plan */
1345 },
1346 {
1347 "considering_tmp_tables": [
1348 ] /* considering_tmp_tables */
1349 }
1350 ] /* steps */
1351 } /* join_optimization */
1352 },
1353 {
1354 "join_execution": {
1355 "select#": 1,
1356 "steps": [
1357 {
1358 "creating_tmp_table": {
1359 "tmp_table_info": {
1360 "table": "teeone",
1361 "in_plan_at_position": 0,
1362 "columns": 1,
1363 "row_length": 16,
1364 "key_length": 0,
1365 "unique_constraint": false,
1366 "makes_grouped_rows": false,
1367 "cannot_insert_duplicates": false,
1368 "location": "TempTable"
1369 } /* tmp_table_info */
1370 } /* creating_tmp_table */
1371 },
1372 {
1373 "materialize": {
1374 "select#": 2,
1375 "steps": [
1376 {
1377 "temp_table_aggregate": {
1378 "select#": 2,
1379 "steps": [
1380 {
1381 "creating_tmp_table": {
1382 "tmp_table_info": {
1383 "table": "<temporary>",
1384 "in_plan_at_position": 1,
1385 "columns": 2,
1386 "row_length": 22,
1387 "key_length": 5,
1388 "unique_constraint": false,
1389 "makes_grouped_rows": true,
1390 "cannot_insert_duplicates": false,
1391 "location": "TempTable"
1392 } /* tmp_table_info */
1393 } /* creating_tmp_table */
1394 }
1395 ] /* steps */
1396 } /* temp_table_aggregate */
1397 }
1398 ] /* steps */
1399 } /* materialize */
1400 }
1401 ] /* steps */
1402 } /* join_execution */
1403 }
1404 ] /* steps */
1405 }
1406 drop table t1;
1407
1408 # To have no crash above, we had to restore the ref_array at
1409 # end of JOIN::exec(). This impacts how the query looks like,
1410 # but not too much, as seen in the error message below.
1411 # Comes from func_gconcat.test.
1412
1413 CREATE TABLE t1(f1 int);
1414 INSERT INTO t1 values (0),(0);
1415 set optimizer_trace="enabled=off";
1416 SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
1417 ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
1418 set optimizer_trace="enabled=on";
1419 SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
1420 ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
1421 DROP TABLE t1;
1422
1423 # Check that SQL PREPARE and SQL EXECUTE each produce one trace.
1424
1425 set optimizer_trace_offset=0, optimizer_trace_limit=100;
1426 prepare stmt from "select 1";
1427 select * from information_schema.OPTIMIZER_TRACE;
1428 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1429 select 1 {
1430 "steps": [
1431 {
1432 "join_preparation": {
1433 "select#": 1,
1434 "steps": [
1435 {
1436 "expanded_query": "/* select#1 */ select 1 AS `1`"
1437 }
1438 ] /* steps */
1439 } /* join_preparation */
1440 }
1441 ] /* steps */
1442 } 0 0
1443 set optimizer_trace_offset=0, optimizer_trace_limit=100;
1444 execute stmt;
1445 1
1446 1
1447 select * from information_schema.OPTIMIZER_TRACE;
1448 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1449 select 1 {
1450 "steps": [
1451 {
1452 "join_optimization": {
1453 "select#": 1,
1454 "steps": [
1455 ] /* steps */
1456 } /* join_optimization */
1457 },
1458 {
1459 "join_execution": {
1460 "select#": 1,
1461 "steps": [
1462 ] /* steps */
1463 } /* join_execution */
1464 }
1465 ] /* steps */
1466 } 0 0
1467 deallocate prepare stmt;
1468 set optimizer_trace_offset=default, optimizer_trace_limit=default;
1469
1470 # Test of SELECTs in IF in stored routine.
1471 # Same test for CASE WHEN.
1472
1473 create table t1 (a int);
1474 create procedure p1()
1475 begin
1476 if exists(select 1) then
1477 insert into t1 values(1);
1478 end if;
1479 if exists(select 2) then
1480 insert into t1 values(2);
1481 end if;
1482 if (select count(*) from t1) then
1483 insert into t1 values(3);
1484 end if;
1485 set @a=(select count(a) from t1 where a>0);
1486 case (select count(a) from t1 where a>1)
1487 when 2 then set @b=2;
1488 else set @b=3;
1489 end case;
1490 end|
1491 set optimizer_trace_offset=0, optimizer_trace_limit=100;
1492 set @old_max=@@optimizer_trace_max_mem_size;
1493 set optimizer_trace_max_mem_size=40000;
1494 call p1();
1495 select * from information_schema.OPTIMIZER_TRACE;
1496 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1497 set @old_max=@@optimizer_trace_max_mem_size {
1498 "steps": [
1499 ] /* steps */
1500 } 0 0
1501 set optimizer_trace_max_mem_size=40000 {
1502 "steps": [
1503 ] /* steps */
1504 } 0 0
1505 call p1() {
1506 "steps": [
1507 ] /* steps */
1508 } 0 0
1509 call p1() {
1510 "steps": [
1511 ] /* steps */
1512 } 0 0
1513 jump_if_not 2(2) exists(select 1) {
1514 "steps": [
1515 {
1516 "join_preparation": {
1517 "select#": 2,
1518 "steps": [
1519 {
1520 "expanded_query": "/* select#2 */ select 1"
1521 }
1522 ] /* steps */
1523 } /* join_preparation */
1524 },
1525 {
1526 "subselect_execution": {
1527 "select#": 2,
1528 "steps": [
1529 {
1530 "join_optimization": {
1531 "select#": 2,
1532 "steps": [
1533 ] /* steps */
1534 } /* join_optimization */
1535 },
1536 {
1537 "join_execution": {
1538 "select#": 2,
1539 "steps": [
1540 ] /* steps */
1541 } /* join_execution */
1542 }
1543 ] /* steps */
1544 } /* subselect_execution */
1545 }
1546 ] /* steps */
1547 } 0 0
1548 insert into t1 values(1) {
1549 "steps": [
1550 ] /* steps */
1551 } 0 0
1552 jump_if_not 4(4) exists(select 2) {
1553 "steps": [
1554 {
1555 "join_preparation": {
1556 "select#": 2,
1557 "steps": [
1558 {
1559 "expanded_query": "/* select#2 */ select 2"
1560 }
1561 ] /* steps */
1562 } /* join_preparation */
1563 },
1564 {
1565 "subselect_execution": {
1566 "select#": 2,
1567 "steps": [
1568 {
1569 "join_optimization": {
1570 "select#": 2,
1571 "steps": [
1572 ] /* steps */
1573 } /* join_optimization */
1574 },
1575 {
1576 "join_execution": {
1577 "select#": 2,
1578 "steps": [
1579 ] /* steps */
1580 } /* join_execution */
1581 }
1582 ] /* steps */
1583 } /* subselect_execution */
1584 }
1585 ] /* steps */
1586 } 0 0
1587 insert into t1 values(2) {
1588 "steps": [
1589 ] /* steps */
1590 } 0 0
1591 jump_if_not 6(6) (select count(0) from `test`.`t1`) {
1592 "steps": [
1593 {
1594 "join_preparation": {
1595 "select#": 2,
1596 "steps": [
1597 {
1598 "expanded_query": "/* select#2 */ select count(0) from `t1`"
1599 }
1600 ] /* steps */
1601 } /* join_preparation */
1602 },
1603 {
1604 "subselect_execution": {
1605 "select#": 2,
1606 "steps": [
1607 {
1608 "join_optimization": {
1609 "select#": 2,
1610 "steps": [
1611 ] /* steps */,
1612 "empty_result": {
1613 "cause": "Select tables optimized away"
1614 } /* empty_result */
1615 } /* join_optimization */
1616 },
1617 {
1618 "join_execution": {
1619 "select#": 2,
1620 "steps": [
1621 ] /* steps */
1622 } /* join_execution */
1623 }
1624 ] /* steps */
1625 } /* subselect_execution */
1626 }
1627 ] /* steps */
1628 } 0 0
1629 insert into t1 values(3) {
1630 "steps": [
1631 ] /* steps */
1632 } 0 0
1633 SET @a=(select count(a) from t1 where a>0) {
1634 "steps": [
1635 {
1636 "join_preparation": {
1637 "select#": 2,
1638 "steps": [
1639 {
1640 "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 0)"
1641 }
1642 ] /* steps */
1643 } /* join_preparation */
1644 },
1645 {
1646 "subselect_execution": {
1647 "select#": 2,
1648 "steps": [
1649 {
1650 "join_optimization": {
1651 "select#": 2,
1652 "steps": [
1653 {
1654 "condition_processing": {
1655 "condition": "WHERE",
1656 "original_condition": "(`t1`.`a` > 0)",
1657 "steps": [
1658 {
1659 "transformation": "equality_propagation",
1660 "resulting_condition": "(`t1`.`a` > 0)"
1661 },
1662 {
1663 "transformation": "constant_propagation",
1664 "resulting_condition": "(`t1`.`a` > 0)"
1665 },
1666 {
1667 "transformation": "trivial_condition_removal",
1668 "resulting_condition": "(`t1`.`a` > 0)"
1669 }
1670 ] /* steps */
1671 } /* condition_processing */
1672 },
1673 {
1674 "substitute_generated_columns": {
1675 } /* substitute_generated_columns */
1676 },
1677 {
1678 "table_dependencies": [
1679 {
1680 "table": "`t1`",
1681 "row_may_be_null": false,
1682 "map_bit": 0,
1683 "depends_on_map_bits": [
1684 ] /* depends_on_map_bits */
1685 }
1686 ] /* table_dependencies */
1687 },
1688 {
1689 "ref_optimizer_key_uses": [
1690 ] /* ref_optimizer_key_uses */
1691 },
1692 {
1693 "rows_estimation": [
1694 {
1695 "table": "`t1`",
1696 "table_scan": {
1697 "rows": 3,
1698 "cost": 0.501282
1699 } /* table_scan */
1700 }
1701 ] /* rows_estimation */
1702 },
1703 {
1704 "considered_execution_plans": [
1705 {
1706 "plan_prefix": [
1707 ] /* plan_prefix */,
1708 "table": "`t1`",
1709 "best_access_path": {
1710 "considered_access_paths": [
1711 {
1712 "rows_to_scan": 3,
1713 "filtering_effect": [
1714 ] /* filtering_effect */,
1715 "final_filtering_effect": 0.333333,
1716 "access_type": "scan",
1717 "resulting_rows": 1,
1718 "cost": 0.801282,
1719 "chosen": true
1720 }
1721 ] /* considered_access_paths */
1722 } /* best_access_path */,
1723 "condition_filtering_pct": 100,
1724 "rows_for_plan": 1,
1725 "cost_for_plan": 0.801282,
1726 "chosen": true
1727 }
1728 ] /* considered_execution_plans */
1729 },
1730 {
1731 "attaching_conditions_to_tables": {
1732 "original_condition": "(`t1`.`a` > 0)",
1733 "attached_conditions_computation": [
1734 ] /* attached_conditions_computation */,
1735 "attached_conditions_summary": [
1736 {
1737 "table": "`t1`",
1738 "attached": "(`t1`.`a` > 0)"
1739 }
1740 ] /* attached_conditions_summary */
1741 } /* attaching_conditions_to_tables */
1742 },
1743 {
1744 "optimizing_distinct_group_by_order_by": {
1745 } /* optimizing_distinct_group_by_order_by */
1746 },
1747 {
1748 "finalizing_table_conditions": [
1749 {
1750 "table": "`t1`",
1751 "original_table_condition": "(`t1`.`a` > 0)",
1752 "final_table_condition ": "(`t1`.`a` > 0)"
1753 }
1754 ] /* finalizing_table_conditions */
1755 },
1756 {
1757 "refine_plan": [
1758 {
1759 "table": "`t1`"
1760 }
1761 ] /* refine_plan */
1762 },
1763 {
1764 "considering_tmp_tables": [
1765 ] /* considering_tmp_tables */
1766 }
1767 ] /* steps */
1768 } /* join_optimization */
1769 },
1770 {
1771 "join_execution": {
1772 "select#": 2,
1773 "steps": [
1774 ] /* steps */
1775 } /* join_execution */
1776 }
1777 ] /* steps */
1778 } /* subselect_execution */
1779 }
1780 ] /* steps */
1781 } 0 0
1782 set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1)) {
1783 "steps": [
1784 {
1785 "join_preparation": {
1786 "select#": 2,
1787 "steps": [
1788 {
1789 "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 1)"
1790 }
1791 ] /* steps */
1792 } /* join_preparation */
1793 },
1794 {
1795 "subselect_execution": {
1796 "select#": 2,
1797 "steps": [
1798 {
1799 "join_optimization": {
1800 "select#": 2,
1801 "steps": [
1802 {
1803 "condition_processing": {
1804 "condition": "WHERE",
1805 "original_condition": "(`t1`.`a` > 1)",
1806 "steps": [
1807 {
1808 "transformation": "equality_propagation",
1809 "resulting_condition": "(`t1`.`a` > 1)"
1810 },
1811 {
1812 "transformation": "constant_propagation",
1813 "resulting_condition": "(`t1`.`a` > 1)"
1814 },
1815 {
1816 "transformation": "trivial_condition_removal",
1817 "resulting_condition": "(`t1`.`a` > 1)"
1818 }
1819 ] /* steps */
1820 } /* condition_processing */
1821 },
1822 {
1823 "substitute_generated_columns": {
1824 } /* substitute_generated_columns */
1825 },
1826 {
1827 "table_dependencies": [
1828 {
1829 "table": "`t1`",
1830 "row_may_be_null": false,
1831 "map_bit": 0,
1832 "depends_on_map_bits": [
1833 ] /* depends_on_map_bits */
1834 }
1835 ] /* table_dependencies */
1836 },
1837 {
1838 "ref_optimizer_key_uses": [
1839 ] /* ref_optimizer_key_uses */
1840 },
1841 {
1842 "rows_estimation": [
1843 {
1844 "table": "`t1`",
1845 "table_scan": {
1846 "rows": 3,
1847 "cost": 0.501282
1848 } /* table_scan */
1849 }
1850 ] /* rows_estimation */
1851 },
1852 {
1853 "considered_execution_plans": [
1854 {
1855 "plan_prefix": [
1856 ] /* plan_prefix */,
1857 "table": "`t1`",
1858 "best_access_path": {
1859 "considered_access_paths": [
1860 {
1861 "rows_to_scan": 3,
1862 "filtering_effect": [
1863 ] /* filtering_effect */,
1864 "final_filtering_effect": 0.333333,
1865 "access_type": "scan",
1866 "resulting_rows": 1,
1867 "cost": 0.801282,
1868 "chosen": true
1869 }
1870 ] /* considered_access_paths */
1871 } /* best_access_path */,
1872 "condition_filtering_pct": 100,
1873 "rows_for_plan": 1,
1874 "cost_for_plan": 0.801282,
1875 "chosen": true
1876 }
1877 ] /* considered_execution_plans */
1878 },
1879 {
1880 "attaching_conditions_to_tables": {
1881 "original_condition": "(`t1`.`a` > 1)",
1882 "attached_conditions_computation": [
1883 ] /* attached_conditions_computation */,
1884 "attached_conditions_summary": [
1885 {
1886 "table": "`t1`",
1887 "attached": "(`t1`.`a` > 1)"
1888 }
1889 ] /* attached_conditions_summary */
1890 } /* attaching_conditions_to_tables */
1891 },
1892 {
1893 "optimizing_distinct_group_by_order_by": {
1894 } /* optimizing_distinct_group_by_order_by */
1895 },
1896 {
1897 "finalizing_table_conditions": [
1898 {
1899 "table": "`t1`",
1900 "original_table_condition": "(`t1`.`a` > 1)",
1901 "final_table_condition ": "(`t1`.`a` > 1)"
1902 }
1903 ] /* finalizing_table_conditions */
1904 },
1905 {
1906 "refine_plan": [
1907 {
1908 "table": "`t1`"
1909 }
1910 ] /* refine_plan */
1911 },
1912 {
1913 "considering_tmp_tables": [
1914 ] /* considering_tmp_tables */
1915 }
1916 ] /* steps */
1917 } /* join_optimization */
1918 },
1919 {
1920 "join_execution": {
1921 "select#": 2,
1922 "steps": [
1923 ] /* steps */
1924 } /* join_execution */
1925 }
1926 ] /* steps */
1927 } /* subselect_execution */
1928 }
1929 ] /* steps */
1930 } 0 0
1931 jump_if_not_case_when 11(15) (case_expr@0 = 2) {
1932 "steps": [
1933 ] /* steps */
1934 } 0 0
1935 SET @b=2 {
1936 "steps": [
1937 ] /* steps */
1938 } 0 0
1939 select * from t1;
1940 a
1941 1
1942 2
1943 3
1944 select @a,@b;
1945 @a @b
1946 3 2
1947 set optimizer_trace_max_mem_size=@old_max;
1948 drop procedure p1;
1949 drop table t1;
1950
1951 # Test of tracing of DO.
1952
1953 set optimizer_trace_offset=0, optimizer_trace_limit=100;
1954 do (select 42);
1955 select * from information_schema.OPTIMIZER_TRACE;
1956 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
1957 do (select 42) {
1958 "steps": [
1959 {
1960 "join_preparation": {
1961 "select#": 1,
1962 "steps": [
1963 {
1964 "join_preparation": {
1965 "select#": 2,
1966 "steps": [
1967 {
1968 "expanded_query": "/* select#2 */ select 42"
1969 }
1970 ] /* steps */
1971 } /* join_preparation */
1972 },
1973 {
1974 "expanded_query": "/* select#1 */ select 42 AS `(select 42)`"
1975 }
1976 ] /* steps */
1977 } /* join_preparation */
1978 }
1979 ] /* steps */
1980 } 0 0
1981 do (select 42) {
1982 "steps": [
1983 {
1984 "join_optimization": {
1985 "select#": 1,
1986 "steps": [
1987 ] /* steps */
1988 } /* join_optimization */
1989 },
1990 {
1991 "join_execution": {
1992 "select#": 1,
1993 "steps": [
1994 ] /* steps */
1995 } /* join_execution */
1996 }
1997 ] /* steps */
1998 } 0 0
1999
2000 # Test of tracing of subquery used in parameter of routine call
2001
2002 create table t1(a int);
2003 insert into t1 values(1),(2);
2004 create procedure p1(x int)
2005 begin
2006 declare b int;
2007 set b=(select 2+x from dual);
2008 end|
2009 set optimizer_trace_offset=0, optimizer_trace_limit=100;
2010 call p1((select a from t1 limit 1));
2011 select * from information_schema.OPTIMIZER_TRACE;
2012 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2013 call p1((select a from t1 limit 1)) {
2014 "steps": [
2015 {
2016 "join_preparation": {
2017 "select#": 2,
2018 "steps": [
2019 {
2020 "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` limit 1"
2021 }
2022 ] /* steps */
2023 } /* join_preparation */
2024 }
2025 ] /* steps */
2026 } 0 0
2027 call p1((select a from t1 limit 1)) {
2028 "steps": [
2029 {
2030 "subselect_execution": {
2031 "select#": 2,
2032 "steps": [
2033 {
2034 "join_optimization": {
2035 "select#": 2,
2036 "steps": [
2037 {
2038 "table_dependencies": [
2039 {
2040 "table": "`t1`",
2041 "row_may_be_null": false,
2042 "map_bit": 0,
2043 "depends_on_map_bits": [
2044 ] /* depends_on_map_bits */
2045 }
2046 ] /* table_dependencies */
2047 },
2048 {
2049 "rows_estimation": [
2050 {
2051 "table": "`t1`",
2052 "table_scan": {
2053 "rows": 2,
2054 "cost": 0.500854
2055 } /* table_scan */
2056 }
2057 ] /* rows_estimation */
2058 },
2059 {
2060 "considered_execution_plans": [
2061 {
2062 "plan_prefix": [
2063 ] /* plan_prefix */,
2064 "table": "`t1`",
2065 "best_access_path": {
2066 "considered_access_paths": [
2067 {
2068 "rows_to_scan": 2,
2069 "access_type": "scan",
2070 "resulting_rows": 2,
2071 "cost": 0.700854,
2072 "chosen": true
2073 }
2074 ] /* considered_access_paths */
2075 } /* best_access_path */,
2076 "condition_filtering_pct": 100,
2077 "rows_for_plan": 2,
2078 "cost_for_plan": 0.700854,
2079 "chosen": true
2080 }
2081 ] /* considered_execution_plans */
2082 },
2083 {
2084 "attaching_conditions_to_tables": {
2085 "original_condition": null,
2086 "attached_conditions_computation": [
2087 ] /* attached_conditions_computation */,
2088 "attached_conditions_summary": [
2089 {
2090 "table": "`t1`",
2091 "attached": null
2092 }
2093 ] /* attached_conditions_summary */
2094 } /* attaching_conditions_to_tables */
2095 },
2096 {
2097 "finalizing_table_conditions": [
2098 ] /* finalizing_table_conditions */
2099 },
2100 {
2101 "refine_plan": [
2102 {
2103 "table": "`t1`"
2104 }
2105 ] /* refine_plan */
2106 }
2107 ] /* steps */
2108 } /* join_optimization */
2109 },
2110 {
2111 "join_execution": {
2112 "select#": 2,
2113 "steps": [
2114 ] /* steps */
2115 } /* join_execution */
2116 }
2117 ] /* steps */
2118 } /* subselect_execution */
2119 }
2120 ] /* steps */
2121 } 0 0
2122 set b@1 NULL {
2123 "steps": [
2124 ] /* steps */
2125 } 0 0
2126 set b@1 (select (2 + x@0)) {
2127 "steps": [
2128 {
2129 "join_preparation": {
2130 "select#": 2,
2131 "steps": [
2132 {
2133 "expanded_query": "/* select#2 */ select (2 + x@0)"
2134 }
2135 ] /* steps */
2136 } /* join_preparation */
2137 }
2138 ] /* steps */
2139 } 0 0
2140 drop procedure p1;
2141 drop table t1;
2142 set optimizer_trace_offset=default, optimizer_trace_limit=default;
2143
2144 # Test that printing expanded query does not alter query's
2145 # results.
2146 # Comes from ctype_utf8mb4_heap.test
2147
2148 create table t1 (f1 varchar(1) not null) default charset utf8mb4;
2149 insert into t1 values (''), ('');
2150 select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
2151 concat(concat(_latin1'->',f1),_latin1'<-')
2152 -><-
2153 -><-
2154 select * from information_schema.optimizer_trace;
2155 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2156 select concat(concat(_latin1'->',f1),_latin1'<-') from t1 {
2157 "steps": [
2158 {
2159 "join_optimization": {
2160 "select#": 1,
2161 "steps": [
2162 {
2163 "table_dependencies": [
2164 {
2165 "table": "`t1`",
2166 "row_may_be_null": false,
2167 "map_bit": 0,
2168 "depends_on_map_bits": [
2169 ] /* depends_on_map_bits */
2170 }
2171 ] /* table_dependencies */
2172 },
2173 {
2174 "rows_estimation": [
2175 {
2176 "table": "`t1`",
2177 "table_scan": {
2178 "rows": 2,
2179 "cost": 0.502441
2180 } /* table_scan */
2181 }
2182 ] /* rows_estimation */
2183 },
2184 {
2185 "considered_execution_plans": [
2186 {
2187 "plan_prefix": [
2188 ] /* plan_prefix */,
2189 "table": "`t1`",
2190 "best_access_path": {
2191 "considered_access_paths": [
2192 {
2193 "rows_to_scan": 2,
2194 "access_type": "scan",
2195 "resulting_rows": 2,
2196 "cost": 0.702441,
2197 "chosen": true
2198 }
2199 ] /* considered_access_paths */
2200 } /* best_access_path */,
2201 "condition_filtering_pct": 100,
2202 "rows_for_plan": 2,
2203 "cost_for_plan": 0.702441,
2204 "chosen": true
2205 }
2206 ] /* considered_execution_plans */
2207 },
2208 {
2209 "attaching_conditions_to_tables": {
2210 "original_condition": null,
2211 "attached_conditions_computation": [
2212 ] /* attached_conditions_computation */,
2213 "attached_conditions_summary": [
2214 {
2215 "table": "`t1`",
2216 "attached": null
2217 }
2218 ] /* attached_conditions_summary */
2219 } /* attaching_conditions_to_tables */
2220 },
2221 {
2222 "finalizing_table_conditions": [
2223 ] /* finalizing_table_conditions */
2224 },
2225 {
2226 "refine_plan": [
2227 {
2228 "table": "`t1`"
2229 }
2230 ] /* refine_plan */
2231 }
2232 ] /* steps */
2233 } /* join_optimization */
2234 },
2235 {
2236 "join_execution": {
2237 "select#": 1,
2238 "steps": [
2239 ] /* steps */
2240 } /* join_execution */
2241 }
2242 ] /* steps */
2243 } 0 0
2244 drop table t1;
2245
2246 # Bug#12546331 - SEGFAULT IN SUBSELECT_INDEXSUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
2247
2248 CREATE TABLE t1 (
2249 col_int_nokey INT,
2250 col_int_key INT,
2251 col_varchar_key varchar(1),
2252 KEY col_int_key (col_int_key),
2253 KEY col_varchar_key (col_varchar_key,col_int_key)
2254 );
2255 INSERT INTO t1 VALUES
2256 (NULL,8,'x'),
2257 (8,7,'d'),
2258 (1,1,'r'),
2259 (9,7,'f'),
2260 (4,9,'y'),
2261 (3,NULL,'u'),
2262 (2,1,'m'),
2263 (NULL,9,NULL),
2264 (2,2,'o'),
2265 (NULL,9,'w'),
2266 (6,2,'m'),
2267 (7,4,'q'),
2268 (2,0,NULL),
2269 (5,4,'d'),
2270 (7,8,'g'),
2271 (6,NULL,'x'),
2272 (6,NULL,'f'),
2273 (2,0,'p'),
2274 (9,NULL,'j'),
2275 (6,8,'c')
2276 ;
2277 CREATE TABLE t2 (
2278 col_int_nokey INT,
2279 col_int_key INT,
2280 col_varchar_key varchar(1),
2281 KEY col_int_key (col_int_key),
2282 KEY col_varchar_key (col_varchar_key,col_int_key)
2283 );
2284 INSERT INTO t2 VALUES
2285 (2,4,'v'),
2286 (150,62,'v'),
2287 (NULL,7,'c'),
2288 (2,1,NULL),
2289 (5,0,'x'),
2290 (3,7,'i'),
2291 (1,7,'e'),
2292 (4,1,'p'),
2293 (NULL,7,'s'),
2294 (2,1,'j'),
2295 (6,5,'z'),
2296 (6,2,'c'),
2297 (8,0,'a'),
2298 (2,1,'q'),
2299 (6,8,'y'),
2300 (8,1,NULL),
2301 (3,1,'r'),
2302 (3,9,'v'),
2303 (9,1,NULL),
2304 (6,5,'r')
2305 ;
2306 SELECT col_int_nokey
2307 FROM (
2308 SELECT *
2309 FROM t2
2310 WHERE col_varchar_key > 'a'
2311 OR ( 7 , 5 ) NOT IN (
2312 SELECT col_int_nokey , col_int_key
2313 FROM t1 )
2314 ) AS alias1;
2315 col_int_nokey
2316 2
2317 150
2318 NULL
2319 2
2320 5
2321 3
2322 1
2323 4
2324 NULL
2325 2
2326 6
2327 6
2328 8
2329 2
2330 6
2331 8
2332 3
2333 3
2334 9
2335 6
2336 DROP TABLE t1;
2337 DROP TABLE t2;
2338
2339 BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT
2340
2341 CREATE TABLE t1 (
2342 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
2343 col_int_key INT,
2344 KEY col_int_key (col_int_key)
2345 );
2346 CREATE TABLE t2 (
2347 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
2348 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
2349 col_int_key INT,
2350 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
2351 KEY col_int_key (col_int_key)
2352 );
2353 Warnings:
2354 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.
2355 INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
2356 CREATE TABLE t3 (
2357 col_int_key INT,
2358 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
2359 col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 DEFAULT NULL,
2360 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
2361 KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
2362 );
2363 Warnings:
2364 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.
2365 INSERT INTO t3 VALUES (0,'s','it');
2366 INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
2367 SELECT table2.col_int_key
2368 FROM t3 AS table1
2369 LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
2370 LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
2371 table3.col_varchar_10_utf8_key
2372 ;
2373 col_int_key
2374 NULL
2375 NULL
2376 select * from information_schema.optimizer_trace;
2377 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2378 SELECT table2.col_int_key
2379 FROM t3 AS table1
2380 LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
2381 LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
2382 table3.col_varchar_10_utf8_key {
2383 "steps": [
2384 {
2385 "join_optimization": {
2386 "select#": 1,
2387 "steps": [
2388 {
2389 "condition_processing": {
2390 "condition": "WHERE",
2391 "original_condition": null,
2392 "steps": [
2393 {
2394 "transformation": "equality_propagation",
2395 "resulting_condition": null
2396 }
2397 ] /* steps */
2398 } /* condition_processing */
2399 },
2400 {
2401 "table_dependencies": [
2402 {
2403 "table": "`t3` `table1`",
2404 "row_may_be_null": false,
2405 "map_bit": 0,
2406 "depends_on_map_bits": [
2407 ] /* depends_on_map_bits */
2408 },
2409 {
2410 "table": "`t1` `table2`",
2411 "row_may_be_null": true,
2412 "map_bit": 1,
2413 "depends_on_map_bits": [
2414 0
2415 ] /* depends_on_map_bits */
2416 },
2417 {
2418 "table": "`t2` `table3`",
2419 "row_may_be_null": true,
2420 "map_bit": 2,
2421 "depends_on_map_bits": [
2422 0,
2423 1
2424 ] /* depends_on_map_bits */
2425 }
2426 ] /* table_dependencies */
2427 },
2428 {
2429 "ref_optimizer_key_uses": [
2430 ] /* ref_optimizer_key_uses */
2431 },
2432 {
2433 "rows_estimation": [
2434 {
2435 "table": "`t3` `table1`",
2436 "table_scan": {
2437 "rows": 2,
2438 "cost": 0.502686
2439 } /* table_scan */
2440 },
2441 {
2442 "table": "`t1` `table2`",
2443 "rows": 1,
2444 "cost": 1,
2445 "table_type": "system",
2446 "empty": true
2447 },
2448 {
2449 "table": "`t2` `table3`",
2450 "range_analysis": {
2451 "table_scan": {
2452 "rows": 1,
2453 "cost": 2.70122
2454 } /* table_scan */,
2455 "potential_range_indexes": [
2456 {
2457 "index": "col_varchar_10_utf8_key",
2458 "usable": true,
2459 "key_parts": [
2460 "col_varchar_10_utf8_key"
2461 ] /* key_parts */
2462 },
2463 {
2464 "index": "col_int_key",
2465 "usable": false,
2466 "cause": "not_applicable"
2467 }
2468 ] /* potential_range_indexes */,
2469 "best_covering_index_scan": {
2470 "index": "col_varchar_10_utf8_key",
2471 "cost": 0.35,
2472 "chosen": true
2473 } /* best_covering_index_scan */,
2474 "setup_range_conditions": [
2475 {
2476 "impossible_condition": {
2477 "cause": "comparison_with_null_always_false"
2478 } /* impossible_condition */
2479 }
2480 ] /* setup_range_conditions */,
2481 "impossible_range": true
2482 } /* range_analysis */,
2483 "returning_empty_null_row": true,
2484 "cause": "always_false_outer_join_condition"
2485 }
2486 ] /* rows_estimation */
2487 },
2488 {
2489 "considered_execution_plans": [
2490 {
2491 "plan_prefix": [
2492 "`t1` `table2`",
2493 "`t2` `table3`"
2494 ] /* plan_prefix */,
2495 "table": "`t3` `table1`",
2496 "best_access_path": {
2497 "considered_access_paths": [
2498 {
2499 "rows_to_scan": 2,
2500 "access_type": "scan",
2501 "resulting_rows": 2,
2502 "cost": 0.702686,
2503 "chosen": true
2504 }
2505 ] /* considered_access_paths */
2506 } /* best_access_path */,
2507 "condition_filtering_pct": 100,
2508 "rows_for_plan": 2,
2509 "cost_for_plan": 0.702686,
2510 "chosen": true
2511 }
2512 ] /* considered_execution_plans */
2513 },
2514 {
2515 "condition_on_constant_tables": "true",
2516 "condition_value": true
2517 },
2518 {
2519 "attaching_conditions_to_tables": {
2520 "original_condition": "true",
2521 "attached_conditions_computation": [
2522 ] /* attached_conditions_computation */,
2523 "attached_conditions_summary": [
2524 {
2525 "table": "`t3` `table1`",
2526 "attached": null
2527 }
2528 ] /* attached_conditions_summary */
2529 } /* attaching_conditions_to_tables */
2530 },
2531 {
2532 "finalizing_table_conditions": [
2533 ] /* finalizing_table_conditions */
2534 },
2535 {
2536 "refine_plan": [
2537 {
2538 "table": "`t3` `table1`"
2539 }
2540 ] /* refine_plan */
2541 }
2542 ] /* steps */
2543 } /* join_optimization */
2544 },
2545 {
2546 "join_execution": {
2547 "select#": 1,
2548 "steps": [
2549 ] /* steps */
2550 } /* join_execution */
2551 }
2552 ] /* steps */
2553 } 0 0
2554 DROP TABLE t1,t2,t3;
2555
2556 Tests of tracing of the "eq_ref optimization" of plan search
2557
2558 create table t0 (a int);
2559 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2560 create table t1 (a int, b int, key(a));
2561 create table t2 (a int, b int, key(a));
2562 create table t3 (a int, b int, key(a));
2563 insert into t1 select a,a from t0;
2564 insert into t2 select a,a from t0;
2565 insert into t3 select a,a from t0;
2566 set @old_opt_switch=@@optimizer_switch;
2567 explain select *
2568 from t0 where a in
2569 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
2570 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2571 1 PRIMARY t0 NULL ALL NULL NULL NULL NULL 10 100.00 Using where
2572 2 DEPENDENT SUBQUERY t1 NULL index a a 5 NULL 10 100.00 Using where; Using index
2573 2 DEPENDENT SUBQUERY t2 NULL ref a a 5 test.t1.a 1 100.00 Using index
2574 2 DEPENDENT SUBQUERY t3 NULL ref a a 5 test.t1.a 1 100.00 Using index
2575 Warnings:
2576 Note 1003 /* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select (`test`.`t1`.`a` + `test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))))
2577 select * from information_schema.optimizer_trace;
2578 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
2579 explain select *
2580 from t0 where a in
2581 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a) {
2582 "steps": [
2583 {
2584 "join_preparation": {
2585 "select#": 1,
2586 "steps": [
2587 {
2588 "join_preparation": {
2589 "select#": 2,
2590 "steps": [
2591 {
2592 "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2593 },
2594 {
2595 "transformation": {
2596 "select#": 2,
2597 "from": "IN (SELECT)",
2598 "to": "EXISTS (CORRELATED SELECT)",
2599 "chosen": true,
2600 "evaluating_constant_where_conditions": [
2601 ] /* evaluating_constant_where_conditions */
2602 } /* transformation */
2603 }
2604 ] /* steps */
2605 } /* join_preparation */
2606 },
2607 {
2608 "expanded_query": "/* select#1 */ select `t0`.`a` AS `a` from `t0` where <in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`)))) where (<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`))))"
2609 },
2610 {
2611 "transformations_to_nested_joins": {
2612 "transformations": [
2613 "outer_join_to_inner_join",
2614 "JOIN_condition_to_WHERE",
2615 "parenthesis_removal"
2616 ] /* transformations */,
2617 "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))"
2618 } /* transformations_to_nested_joins */
2619 }
2620 ] /* steps */
2621 } /* join_preparation */
2622 },
2623 {
2624 "join_optimization": {
2625 "select#": 1,
2626 "steps": [
2627 {
2628 "condition_processing": {
2629 "condition": "WHERE",
2630 "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
2631 "steps": [
2632 {
2633 "transformation": "equality_propagation",
2634 "subselect_evaluation": [
2635 ] /* subselect_evaluation */,
2636 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2637 },
2638 {
2639 "transformation": "constant_propagation",
2640 "subselect_evaluation": [
2641 ] /* subselect_evaluation */,
2642 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2643 },
2644 {
2645 "transformation": "trivial_condition_removal",
2646 "subselect_evaluation": [
2647 ] /* subselect_evaluation */,
2648 "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2649 }
2650 ] /* steps */
2651 } /* condition_processing */
2652 },
2653 {
2654 "substitute_generated_columns": {
2655 } /* substitute_generated_columns */
2656 },
2657 {
2658 "table_dependencies": [
2659 {
2660 "table": "`t0`",
2661 "row_may_be_null": false,
2662 "map_bit": 0,
2663 "depends_on_map_bits": [
2664 ] /* depends_on_map_bits */
2665 }
2666 ] /* table_dependencies */
2667 },
2668 {
2669 "ref_optimizer_key_uses": [
2670 ] /* ref_optimizer_key_uses */
2671 },
2672 {
2673 "rows_estimation": [
2674 {
2675 "table": "`t0`",
2676 "table_scan": {
2677 "rows": 10,
2678 "cost": 0.504272
2679 } /* table_scan */
2680 }
2681 ] /* rows_estimation */
2682 },
2683 {
2684 "considered_execution_plans": [
2685 {
2686 "plan_prefix": [
2687 ] /* plan_prefix */,
2688 "table": "`t0`",
2689 "best_access_path": {
2690 "considered_access_paths": [
2691 {
2692 "rows_to_scan": 10,
2693 "filtering_effect": [
2694 ] /* filtering_effect */,
2695 "final_filtering_effect": 1,
2696 "access_type": "scan",
2697 "resulting_rows": 10,
2698 "cost": 1.50427,
2699 "chosen": true
2700 }
2701 ] /* considered_access_paths */
2702 } /* best_access_path */,
2703 "condition_filtering_pct": 100,
2704 "rows_for_plan": 10,
2705 "cost_for_plan": 1.50427,
2706 "chosen": true
2707 }
2708 ] /* considered_execution_plans */
2709 },
2710 {
2711 "attaching_conditions_to_tables": {
2712 "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
2713 "attached_conditions_computation": [
2714 ] /* attached_conditions_computation */,
2715 "attached_conditions_summary": [
2716 {
2717 "table": "`t0`",
2718 "attached": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2719 }
2720 ] /* attached_conditions_summary */
2721 } /* attaching_conditions_to_tables */
2722 },
2723 {
2724 "finalizing_table_conditions": [
2725 {
2726 "table": "`t0`",
2727 "original_table_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
2728 "final_table_condition ": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
2729 }
2730 ] /* finalizing_table_conditions */
2731 },
2732 {
2733 "refine_plan": [
2734 {
2735 "table": "`t0`"
2736 }
2737 ] /* refine_plan */
2738 }
2739 ] /* steps */
2740 } /* join_optimization */
2741 },
2742 {
2743 "join_optimization": {
2744 "select#": 2,
2745 "steps": [
2746 {
2747 "condition_processing": {
2748 "condition": "WHERE",
2749 "original_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))",
2750 "steps": [
2751 {
2752 "transformation": "equality_propagation",
2753 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
2754 },
2755 {
2756 "transformation": "constant_propagation",
2757 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
2758 },
2759 {
2760 "transformation": "trivial_condition_removal",
2761 "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
2762 }
2763 ] /* steps */
2764 } /* condition_processing */
2765 },
2766 {
2767 "substitute_generated_columns": {
2768 } /* substitute_generated_columns */
2769 },
2770 {
2771 "table_dependencies": [
2772 {
2773 "table": "`t1`",
2774 "row_may_be_null": false,
2775 "map_bit": 0,
2776 "depends_on_map_bits": [
2777 ] /* depends_on_map_bits */
2778 },
2779 {
2780 "table": "`t2`",
2781 "row_may_be_null": true,
2782 "map_bit": 1,
2783 "depends_on_map_bits": [
2784 ] /* depends_on_map_bits */
2785 },
2786 {
2787 "table": "`t3`",
2788 "row_may_be_null": true,
2789 "map_bit": 2,
2790 "depends_on_map_bits": [
2791 ] /* depends_on_map_bits */
2792 }
2793 ] /* table_dependencies */
2794 },
2795 {
2796 "ref_optimizer_key_uses": [
2797 {
2798 "table": "`t1`",
2799 "field": "a",
2800 "equals": "`t2`.`a`",
2801 "null_rejecting": true
2802 },
2803 {
2804 "table": "`t1`",
2805 "field": "a",
2806 "equals": "`t3`.`a`",
2807 "null_rejecting": true
2808 },
2809 {
2810 "table": "`t2`",
2811 "field": "a",
2812 "equals": "`t1`.`a`",
2813 "null_rejecting": true
2814 },
2815 {
2816 "table": "`t2`",
2817 "field": "a",
2818 "equals": "`t3`.`a`",
2819 "null_rejecting": true
2820 },
2821 {
2822 "table": "`t3`",
2823 "field": "a",
2824 "equals": "`t2`.`a`",
2825 "null_rejecting": true
2826 },
2827 {
2828 "table": "`t3`",
2829 "field": "a",
2830 "equals": "`t1`.`a`",
2831 "null_rejecting": true
2832 }
2833 ] /* ref_optimizer_key_uses */
2834 },
2835 {
2836 "rows_estimation": [
2837 {
2838 "table": "`t1`",
2839 "table_scan": {
2840 "rows": 10,
2841 "cost": 0.505493
2842 } /* table_scan */
2843 },
2844 {
2845 "table": "`t2`",
2846 "table_scan": {
2847 "rows": 10,
2848 "cost": 0.505493
2849 } /* table_scan */
2850 },
2851 {
2852 "table": "`t3`",
2853 "table_scan": {
2854 "rows": 10,
2855 "cost": 0.505493
2856 } /* table_scan */
2857 }
2858 ] /* rows_estimation */
2859 },
2860 {
2861 "considered_execution_plans": [
2862 {
2863 "plan_prefix": [
2864 ] /* plan_prefix */,
2865 "table": "`t1`",
2866 "best_access_path": {
2867 "considered_access_paths": [
2868 {
2869 "access_type": "ref",
2870 "index": "a",
2871 "usable": false,
2872 "chosen": false
2873 },
2874 {
2875 "rows_to_scan": 10,
2876 "filtering_effect": [
2877 ] /* filtering_effect */,
2878 "final_filtering_effect": 1,
2879 "access_type": "scan",
2880 "resulting_rows": 10,
2881 "cost": 1.50549,
2882 "chosen": true
2883 }
2884 ] /* considered_access_paths */
2885 } /* best_access_path */,
2886 "condition_filtering_pct": 100,
2887 "rows_for_plan": 10,
2888 "cost_for_plan": 1.50549,
2889 "rest_of_plan": [
2890 {
2891 "plan_prefix": [
2892 "`t1`"
2893 ] /* plan_prefix */,
2894 "table": "`t2`",
2895 "best_access_path": {
2896 "considered_access_paths": [
2897 {
2898 "access_type": "ref",
2899 "index": "a",
2900 "rows": 1,
2901 "cost": 3.5,
2902 "chosen": true
2903 },
2904 {
2905 "rows_to_scan": 10,
2906 "filtering_effect": [
2907 ] /* filtering_effect */,
2908 "final_filtering_effect": 1,
2909 "access_type": "scan",
2910 "using_join_cache": true,
2911 "buffers_needed": 1,
2912 "resulting_rows": 10,
2913 "cost": 10.5056,
2914 "chosen": false
2915 }
2916 ] /* considered_access_paths */
2917 } /* best_access_path */,
2918 "condition_filtering_pct": 100,
2919 "rows_for_plan": 10,
2920 "cost_for_plan": 5.00549,
2921 "rest_of_plan": [
2922 {
2923 "plan_prefix": [
2924 "`t1`",
2925 "`t2`"
2926 ] /* plan_prefix */,
2927 "table": "`t3`",
2928 "best_access_path": {
2929 "considered_access_paths": [
2930 {
2931 "access_type": "ref",
2932 "index": "a",
2933 "rows": 1,
2934 "cost": 3.5,
2935 "chosen": true
2936 },
2937 {
2938 "rows_to_scan": 10,
2939 "filtering_effect": [
2940 ] /* filtering_effect */,
2941 "final_filtering_effect": 1,
2942 "access_type": "scan",
2943 "using_join_cache": true,
2944 "buffers_needed": 1,
2945 "resulting_rows": 10,
2946 "cost": 10.5057,
2947 "chosen": false
2948 }
2949 ] /* considered_access_paths */
2950 } /* best_access_path */,
2951 "added_to_eq_ref_extension": true,
2952 "condition_filtering_pct": 100,
2953 "rows_for_plan": 10,
2954 "cost_for_plan": 8.50549,
2955 "chosen": true
2956 }
2957 ] /* rest_of_plan */
2958 }
2959 ] /* rest_of_plan */
2960 },
2961 {
2962 "plan_prefix": [
2963 ] /* plan_prefix */,
2964 "table": "`t2`",
2965 "best_access_path": {
2966 "considered_access_paths": [
2967 {
2968 "access_type": "ref",
2969 "index": "a",
2970 "usable": false,
2971 "chosen": false
2972 },
2973 {
2974 "rows_to_scan": 10,
2975 "filtering_effect": [
2976 ] /* filtering_effect */,
2977 "final_filtering_effect": 1,
2978 "access_type": "scan",
2979 "resulting_rows": 10,
2980 "cost": 1.50549,
2981 "chosen": true
2982 }
2983 ] /* considered_access_paths */
2984 } /* best_access_path */,
2985 "condition_filtering_pct": 100,
2986 "rows_for_plan": 10,
2987 "cost_for_plan": 1.50549,
2988 "rest_of_plan": [
2989 {
2990 "plan_prefix": [
2991 "`t2`"
2992 ] /* plan_prefix */,
2993 "table": "`t1`",
2994 "best_access_path": {
2995 "considered_access_paths": [
2996 {
2997 "access_type": "ref",
2998 "index": "a",
2999 "rows": 1,
3000 "cost": 3.5,
3001 "chosen": true
3002 },
3003 {
3004 "rows_to_scan": 10,
3005 "filtering_effect": [
3006 ] /* filtering_effect */,
3007 "final_filtering_effect": 1,
3008 "access_type": "scan",
3009 "using_join_cache": true,
3010 "buffers_needed": 1,
3011 "resulting_rows": 10,
3012 "cost": 10.5056,
3013 "chosen": false
3014 }
3015 ] /* considered_access_paths */
3016 } /* best_access_path */,
3017 "condition_filtering_pct": 100,
3018 "rows_for_plan": 10,
3019 "cost_for_plan": 5.00549,
3020 "rest_of_plan": [
3021 {
3022 "plan_prefix": [
3023 "`t2`",
3024 "`t1`"
3025 ] /* plan_prefix */,
3026 "table": "`t3`",
3027 "best_access_path": {
3028 "considered_access_paths": [
3029 {
3030 "access_type": "ref",
3031 "index": "a",
3032 "rows": 1,
3033 "cost": 3.5,
3034 "chosen": true
3035 },
3036 {
3037 "rows_to_scan": 10,
3038 "filtering_effect": [
3039 ] /* filtering_effect */,
3040 "final_filtering_effect": 1,
3041 "access_type": "scan",
3042 "using_join_cache": true,
3043 "buffers_needed": 1,
3044 "resulting_rows": 10,
3045 "cost": 10.5057,
3046 "chosen": false
3047 }
3048 ] /* considered_access_paths */
3049 } /* best_access_path */,
3050 "added_to_eq_ref_extension": true,
3051 "condition_filtering_pct": 100,
3052 "rows_for_plan": 10,
3053 "cost_for_plan": 8.50549,
3054 "pruned_by_cost": true
3055 },
3056 {
3057 "plan_prefix": [
3058 "`t2`",
3059 "`t1`"
3060 ] /* plan_prefix */,
3061 "table": "`t3`",
3062 "best_access_path": {
3063 "considered_access_paths": [
3064 {
3065 "access_type": "ref",
3066 "index": "a",
3067 "rows": 1,
3068 "cost": 3.5,
3069 "chosen": true
3070 },
3071 {
3072 "rows_to_scan": 10,
3073 "filtering_effect": [
3074 ] /* filtering_effect */,
3075 "final_filtering_effect": 1,
3076 "access_type": "scan",
3077 "using_join_cache": true,
3078 "buffers_needed": 1,
3079 "resulting_rows": 10,
3080 "cost": 10.5057,
3081 "chosen": false
3082 }
3083 ] /* considered_access_paths */
3084 } /* best_access_path */,
3085 "condition_filtering_pct": 100,
3086 "rows_for_plan": 10,
3087 "cost_for_plan": 8.50549,
3088 "pruned_by_cost": true
3089 }
3090 ] /* rest_of_plan */
3091 },
3092 {
3093 "plan_prefix": [
3094 "`t2`"
3095 ] /* plan_prefix */,
3096 "table": "`t3`",
3097 "best_access_path": {
3098 "considered_access_paths": [
3099 {
3100 "access_type": "ref",
3101 "index": "a",
3102 "rows": 1,
3103 "cost": 3.5,
3104 "chosen": true
3105 },
3106 {
3107 "rows_to_scan": 10,
3108 "filtering_effect": [
3109 ] /* filtering_effect */,
3110 "final_filtering_effect": 1,
3111 "access_type": "scan",
3112 "using_join_cache": true,
3113 "buffers_needed": 1,
3114 "resulting_rows": 10,
3115 "cost": 10.5056,
3116 "chosen": false
3117 }
3118 ] /* considered_access_paths */
3119 } /* best_access_path */,
3120 "condition_filtering_pct": 100,
3121 "rows_for_plan": 10,
3122 "cost_for_plan": 5.00549,
3123 "pruned_by_heuristic": true
3124 }
3125 ] /* rest_of_plan */
3126 },
3127 {
3128 "plan_prefix": [
3129 ] /* plan_prefix */,
3130 "table": "`t3`",
3131 "best_access_path": {
3132 "considered_access_paths": [
3133 {
3134 "access_type": "ref",
3135 "index": "a",
3136 "usable": false,
3137 "chosen": false
3138 },
3139 {
3140 "rows_to_scan": 10,
3141 "filtering_effect": [
3142 ] /* filtering_effect */,
3143 "final_filtering_effect": 1,
3144 "access_type": "scan",
3145 "resulting_rows": 10,
3146 "cost": 1.50549,
3147 "chosen": true
3148 }
3149 ] /* considered_access_paths */
3150 } /* best_access_path */,
3151 "condition_filtering_pct": 100,
3152 "rows_for_plan": 10,
3153 "cost_for_plan": 1.50549,
3154 "rest_of_plan": [
3155 {
3156 "plan_prefix": [
3157 "`t3`"
3158 ] /* plan_prefix */,
3159 "table": "`t1`",
3160 "best_access_path": {
3161 "considered_access_paths": [
3162 {
3163 "access_type": "ref",
3164 "index": "a",
3165 "rows": 1,
3166 "cost": 3.5,
3167 "chosen": true
3168 },
3169 {
3170 "rows_to_scan": 10,
3171 "filtering_effect": [
3172 ] /* filtering_effect */,
3173 "final_filtering_effect": 1,
3174 "access_type": "scan",
3175 "using_join_cache": true,
3176 "buffers_needed": 1,
3177 "resulting_rows": 10,
3178 "cost": 10.5056,
3179 "chosen": false
3180 }
3181 ] /* considered_access_paths */
3182 } /* best_access_path */,
3183 "condition_filtering_pct": 100,
3184 "rows_for_plan": 10,
3185 "cost_for_plan": 5.00549,
3186 "rest_of_plan": [
3187 {
3188 "plan_prefix": [
3189 "`t3`",
3190 "`t1`"
3191 ] /* plan_prefix */,
3192 "table": "`t2`",
3193 "best_access_path": {
3194 "considered_access_paths": [
3195 {
3196 "access_type": "ref",
3197 "index": "a",
3198 "rows": 1,
3199 "cost": 3.5,
3200 "chosen": true
3201 },
3202 {
3203 "rows_to_scan": 10,
3204 "filtering_effect": [
3205 ] /* filtering_effect */,
3206 "final_filtering_effect": 1,
3207 "access_type": "scan",
3208 "using_join_cache": true,
3209 "buffers_needed": 1,
3210 "resulting_rows": 10,
3211 "cost": 10.5057,
3212 "chosen": false
3213 }
3214 ] /* considered_access_paths */
3215 } /* best_access_path */,
3216 "added_to_eq_ref_extension": true,
3217 "condition_filtering_pct": 100,
3218 "rows_for_plan": 10,
3219 "cost_for_plan": 8.50549,
3220 "pruned_by_cost": true
3221 },
3222 {
3223 "plan_prefix": [
3224 "`t3`",
3225 "`t1`"
3226 ] /* plan_prefix */,
3227 "table": "`t2`",
3228 "best_access_path": {
3229 "considered_access_paths": [
3230 {
3231 "access_type": "ref",
3232 "index": "a",
3233 "rows": 1,
3234 "cost": 3.5,
3235 "chosen": true
3236 },
3237 {
3238 "rows_to_scan": 10,
3239 "filtering_effect": [
3240 ] /* filtering_effect */,
3241 "final_filtering_effect": 1,
3242 "access_type": "scan",
3243 "using_join_cache": true,
3244 "buffers_needed": 1,
3245 "resulting_rows": 10,
3246 "cost": 10.5057,
3247 "chosen": false
3248 }
3249 ] /* considered_access_paths */
3250 } /* best_access_path */,
3251 "condition_filtering_pct": 100,
3252 "rows_for_plan": 10,
3253 "cost_for_plan": 8.50549,
3254 "pruned_by_cost": true
3255 }
3256 ] /* rest_of_plan */
3257 },
3258 {
3259 "plan_prefix": [
3260 "`t3`"
3261 ] /* plan_prefix */,
3262 "table": "`t2`",
3263 "best_access_path": {
3264 "considered_access_paths": [
3265 {
3266 "access_type": "ref",
3267 "index": "a",
3268 "rows": 1,
3269 "cost": 3.5,
3270 "chosen": true
3271 },
3272 {
3273 "rows_to_scan": 10,
3274 "filtering_effect": [
3275 ] /* filtering_effect */,
3276 "final_filtering_effect": 1,
3277 "access_type": "scan",
3278 "using_join_cache": true,
3279 "buffers_needed": 1,
3280 "resulting_rows": 10,
3281 "cost": 10.5056,
3282 "chosen": false
3283 }
3284 ] /* considered_access_paths */
3285 } /* best_access_path */,
3286 "condition_filtering_pct": 100,
3287 "rows_for_plan": 10,
3288 "cost_for_plan": 5.00549,
3289 "pruned_by_heuristic": true
3290 }
3291 ] /* rest_of_plan */
3292 }
3293 ] /* considered_execution_plans */
3294 },
3295 {
3296 "attaching_conditions_to_tables": {
3297 "original_condition": "((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`) and (<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`)))",
3298 "attached_conditions_computation": [
3299 {
3300 "table": "`t1`",
3301 "rechecking_index_usage": {
3302 "recheck_reason": "not_first_table",
3303 "range_analysis": {
3304 "table_scan": {
3305 "rows": 10,
3306 "cost": 5.5
3307 } /* table_scan */,
3308 "potential_range_indexes": [
3309 {
3310 "index": "a",
3311 "usable": true,
3312 "key_parts": [
3313 "a"
3314 ] /* key_parts */
3315 }
3316 ] /* potential_range_indexes */,
3317 "best_covering_index_scan": {
3318 "index": "a",
3319 "cost": 1.30233,
3320 "chosen": true
3321 } /* best_covering_index_scan */,
3322 "setup_range_conditions": [
3323 ] /* setup_range_conditions */,
3324 "group_index_range": {
3325 "chosen": false,
3326 "cause": "not_single_table"
3327 } /* group_index_range */,
3328 "skip_scan_range": {
3329 "chosen": false,
3330 "cause": "not_single_table"
3331 } /* skip_scan_range */
3332 } /* range_analysis */
3333 } /* rechecking_index_usage */
3334 }
3335 ] /* attached_conditions_computation */,
3336 "attached_conditions_summary": [
3337 {
3338 "table": "`t1`",
3339 "attached": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))"
3340 },
3341 {
3342 "table": "`t2`",
3343 "attached": "(`t2`.`a` = `t1`.`a`)"
3344 },
3345 {
3346 "table": "`t3`",
3347 "attached": "(`t3`.`a` = `t1`.`a`)"
3348 }
3349 ] /* attached_conditions_summary */
3350 } /* attaching_conditions_to_tables */
3351 },
3352 {
3353 "finalizing_table_conditions": [
3354 {
3355 "table": "`t1`",
3356 "original_table_condition": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))",
3357 "final_table_condition ": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))"
3358 },
3359 {
3360 "table": "`t2`",
3361 "original_table_condition": "(`t2`.`a` = `t1`.`a`)",
3362 "final_table_condition ": null
3363 },
3364 {
3365 "table": "`t3`",
3366 "original_table_condition": "(`t3`.`a` = `t1`.`a`)",
3367 "final_table_condition ": null
3368 }
3369 ] /* finalizing_table_conditions */
3370 },
3371 {
3372 "refine_plan": [
3373 {
3374 "table": "`t1`"
3375 },
3376 {
3377 "table": "`t2`"
3378 },
3379 {
3380 "table": "`t3`"
3381 }
3382 ] /* refine_plan */
3383 }
3384 ] /* steps */
3385 } /* join_optimization */
3386 },
3387 {
3388 "join_explain": {
3389 "select#": 1,
3390 "steps": [
3391 {
3392 "join_explain": {
3393 "select#": 2,
3394 "steps": [
3395 ] /* steps */
3396 } /* join_explain */
3397 }
3398 ] /* steps */
3399 } /* join_explain */
3400 }
3401 ] /* steps */
3402 } 0 0
3403 set optimizer_switch=@old_opt_switch;
3404 drop table t0,t1,t2,t3;
3405 CREATE TABLE t1 (
3406 OBJECTID int(11) NOT NULL default '0',
3407 SORTORDER int(11) NOT NULL auto_increment,
3408 KEY t1_SortIndex (SORTORDER),
3409 KEY t1_IdIndex (OBJECTID)
3410 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
3411 Warnings:
3412 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3413 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3414 CREATE TABLE t2 (
3415 ID int(11) default NULL,
3416 PARID int(11) default NULL,
3417 UNIQUE KEY t2_ID_IDX (ID),
3418 KEY t2_PARID_IDX (PARID)
3419 ) engine=MyISAM DEFAULT CHARSET=latin1;
3420 Warnings:
3421 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3422 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3423 INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
3424 CREATE TABLE t3 (
3425 ID int(11) default NULL,
3426 DATA decimal(10,2) default NULL,
3427 UNIQUE KEY t3_ID_IDX (ID)
3428 ) engine=MyISAM DEFAULT CHARSET=latin1;
3429 Warnings:
3430 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3431 INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
3432 select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
3433 497 ID NULL
3434 select * from information_schema.optimizer_trace;
3435 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
3436 select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp {
3437 "steps": [
3438 {
3439 "join_optimization": {
3440 "select#": 1,
3441 "steps": [
3442 {
3443 "join_optimization": {
3444 "select#": 2,
3445 "steps": [
3446 {
3447 "condition_processing": {
3448 "condition": "WHERE",
3449 "original_condition": "((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`))",
3450 "steps": [
3451 {
3452 "transformation": "equality_propagation",
3453 "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
3454 },
3455 {
3456 "transformation": "constant_propagation",
3457 "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
3458 },
3459 {
3460 "transformation": "trivial_condition_removal",
3461 "resulting_condition": "multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`)"
3462 }
3463 ] /* steps */
3464 } /* condition_processing */
3465 },
3466 {
3467 "substitute_generated_columns": {
3468 } /* substitute_generated_columns */
3469 },
3470 {
3471 "table_dependencies": [
3472 {
3473 "table": "`t1`",
3474 "row_may_be_null": false,
3475 "map_bit": 0,
3476 "depends_on_map_bits": [
3477 ] /* depends_on_map_bits */
3478 },
3479 {
3480 "table": "`t2`",
3481 "row_may_be_null": false,
3482 "map_bit": 1,
3483 "depends_on_map_bits": [
3484 ] /* depends_on_map_bits */
3485 },
3486 {
3487 "table": "`t3`",
3488 "row_may_be_null": false,
3489 "map_bit": 2,
3490 "depends_on_map_bits": [
3491 ] /* depends_on_map_bits */
3492 }
3493 ] /* table_dependencies */
3494 },
3495 {
3496 "ref_optimizer_key_uses": [
3497 {
3498 "table": "`t1`",
3499 "field": "OBJECTID",
3500 "equals": "`t3`.`ID`",
3501 "null_rejecting": true
3502 },
3503 {
3504 "table": "`t1`",
3505 "field": "OBJECTID",
3506 "equals": "`t2`.`ID`",
3507 "null_rejecting": true
3508 },
3509 {
3510 "table": "`t2`",
3511 "field": "ID",
3512 "equals": "`t1`.`OBJECTID`",
3513 "null_rejecting": true
3514 },
3515 {
3516 "table": "`t2`",
3517 "field": "ID",
3518 "equals": "`t3`.`ID`",
3519 "null_rejecting": true
3520 },
3521 {
3522 "table": "`t3`",
3523 "field": "ID",
3524 "equals": "`t1`.`OBJECTID`",
3525 "null_rejecting": true
3526 },
3527 {
3528 "table": "`t3`",
3529 "field": "ID",
3530 "equals": "`t2`.`ID`",
3531 "null_rejecting": true
3532 }
3533 ] /* ref_optimizer_key_uses */
3534 },
3535 {
3536 "rows_estimation": [
3537 {
3538 "table": "`t1`",
3539 "rows": 1,
3540 "cost": 1,
3541 "table_type": "system",
3542 "empty": true
3543 },
3544 {
3545 "table": "`t2`",
3546 "const_keys_added": {
3547 "keys": [
3548 "t2_PARID_IDX"
3549 ] /* keys */,
3550 "cause": "group_by"
3551 } /* const_keys_added */,
3552 "range_analysis": {
3553 "table_scan": {
3554 "rows": 9,
3555 "cost": 3.50494
3556 } /* table_scan */,
3557 "potential_range_indexes": [
3558 {
3559 "index": "t2_ID_IDX",
3560 "usable": false,
3561 "cause": "not_applicable"
3562 },
3563 {
3564 "index": "t2_PARID_IDX",
3565 "usable": true,
3566 "key_parts": [
3567 "PARID"
3568 ] /* key_parts */
3569 }
3570 ] /* potential_range_indexes */,
3571 "setup_range_conditions": [
3572 ] /* setup_range_conditions */,
3573 "group_index_range": {
3574 "chosen": false,
3575 "cause": "not_single_table"
3576 } /* group_index_range */,
3577 "skip_scan_range": {
3578 "chosen": false,
3579 "cause": "not_single_table"
3580 } /* skip_scan_range */
3581 } /* range_analysis */
3582 },
3583 {
3584 "table": "`t3`",
3585 "table_scan": {
3586 "rows": 8,
3587 "cost": 0.504883
3588 } /* table_scan */
3589 }
3590 ] /* rows_estimation */
3591 },
3592 {
3593 "considered_execution_plans": [
3594 {
3595 "plan_prefix": [
3596 "`t1`"
3597 ] /* plan_prefix */,
3598 "table": "`t3`",
3599 "best_access_path": {
3600 "considered_access_paths": [
3601 {
3602 "access_type": "eq_ref",
3603 "index": "t3_ID_IDX",
3604 "rows": 1,
3605 "cost": 0.1,
3606 "chosen": true
3607 },
3608 {
3609 "access_type": "scan",
3610 "cost": 1.30488,
3611 "rows": 8,
3612 "chosen": false,
3613 "cause": "cost"
3614 }
3615 ] /* considered_access_paths */
3616 } /* best_access_path */,
3617 "condition_filtering_pct": 100,
3618 "rows_for_plan": 1,
3619 "cost_for_plan": 0.1,
3620 "rest_of_plan": [
3621 {
3622 "plan_prefix": [
3623 "`t1`",
3624 "`t3`"
3625 ] /* plan_prefix */,
3626 "table": "`t2`",
3627 "best_access_path": {
3628 "considered_access_paths": [
3629 {
3630 "access_type": "eq_ref",
3631 "index": "t2_ID_IDX",
3632 "rows": 1,
3633 "cost": 0.1,
3634 "chosen": true
3635 },
3636 {
3637 "access_type": "scan",
3638 "cost": 1.40494,
3639 "rows": 9,
3640 "chosen": false,
3641 "cause": "cost"
3642 }
3643 ] /* considered_access_paths */
3644 } /* best_access_path */,
3645 "added_to_eq_ref_extension": true,
3646 "condition_filtering_pct": 100,
3647 "rows_for_plan": 1,
3648 "cost_for_plan": 0.2,
3649 "chosen": true
3650 }
3651 ] /* rest_of_plan */
3652 }
3653 ] /* considered_execution_plans */
3654 }
3655 ] /* steps */,
3656 "empty_result": {
3657 "cause": "no matching row in const table"
3658 } /* empty_result */
3659 } /* join_optimization */
3660 },
3661 {
3662 "table_dependencies": [
3663 {
3664 "table": " `tmp`",
3665 "row_may_be_null": false,
3666 "map_bit": 0,
3667 "depends_on_map_bits": [
3668 ] /* depends_on_map_bits */
3669 }
3670 ] /* table_dependencies */
3671 },
3672 {
3673 "rows_estimation": [
3674 {
3675 "table": " `tmp`",
3676 "table_scan": {
3677 "rows": 2,
3678 "cost": 2.525
3679 } /* table_scan */
3680 }
3681 ] /* rows_estimation */
3682 },
3683 {
3684 "considered_execution_plans": [
3685 {
3686 "plan_prefix": [
3687 ] /* plan_prefix */,
3688 "table": " `tmp`",
3689 "best_access_path": {
3690 "considered_access_paths": [
3691 {
3692 "rows_to_scan": 2,
3693 "access_type": "scan",
3694 "resulting_rows": 2,
3695 "cost": 2.725,
3696 "chosen": true
3697 }
3698 ] /* considered_access_paths */
3699 } /* best_access_path */,
3700 "condition_filtering_pct": 100,
3701 "rows_for_plan": 2,
3702 "cost_for_plan": 2.725,
3703 "chosen": true
3704 }
3705 ] /* considered_execution_plans */
3706 },
3707 {
3708 "attaching_conditions_to_tables": {
3709 "original_condition": null,
3710 "attached_conditions_computation": [
3711 ] /* attached_conditions_computation */,
3712 "attached_conditions_summary": [
3713 {
3714 "table": " `tmp`",
3715 "attached": null
3716 }
3717 ] /* attached_conditions_summary */
3718 } /* attaching_conditions_to_tables */
3719 },
3720 {
3721 "finalizing_table_conditions": [
3722 ] /* finalizing_table_conditions */
3723 },
3724 {
3725 "refine_plan": [
3726 {
3727 "table": " `tmp`"
3728 }
3729 ] /* refine_plan */
3730 }
3731 ] /* steps */
3732 } /* join_optimization */
3733 },
3734 {
3735 "join_execution": {
3736 "select#": 1,
3737 "steps": [
3738 {
3739 "creating_tmp_table": {
3740 "tmp_table_info": {
3741 "table": "tmp",
3742 "in_plan_at_position": 0,
3743 "columns": 2,
3744 "row_length": 10,
3745 "key_length": 0,
3746 "unique_constraint": false,
3747 "makes_grouped_rows": false,
3748 "cannot_insert_duplicates": false,
3749 "location": "TempTable"
3750 } /* tmp_table_info */
3751 } /* creating_tmp_table */
3752 },
3753 {
3754 "materialize": {
3755 "select#": 2,
3756 "steps": [
3757 ] /* steps */
3758 } /* materialize */
3759 }
3760 ] /* steps */
3761 } /* join_execution */
3762 }
3763 ] /* steps */
3764 } 0 0
3765 drop table t1,t2,t3;
3766 CREATE TABLE t1 (
3767 t1_id bigint(21) NOT NULL auto_increment,
3768 _field_72 varchar(128) DEFAULT '' NOT NULL,
3769 _field_95 varchar(32),
3770 _field_115 tinyint(4) DEFAULT '0' NOT NULL,
3771 _field_122 tinyint(4) DEFAULT '0' NOT NULL,
3772 _field_126 tinyint(4),
3773 _field_134 tinyint(4),
3774 PRIMARY KEY (t1_id),
3775 UNIQUE _field_72 (_field_72),
3776 KEY _field_115 (_field_115),
3777 KEY _field_122 (_field_122)
3778 );
3779 Warnings:
3780 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3781 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3782 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3783 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3784 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3785 INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
3786 INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
3787 INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
3788 CREATE TABLE t2 (
3789 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
3790 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
3791 PRIMARY KEY (seq_0_id,seq_1_id)
3792 );
3793 Warnings:
3794 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3795 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3796 INSERT INTO t2 VALUES (1,1);
3797 INSERT INTO t2 VALUES (2,1);
3798 INSERT INTO t2 VALUES (2,2);
3799 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
3800 CREATE TABLE t3 (
3801 t3_id bigint(21) NOT NULL auto_increment,
3802 _field_131 varchar(128),
3803 _field_133 tinyint(4) DEFAULT '0' NOT NULL,
3804 _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
3805 _field_137 tinyint(4),
3806 _field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
3807 _field_140 blob,
3808 _field_142 tinyint(4) DEFAULT '0' NOT NULL,
3809 _field_145 tinyint(4) DEFAULT '0' NOT NULL,
3810 _field_148 tinyint(4) DEFAULT '0' NOT NULL,
3811 PRIMARY KEY (t3_id),
3812 KEY _field_133 (_field_133),
3813 KEY _field_135 (_field_135),
3814 KEY _field_139 (_field_139),
3815 KEY _field_142 (_field_142),
3816 KEY _field_145 (_field_145),
3817 KEY _field_148 (_field_148)
3818 );
3819 Warnings:
3820 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3821 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3822 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3823 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3824 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3825 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3826 INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
3827 INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
3828 SET sql_mode = default;
3829 CREATE TABLE t4 (
3830 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
3831 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
3832 PRIMARY KEY (seq_0_id,seq_1_id)
3833 );
3834 Warnings:
3835 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3836 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3837 INSERT INTO t4 VALUES (1,1);
3838 INSERT INTO t4 VALUES (2,1);
3839 CREATE TABLE t5 (
3840 t5_id bigint(21) NOT NULL auto_increment,
3841 _field_149 tinyint(4),
3842 _field_156 varchar(128) DEFAULT '' NOT NULL,
3843 _field_157 varchar(128) DEFAULT '' NOT NULL,
3844 _field_158 varchar(128) DEFAULT '' NOT NULL,
3845 _field_159 varchar(128) DEFAULT '' NOT NULL,
3846 _field_160 varchar(128) DEFAULT '' NOT NULL,
3847 _field_161 varchar(128) DEFAULT '' NOT NULL,
3848 PRIMARY KEY (t5_id),
3849 KEY _field_156 (_field_156),
3850 KEY _field_157 (_field_157),
3851 KEY _field_158 (_field_158),
3852 KEY _field_159 (_field_159),
3853 KEY _field_160 (_field_160),
3854 KEY _field_161 (_field_161)
3855 );
3856 Warnings:
3857 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3858 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3859 INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
3860 INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
3861 CREATE TABLE t6 (
3862 seq_0_id bigint(21) DEFAULT '0' NOT NULL,
3863 seq_1_id bigint(21) DEFAULT '0' NOT NULL,
3864 PRIMARY KEY (seq_0_id,seq_1_id)
3865 );
3866 Warnings:
3867 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3868 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3869 INSERT INTO t6 VALUES (1,1);
3870 INSERT INTO t6 VALUES (1,2);
3871 INSERT INTO t6 VALUES (2,2);
3872 CREATE TABLE t7 (
3873 t7_id bigint(21) NOT NULL auto_increment,
3874 _field_143 tinyint(4),
3875 _field_165 varchar(32),
3876 _field_166 smallint(6) DEFAULT '0' NOT NULL,
3877 PRIMARY KEY (t7_id),
3878 KEY _field_166 (_field_166)
3879 );
3880 Warnings:
3881 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3882 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3883 Warning 1681 Integer display width is deprecated and will be removed in a future release.
3884 INSERT INTO t7 VALUES (1,0,'High',1);
3885 INSERT INTO t7 VALUES (2,0,'Medium',2);
3886 INSERT INTO t7 VALUES (3,0,'Low',3);
3887 select
3888 replace(t3._field_140, "\r","^M"),
3889 t3_id,
3890 min(t3._field_131),
3891 min(t3._field_135),
3892 min(t3._field_139),
3893 min(t3._field_137),
3894 min(link_alias_142._field_165),
3895 min(link_alias_133._field_72),
3896 min(t3._field_145),
3897 min(link_alias_148._field_156),
3898 replace(min(t3._field_140), "\r","^M"),
3899 t3.t3_id
3900 from
3901 t3 left join t4 on t4.seq_0_id = t3.t3_id
3902 left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
3903 left join t6 on t6.seq_0_id = t3.t3_id
3904 left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
3905 left join t2 on t2.seq_0_id = t3.t3_id
3906 left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
3907 where
3908 t3.t3_id in (1)
3909 group by
3910 t3.t3_id
3911 order by
3912 link_alias_142._field_166,
3913 _field_139,
3914 link_alias_133._field_72,
3915 _field_135,
3916 link_alias_148._field_156
3917 ;
3918 replace(t3._field_140, "\r","^M") t3_id min(t3._field_131) min(t3._field_135) min(t3._field_139) min(t3._field_137) min(link_alias_142._field_165) min(link_alias_133._field_72) min(t3._field_145) min(link_alias_148._field_156) replace(min(t3._field_140), "\r","^M") t3_id
3919 test^M
3920 job^M
3921 1 1 test job 1 0000-00-00 00:00:00 1999-02-25 22:43:32 0 High admin 0 tomato test^M
3922 job^M
3923 1 1
3924 select * from information_schema.optimizer_trace;
3925 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
3926 select
3927 replace(t3._field_140, "\r","^M"),
3928 t3_id,
3929 min(t3._field_131),
3930 min(t3._field_135),
3931 min(t3._field_139),
3932 min(t3._field_137),
3933 min(link_alias_142._field_165),
3934 min(link_alias_133._field_72),
3935 min(t3._field_145),
3936 min(link_alias_148._field_156),
3937 replace(min(t3._field_140), "\r","^M"),
3938 t3.t3_id
3939 from
3940 t3 left join t4 on t4.seq_0_id = t3.t3_id
3941 left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
3942 left join t6 on t6.seq_0_id = t3.t3_id
3943 left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
3944 left join t2 on t2.seq_0_id = t3.t3_id
3945 left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
3946 where
3947 t3.t3_id in (1)
3948 group by
3949 t3.t3_id
3950 order by
3951 link_alias_142._field_166,
3952 _field_139,
3953 link_alias_133._field_72,
3954 _field_135,
3955 link_alias_148._field_156 {
3956 "steps": [
3957 {
3958 "join_optimization": {
3959 "select#": 1,
3960 "steps": [
3961 {
3962 "condition_processing": {
3963 "condition": "WHERE",
3964 "original_condition": "(`t3`.`t3_id` = 1)",
3965 "steps": [
3966 {
3967 "transformation": "equality_propagation",
3968 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
3969 },
3970 {
3971 "transformation": "constant_propagation",
3972 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
3973 },
3974 {
3975 "transformation": "trivial_condition_removal",
3976 "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
3977 }
3978 ] /* steps */
3979 } /* condition_processing */
3980 },
3981 {
3982 "substitute_generated_columns": {
3983 } /* substitute_generated_columns */
3984 },
3985 {
3986 "table_dependencies": [
3987 {
3988 "table": "`t3`",
3989 "row_may_be_null": false,
3990 "map_bit": 0,
3991 "depends_on_map_bits": [
3992 ] /* depends_on_map_bits */
3993 },
3994 {
3995 "table": "`t4`",
3996 "row_may_be_null": true,
3997 "map_bit": 1,
3998 "depends_on_map_bits": [
3999 0
4000 ] /* depends_on_map_bits */
4001 },
4002 {
4003 "table": "`t7` `link_alias_142`",
4004 "row_may_be_null": true,
4005 "map_bit": 2,
4006 "depends_on_map_bits": [
4007 0,
4008 1
4009 ] /* depends_on_map_bits */
4010 },
4011 {
4012 "table": "`t6`",
4013 "row_may_be_null": true,
4014 "map_bit": 3,
4015 "depends_on_map_bits": [
4016 0,
4017 1,
4018 2
4019 ] /* depends_on_map_bits */
4020 },
4021 {
4022 "table": "`t1` `link_alias_133`",
4023 "row_may_be_null": true,
4024 "map_bit": 4,
4025 "depends_on_map_bits": [
4026 0,
4027 1,
4028 2,
4029 3
4030 ] /* depends_on_map_bits */
4031 },
4032 {
4033 "table": "`t2`",
4034 "row_may_be_null": true,
4035 "map_bit": 5,
4036 "depends_on_map_bits": [
4037 0,
4038 1,
4039 2,
4040 3,
4041 4
4042 ] /* depends_on_map_bits */
4043 },
4044 {
4045 "table": "`t5` `link_alias_148`",
4046 "row_may_be_null": true,
4047 "map_bit": 6,
4048 "depends_on_map_bits": [
4049 0,
4050 1,
4051 2,
4052 3,
4053 4,
4054 5
4055 ] /* depends_on_map_bits */
4056 }
4057 ] /* table_dependencies */
4058 },
4059 {
4060 "ref_optimizer_key_uses": [
4061 {
4062 "table": "`t3`",
4063 "field": "t3_id",
4064 "equals": "1",
4065 "null_rejecting": true
4066 },
4067 {
4068 "table": "`t4`",
4069 "field": "seq_0_id",
4070 "equals": "1",
4071 "null_rejecting": true
4072 },
4073 {
4074 "table": "`t7` `link_alias_142`",
4075 "field": "t7_id",
4076 "equals": "`t4`.`seq_1_id`",
4077 "null_rejecting": true
4078 },
4079 {
4080 "table": "`t6`",
4081 "field": "seq_0_id",
4082 "equals": "1",
4083 "null_rejecting": true
4084 },
4085 {
4086 "table": "`t1` `link_alias_133`",
4087 "field": "t1_id",
4088 "equals": "`t6`.`seq_1_id`",
4089 "null_rejecting": true
4090 },
4091 {
4092 "table": "`t2`",
4093 "field": "seq_0_id",
4094 "equals": "1",
4095 "null_rejecting": true
4096 },
4097 {
4098 "table": "`t5` `link_alias_148`",
4099 "field": "t5_id",
4100 "equals": "`t2`.`seq_1_id`",
4101 "null_rejecting": true
4102 }
4103 ] /* ref_optimizer_key_uses */
4104 },
4105 {
4106 "rows_estimation": [
4107 {
4108 "table": "`t3`",
4109 "rows": 1,
4110 "cost": 1,
4111 "table_type": "const",
4112 "empty": false
4113 },
4114 {
4115 "table": "`t4`",
4116 "range_analysis": {
4117 "table_scan": {
4118 "rows": 2,
4119 "cost": 2.80208
4120 } /* table_scan */,
4121 "potential_range_indexes": [
4122 {
4123 "index": "PRIMARY",
4124 "usable": true,
4125 "key_parts": [
4126 "seq_0_id",
4127 "seq_1_id"
4128 ] /* key_parts */
4129 }
4130 ] /* potential_range_indexes */,
4131 "best_covering_index_scan": {
4132 "index": "PRIMARY",
4133 "cost": 0.46087,
4134 "chosen": true
4135 } /* best_covering_index_scan */,
4136 "setup_range_conditions": [
4137 ] /* setup_range_conditions */,
4138 "group_index_range": {
4139 "chosen": false,
4140 "cause": "not_single_table"
4141 } /* group_index_range */,
4142 "skip_scan_range": {
4143 "chosen": false,
4144 "cause": "not_single_table"
4145 } /* skip_scan_range */,
4146 "analyzing_range_alternatives": {
4147 "range_scan_alternatives": [
4148 {
4149 "index": "PRIMARY",
4150 "ranges": [
4151 "seq_0_id = 1"
4152 ] /* ranges */,
4153 "index_dives_for_eq_ranges": true,
4154 "rowid_ordered": false,
4155 "using_mrr": false,
4156 "index_only": true,
4157 "in_memory": -1,
4158 "rows": 1,
4159 "cost": 0.36,
4160 "chosen": true
4161 }
4162 ] /* range_scan_alternatives */,
4163 "analyzing_roworder_intersect": {
4164 "usable": false,
4165 "cause": "too_few_roworder_scans"
4166 } /* analyzing_roworder_intersect */
4167 } /* analyzing_range_alternatives */,
4168 "chosen_range_access_summary": {
4169 "range_access_plan": {
4170 "type": "range_scan",
4171 "index": "PRIMARY",
4172 "rows": 1,
4173 "ranges": [
4174 "seq_0_id = 1"
4175 ] /* ranges */
4176 } /* range_access_plan */,
4177 "rows_for_plan": 1,
4178 "cost_for_plan": 0.36,
4179 "chosen": true
4180 } /* chosen_range_access_summary */
4181 } /* range_analysis */
4182 },
4183 {
4184 "table": "`t7` `link_alias_142`",
4185 "table_scan": {
4186 "rows": 3,
4187 "cost": 0.503906
4188 } /* table_scan */
4189 },
4190 {
4191 "table": "`t6`",
4192 "range_analysis": {
4193 "table_scan": {
4194 "rows": 3,
4195 "cost": 2.90311
4196 } /* table_scan */,
4197 "potential_range_indexes": [
4198 {
4199 "index": "PRIMARY",
4200 "usable": true,
4201 "key_parts": [
4202 "seq_0_id",
4203 "seq_1_id"
4204 ] /* key_parts */
4205 }
4206 ] /* potential_range_indexes */,
4207 "best_covering_index_scan": {
4208 "index": "PRIMARY",
4209 "cost": 0.571739,
4210 "chosen": true
4211 } /* best_covering_index_scan */,
4212 "setup_range_conditions": [
4213 ] /* setup_range_conditions */,
4214 "group_index_range": {
4215 "chosen": false,
4216 "cause": "not_single_table"
4217 } /* group_index_range */,
4218 "skip_scan_range": {
4219 "chosen": false,
4220 "cause": "not_single_table"
4221 } /* skip_scan_range */,
4222 "analyzing_range_alternatives": {
4223 "range_scan_alternatives": [
4224 {
4225 "index": "PRIMARY",
4226 "ranges": [
4227 "seq_0_id = 1"
4228 ] /* ranges */,
4229 "index_dives_for_eq_ranges": true,
4230 "rowid_ordered": false,
4231 "using_mrr": false,
4232 "index_only": true,
4233 "in_memory": -1,
4234 "rows": 1,
4235 "cost": 0.36,
4236 "chosen": true
4237 }
4238 ] /* range_scan_alternatives */,
4239 "analyzing_roworder_intersect": {
4240 "usable": false,
4241 "cause": "too_few_roworder_scans"
4242 } /* analyzing_roworder_intersect */
4243 } /* analyzing_range_alternatives */,
4244 "chosen_range_access_summary": {
4245 "range_access_plan": {
4246 "type": "range_scan",
4247 "index": "PRIMARY",
4248 "rows": 1,
4249 "ranges": [
4250 "seq_0_id = 1"
4251 ] /* ranges */
4252 } /* range_access_plan */,
4253 "rows_for_plan": 1,
4254 "cost_for_plan": 0.36,
4255 "chosen": true
4256 } /* chosen_range_access_summary */
4257 } /* range_analysis */
4258 },
4259 {
4260 "table": "`t1` `link_alias_133`",
4261 "table_scan": {
4262 "rows": 3,
4263 "cost": 0.510254
4264 } /* table_scan */
4265 },
4266 {
4267 "table": "`t2`",
4268 "range_analysis": {
4269 "table_scan": {
4270 "rows": 3,
4271 "cost": 2.90311
4272 } /* table_scan */,
4273 "potential_range_indexes": [
4274 {
4275 "index": "PRIMARY",
4276 "usable": true,
4277 "key_parts": [
4278 "seq_0_id",
4279 "seq_1_id"
4280 ] /* key_parts */
4281 }
4282 ] /* potential_range_indexes */,
4283 "best_covering_index_scan": {
4284 "index": "PRIMARY",
4285 "cost": 0.571739,
4286 "chosen": true
4287 } /* best_covering_index_scan */,
4288 "setup_range_conditions": [
4289 ] /* setup_range_conditions */,
4290 "group_index_range": {
4291 "chosen": false,
4292 "cause": "not_single_table"
4293 } /* group_index_range */,
4294 "skip_scan_range": {
4295 "chosen": false,
4296 "cause": "not_single_table"
4297 } /* skip_scan_range */,
4298 "analyzing_range_alternatives": {
4299 "range_scan_alternatives": [
4300 {
4301 "index": "PRIMARY",
4302 "ranges": [
4303 "seq_0_id = 1"
4304 ] /* ranges */,
4305 "index_dives_for_eq_ranges": true,
4306 "rowid_ordered": false,
4307 "using_mrr": false,
4308 "index_only": true,
4309 "in_memory": -1,
4310 "rows": 1,
4311 "cost": 0.36,
4312 "chosen": true
4313 }
4314 ] /* range_scan_alternatives */,
4315 "analyzing_roworder_intersect": {
4316 "usable": false,
4317 "cause": "too_few_roworder_scans"
4318 } /* analyzing_roworder_intersect */
4319 } /* analyzing_range_alternatives */,
4320 "chosen_range_access_summary": {
4321 "range_access_plan": {
4322 "type": "range_scan",
4323 "index": "PRIMARY",
4324 "rows": 1,
4325 "ranges": [
4326 "seq_0_id = 1"
4327 ] /* ranges */
4328 } /* range_access_plan */,
4329 "rows_for_plan": 1,
4330 "cost_for_plan": 0.36,
4331 "chosen": true
4332 } /* chosen_range_access_summary */
4333 } /* range_analysis */
4334 },
4335 {
4336 "table": "`t5` `link_alias_148`",
4337 "table_scan": {
4338 "rows": 2,
4339 "cost": 0.503418
4340 } /* table_scan */
4341 }
4342 ] /* rows_estimation */
4343 },
4344 {
4345 "considered_execution_plans": [
4346 {
4347 "plan_prefix": [
4348 "`t3`"
4349 ] /* plan_prefix */,
4350 "table": "`t4`",
4351 "best_access_path": {
4352 "considered_access_paths": [
4353 {
4354 "access_type": "ref",
4355 "index": "PRIMARY",
4356 "rows": 1,
4357 "cost": 0.35,
4358 "chosen": true
4359 },
4360 {
4361 "access_type": "range",
4362 "range_details": {
4363 "used_index": "PRIMARY"
4364 } /* range_details */,
4365 "chosen": false,
4366 "cause": "heuristic_index_cheaper"
4367 }
4368 ] /* considered_access_paths */
4369 } /* best_access_path */,
4370 "condition_filtering_pct": 100,
4371 "rows_for_plan": 1,
4372 "cost_for_plan": 0.35,
4373 "rest_of_plan": [
4374 {
4375 "plan_prefix": [
4376 "`t3`",
4377 "`t4`"
4378 ] /* plan_prefix */,
4379 "table": "`t7` `link_alias_142`",
4380 "best_access_path": {
4381 "considered_access_paths": [
4382 {
4383 "access_type": "eq_ref",
4384 "index": "PRIMARY",
4385 "rows": 1,
4386 "cost": 0.35,
4387 "chosen": true
4388 },
4389 {
4390 "access_type": "scan",
4391 "cost": 0.803906,
4392 "rows": 3,
4393 "chosen": false,
4394 "cause": "cost"
4395 }
4396 ] /* considered_access_paths */
4397 } /* best_access_path */,
4398 "added_to_eq_ref_extension": true,
4399 "condition_filtering_pct": 100,
4400 "rows_for_plan": 1,
4401 "cost_for_plan": 0.7,
4402 "rest_of_plan": [
4403 {
4404 "plan_prefix": [
4405 "`t3`",
4406 "`t4`",
4407 "`t7` `link_alias_142`"
4408 ] /* plan_prefix */,
4409 "table": "`t6`",
4410 "best_access_path": {
4411 "considered_access_paths": [
4412 {
4413 "access_type": "ref",
4414 "index": "PRIMARY",
4415 "rows": 1,
4416 "cost": 0.35,
4417 "chosen": true
4418 },
4419 {
4420 "access_type": "range",
4421 "range_details": {
4422 "used_index": "PRIMARY"
4423 } /* range_details */,
4424 "chosen": false,
4425 "cause": "heuristic_index_cheaper"
4426 }
4427 ] /* considered_access_paths */
4428 } /* best_access_path */,
4429 "added_to_eq_ref_extension": true,
4430 "condition_filtering_pct": 100,
4431 "rows_for_plan": 1,
4432 "cost_for_plan": 1.05,
4433 "rest_of_plan": [
4434 {
4435 "plan_prefix": [
4436 "`t3`",
4437 "`t4`",
4438 "`t7` `link_alias_142`",
4439 "`t6`"
4440 ] /* plan_prefix */,
4441 "table": "`t1` `link_alias_133`",
4442 "best_access_path": {
4443 "considered_access_paths": [
4444 {
4445 "access_type": "eq_ref",
4446 "index": "PRIMARY",
4447 "rows": 1,
4448 "cost": 0.35,
4449 "chosen": true
4450 },
4451 {
4452 "access_type": "scan",
4453 "cost": 0.810254,
4454 "rows": 3,
4455 "chosen": false,
4456 "cause": "cost"
4457 }
4458 ] /* considered_access_paths */
4459 } /* best_access_path */,
4460 "added_to_eq_ref_extension": true,
4461 "condition_filtering_pct": 100,
4462 "rows_for_plan": 1,
4463 "cost_for_plan": 1.4,
4464 "rest_of_plan": [
4465 {
4466 "plan_prefix": [
4467 "`t3`",
4468 "`t4`",
4469 "`t7` `link_alias_142`",
4470 "`t6`",
4471 "`t1` `link_alias_133`"
4472 ] /* plan_prefix */,
4473 "table": "`t2`",
4474 "best_access_path": {
4475 "considered_access_paths": [
4476 {
4477 "access_type": "ref",
4478 "index": "PRIMARY",
4479 "rows": 1,
4480 "cost": 0.35,
4481 "chosen": true
4482 },
4483 {
4484 "access_type": "range",
4485 "range_details": {
4486 "used_index": "PRIMARY"
4487 } /* range_details */,
4488 "chosen": false,
4489 "cause": "heuristic_index_cheaper"
4490 }
4491 ] /* considered_access_paths */
4492 } /* best_access_path */,
4493 "added_to_eq_ref_extension": true,
4494 "condition_filtering_pct": 100,
4495 "rows_for_plan": 1,
4496 "cost_for_plan": 1.75,
4497 "rest_of_plan": [
4498 {
4499 "plan_prefix": [
4500 "`t3`",
4501 "`t4`",
4502 "`t7` `link_alias_142`",
4503 "`t6`",
4504 "`t1` `link_alias_133`",
4505 "`t2`"
4506 ] /* plan_prefix */,
4507 "table": "`t5` `link_alias_148`",
4508 "best_access_path": {
4509 "considered_access_paths": [
4510 {
4511 "access_type": "eq_ref",
4512 "index": "PRIMARY",
4513 "rows": 1,
4514 "cost": 0.35,
4515 "chosen": true
4516 },
4517 {
4518 "access_type": "scan",
4519 "cost": 0.703418,
4520 "rows": 2,
4521 "chosen": false,
4522 "cause": "cost"
4523 }
4524 ] /* considered_access_paths */
4525 } /* best_access_path */,
4526 "added_to_eq_ref_extension": true,
4527 "condition_filtering_pct": 100,
4528 "rows_for_plan": 1,
4529 "cost_for_plan": 2.1,
4530 "chosen": true
4531 }
4532 ] /* rest_of_plan */
4533 }
4534 ] /* rest_of_plan */
4535 }
4536 ] /* rest_of_plan */
4537 }
4538 ] /* rest_of_plan */
4539 }
4540 ] /* rest_of_plan */
4541 }
4542 ] /* considered_execution_plans */
4543 },
4544 {
4545 "condition_on_constant_tables": "true",
4546 "condition_value": true
4547 },
4548 {
4549 "attaching_conditions_to_tables": {
4550 "original_condition": "true",
4551 "attached_conditions_computation": [
4552 ] /* attached_conditions_computation */,
4553 "attached_conditions_summary": [
4554 {
4555 "table": "`t4`",
4556 "attached": "<if>(is_not_null_compl(t4), (`t4`.`seq_0_id` = 1), true)"
4557 },
4558 {
4559 "table": "`t7` `link_alias_142`",
4560 "attached": "<if>(is_not_null_compl(link_alias_142), (`link_alias_142`.`t7_id` = `t4`.`seq_1_id`), true)"
4561 },
4562 {
4563 "table": "`t6`",
4564 "attached": "<if>(is_not_null_compl(t6), (`t6`.`seq_0_id` = 1), true)"
4565 },
4566 {
4567 "table": "`t1` `link_alias_133`",
4568 "attached": "<if>(is_not_null_compl(link_alias_133), (`link_alias_133`.`t1_id` = `t6`.`seq_1_id`), true)"
4569 },
4570 {
4571 "table": "`t2`",
4572 "attached": "<if>(is_not_null_compl(t2), (`t2`.`seq_0_id` = 1), true)"
4573 },
4574 {
4575 "table": "`t5` `link_alias_148`",
4576 "attached": "<if>(is_not_null_compl(link_alias_148), (`link_alias_148`.`t5_id` = `t2`.`seq_1_id`), true)"
4577 }
4578 ] /* attached_conditions_summary */
4579 } /* attaching_conditions_to_tables */
4580 },
4581 {
4582 "optimizing_distinct_group_by_order_by": {
4583 "simplifying_order_by": {
4584 "original_clause": "`link_alias_142`.`_field_166`,'1999-02-25 22:43:32',`link_alias_133`.`_field_72`,'0000-00-00 00:00:00',`link_alias_148`.`_field_156`",
4585 "items": [
4586 {
4587 "item": "`link_alias_142`.`_field_166`"
4588 },
4589 {
4590 "item": "'1999-02-25 22:43:32'",
4591 "uses_only_constant_tables": true
4592 },
4593 {
4594 "item": "`link_alias_133`.`_field_72`"
4595 },
4596 {
4597 "item": "'0000-00-00 00:00:00'",
4598 "uses_only_constant_tables": true
4599 },
4600 {
4601 "item": "`link_alias_148`.`_field_156`"
4602 }
4603 ] /* items */,
4604 "resulting_clause_is_simple": false,
4605 "resulting_clause": "`link_alias_142`.`_field_166`,`link_alias_133`.`_field_72`,`link_alias_148`.`_field_156`"
4606 } /* simplifying_order_by */,
4607 "simplifying_group_by": {
4608 "original_clause": "'1'",
4609 "items": [
4610 {
4611 "item": "'1'",
4612 "uses_only_constant_tables": true
4613 }
4614 ] /* items */,
4615 "resulting_clause_is_simple": true,
4616 "resulting_clause": ""
4617 } /* simplifying_group_by */
4618 } /* optimizing_distinct_group_by_order_by */
4619 },
4620 {
4621 "finalizing_table_conditions": [
4622 {
4623 "table": "`t4`",
4624 "original_table_condition": "<if>(is_not_null_compl(t4), (`t4`.`seq_0_id` = 1), true)",
4625 "final_table_condition ": null
4626 },
4627 {
4628 "table": "`t7` `link_alias_142`",
4629 "original_table_condition": "<if>(is_not_null_compl(link_alias_142), (`link_alias_142`.`t7_id` = `t4`.`seq_1_id`), true)",
4630 "final_table_condition ": null
4631 },
4632 {
4633 "table": "`t6`",
4634 "original_table_condition": "<if>(is_not_null_compl(t6), (`t6`.`seq_0_id` = 1), true)",
4635 "final_table_condition ": null
4636 },
4637 {
4638 "table": "`t1` `link_alias_133`",
4639 "original_table_condition": "<if>(is_not_null_compl(link_alias_133), (`link_alias_133`.`t1_id` = `t6`.`seq_1_id`), true)",
4640 "final_table_condition ": null
4641 },
4642 {
4643 "table": "`t2`",
4644 "original_table_condition": "<if>(is_not_null_compl(t2), (`t2`.`seq_0_id` = 1), true)",
4645 "final_table_condition ": null
4646 },
4647 {
4648 "table": "`t5` `link_alias_148`",
4649 "original_table_condition": "<if>(is_not_null_compl(link_alias_148), (`link_alias_148`.`t5_id` = `t2`.`seq_1_id`), true)",
4650 "final_table_condition ": null
4651 }
4652 ] /* finalizing_table_conditions */
4653 },
4654 {
4655 "refine_plan": [
4656 {
4657 "table": "`t4`"
4658 },
4659 {
4660 "table": "`t7` `link_alias_142`"
4661 },
4662 {
4663 "table": "`t6`"
4664 },
4665 {
4666 "table": "`t1` `link_alias_133`"
4667 },
4668 {
4669 "table": "`t2`"
4670 },
4671 {
4672 "table": "`t5` `link_alias_148`"
4673 }
4674 ] /* refine_plan */
4675 }
4676 ] /* steps */
4677 } /* join_optimization */
4678 },
4679 {
4680 "join_execution": {
4681 "select#": 1,
4682 "steps": [
4683 ] /* steps */
4684 } /* join_execution */
4685 }
4686 ] /* steps */
4687 } 0 0
4688 drop table t1,t2,t3,t4,t5,t6,t7;
4689 #
4690 # Tracing of ORDER BY & GROUP BY simplification.
4691 #
4692 CREATE TABLE t1 (
4693 pk INT, col_int_key INT,
4694 col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1)
4695 );
4696 INSERT INTO t1 VALUES
4697 (10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
4698 (15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
4699 (20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
4700 (25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
4701 CREATE TABLE t2 (
4702 pk INT, col_int_key INT,
4703 col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
4704 PRIMARY KEY (pk)
4705 );
4706 INSERT INTO t2 VALUES
4707 (1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
4708 (6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
4709 (11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
4710 (16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
4711 (21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
4712 (26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
4713 (31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
4714 (36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
4715 (41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
4716 (46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
4717 (51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
4718 (56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
4719 (61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
4720 (66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
4721 (71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
4722 (76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
4723 (81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
4724 (86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
4725 (91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
4726 (96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');
4727 SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
4728 STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
4729 GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ;
4730 SUM(alias2.col_varchar_nokey) field2
4731 0 1
4732 0 2
4733 0 3
4734 0 4
4735 0 5
4736 0 6
4737 0 7
4738 0 9
4739 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
4740 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
4741 SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
4742 STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
4743 GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk {
4744 "steps": [
4745 {
4746 "join_optimization": {
4747 "select#": 1,
4748 "steps": [
4749 {
4750 "condition_processing": {
4751 "condition": "WHERE",
4752 "original_condition": "((0 <> `alias1`.`pk`) and (`alias2`.`pk` = `alias1`.`col_int_key`))",
4753 "steps": [
4754 {
4755 "transformation": "equality_propagation",
4756 "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
4757 },
4758 {
4759 "transformation": "constant_propagation",
4760 "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
4761 },
4762 {
4763 "transformation": "trivial_condition_removal",
4764 "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
4765 }
4766 ] /* steps */
4767 } /* condition_processing */
4768 },
4769 {
4770 "substitute_generated_columns": {
4771 } /* substitute_generated_columns */
4772 },
4773 {
4774 "table_dependencies": [
4775 {
4776 "table": "`t1` `alias1`",
4777 "row_may_be_null": false,
4778 "map_bit": 0,
4779 "depends_on_map_bits": [
4780 ] /* depends_on_map_bits */
4781 },
4782 {
4783 "table": "`t2` `alias2`",
4784 "row_may_be_null": false,
4785 "map_bit": 1,
4786 "depends_on_map_bits": [
4787 0
4788 ] /* depends_on_map_bits */
4789 }
4790 ] /* table_dependencies */
4791 },
4792 {
4793 "ref_optimizer_key_uses": [
4794 {
4795 "table": "`t2` `alias2`",
4796 "field": "pk",
4797 "equals": "`alias1`.`col_int_key`",
4798 "null_rejecting": true
4799 }
4800 ] /* ref_optimizer_key_uses */
4801 },
4802 {
4803 "rows_estimation": [
4804 {
4805 "table": "`t1` `alias1`",
4806 "table_scan": {
4807 "rows": 20,
4808 "cost": 0.524414
4809 } /* table_scan */
4810 },
4811 {
4812 "table": "`t2` `alias2`",
4813 "const_keys_added": {
4814 "keys": [
4815 "PRIMARY"
4816 ] /* keys */,
4817 "cause": "group_by"
4818 } /* const_keys_added */,
4819 "range_analysis": {
4820 "table_scan": {
4821 "rows": 100,
4822 "cost": 12.7221
4823 } /* table_scan */,
4824 "potential_range_indexes": [
4825 {
4826 "index": "PRIMARY",
4827 "usable": true,
4828 "key_parts": [
4829 "pk"
4830 ] /* key_parts */
4831 }
4832 ] /* potential_range_indexes */,
4833 "setup_range_conditions": [
4834 ] /* setup_range_conditions */,
4835 "group_index_range": {
4836 "chosen": false,
4837 "cause": "not_single_table"
4838 } /* group_index_range */,
4839 "skip_scan_range": {
4840 "chosen": false,
4841 "cause": "not_single_table"
4842 } /* skip_scan_range */
4843 } /* range_analysis */
4844 }
4845 ] /* rows_estimation */
4846 },
4847 {
4848 "considered_execution_plans": [
4849 {
4850 "plan_prefix": [
4851 ] /* plan_prefix */,
4852 "table": "`t1` `alias1`",
4853 "best_access_path": {
4854 "considered_access_paths": [
4855 {
4856 "rows_to_scan": 20,
4857 "filtering_effect": [
4858 ] /* filtering_effect */,
4859 "final_filtering_effect": 0.9,
4860 "access_type": "scan",
4861 "resulting_rows": 18,
4862 "cost": 2.52441,
4863 "chosen": true
4864 }
4865 ] /* considered_access_paths */
4866 } /* best_access_path */,
4867 "condition_filtering_pct": 100,
4868 "rows_for_plan": 18,
4869 "cost_for_plan": 2.52441,
4870 "rest_of_plan": [
4871 {
4872 "plan_prefix": [
4873 "`t1` `alias1`"
4874 ] /* plan_prefix */,
4875 "table": "`t2` `alias2`",
4876 "best_access_path": {
4877 "considered_access_paths": [
4878 {
4879 "access_type": "eq_ref",
4880 "index": "PRIMARY",
4881 "rows": 1,
4882 "cost": 6.3,
4883 "chosen": true
4884 },
4885 {
4886 "rows_to_scan": 100,
4887 "filtering_effect": [
4888 ] /* filtering_effect */,
4889 "final_filtering_effect": 1,
4890 "access_type": "scan",
4891 "using_join_cache": true,
4892 "buffers_needed": 1,
4893 "resulting_rows": 100,
4894 "cost": 180.622,
4895 "chosen": false
4896 }
4897 ] /* considered_access_paths */
4898 } /* best_access_path */,
4899 "condition_filtering_pct": 100,
4900 "rows_for_plan": 18,
4901 "cost_for_plan": 8.82441,
4902 "chosen": true
4903 }
4904 ] /* rest_of_plan */
4905 }
4906 ] /* considered_execution_plans */
4907 },
4908 {
4909 "attaching_conditions_to_tables": {
4910 "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and (0 <> `alias1`.`pk`))",
4911 "attached_conditions_computation": [
4912 ] /* attached_conditions_computation */,
4913 "attached_conditions_summary": [
4914 {
4915 "table": "`t1` `alias1`",
4916 "attached": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"
4917 },
4918 {
4919 "table": "`t2` `alias2`",
4920 "attached": "(`alias2`.`pk` = `alias1`.`col_int_key`)"
4921 }
4922 ] /* attached_conditions_summary */
4923 } /* attaching_conditions_to_tables */
4924 },
4925 {
4926 "optimizing_distinct_group_by_order_by": {
4927 "simplifying_order_by": {
4928 "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`",
4929 "items": [
4930 {
4931 "item": "`alias1`.`col_int_key`"
4932 },
4933 {
4934 "item": "`alias2`.`pk`",
4935 "eq_ref_to_preceding_items": true
4936 }
4937 ] /* items */,
4938 "resulting_clause_is_simple": true,
4939 "resulting_clause": "`alias1`.`col_int_key`"
4940 } /* simplifying_order_by */,
4941 "simplifying_group_by": {
4942 "original_clause": "`field2`",
4943 "items": [
4944 {
4945 "item": "`alias2`.`pk`"
4946 }
4947 ] /* items */,
4948 "resulting_clause_is_simple": false,
4949 "resulting_clause": "`field2`"
4950 } /* simplifying_group_by */
4951 } /* optimizing_distinct_group_by_order_by */
4952 },
4953 {
4954 "finalizing_table_conditions": [
4955 {
4956 "table": "`t1` `alias1`",
4957 "original_table_condition": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))",
4958 "final_table_condition ": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"
4959 },
4960 {
4961 "table": "`t2` `alias2`",
4962 "original_table_condition": "(`alias2`.`pk` = `alias1`.`col_int_key`)",
4963 "final_table_condition ": null
4964 }
4965 ] /* finalizing_table_conditions */
4966 },
4967 {
4968 "refine_plan": [
4969 {
4970 "table": "`t1` `alias1`"
4971 },
4972 {
4973 "table": "`t2` `alias2`"
4974 }
4975 ] /* refine_plan */
4976 },
4977 {
4978 "considering_tmp_tables": [
4979 {
4980 "adding_tmp_table_in_plan_at_position": 2,
4981 "write_method": "continuously_update_group_row"
4982 },
4983 {
4984 "adding_sort_to_table": ""
4985 } /* filesort */
4986 ] /* considering_tmp_tables */
4987 }
4988 ] /* steps */
4989 } /* join_optimization */
4990 },
4991 {
4992 "join_execution": {
4993 "select#": 1,
4994 "steps": [
4995 {
4996 "temp_table_aggregate": {
4997 "select#": 1,
4998 "steps": [
4999 {
5000 "creating_tmp_table": {
5001 "tmp_table_info": {
5002 "table": "<temporary>",
5003 "in_plan_at_position": 2,
5004 "columns": 3,
5005 "row_length": 18,
5006 "key_length": 4,
5007 "unique_constraint": false,
5008 "makes_grouped_rows": true,
5009 "cannot_insert_duplicates": false,
5010 "location": "TempTable"
5011 } /* tmp_table_info */
5012 } /* creating_tmp_table */
5013 }
5014 ] /* steps */
5015 } /* temp_table_aggregate */
5016 },
5017 {
5018 "sorting_table": "<temporary>",
5019 "filesort_information": [
5020 {
5021 "direction": "asc",
5022 "expression": "`alias1`.`col_int_key`"
5023 }
5024 ] /* filesort_information */,
5025 "filesort_priority_queue_optimization": {
5026 "usable": false,
5027 "cause": "not applicable (no LIMIT)"
5028 } /* filesort_priority_queue_optimization */,
5029 "filesort_execution": [
5030 ] /* filesort_execution */,
5031 "filesort_summary": {
5032 "memory_available": 262144,
5033 "key_size": "XXX",
5034 "row_size": "XXX",
5035 "max_rows_per_buffer": "XXX",
5036 "num_rows_estimate": 18446744073709551615,
5037 "num_rows_found": 8,
5038 "num_initial_chunks_spilled_to_disk": 0,
5039 "peak_memory_used": "NNN",
5040 "sort_algorithm": "std::sort",
5041 "unpacked_addon_fields": "skip_heuristic",
5042 "sort_mode": "<fixed_sort_key, additional_fields>"
5043 } /* filesort_summary */
5044 }
5045 ] /* steps */
5046 } /* join_execution */
5047 }
5048 ] /* steps */
5049 } 0 0
5050 DROP TABLE t1,t2;
5051 #
5052 # Trace of "condition on constant tables"
5053 #
5054 create table t1(a int) engine=myisam;
5055 insert into t1 values(26);
5056 create table t2(b int primary key, c int) engine=myisam;
5057 insert into t2 values(1,100),(2,200),(3,300);
5058 select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2;
5059 a b c
5060 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
5061 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
5062 select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2 {
5063 "steps": [
5064 {
5065 "join_optimization": {
5066 "select#": 1,
5067 "steps": [
5068 {
5069 "condition_processing": {
5070 "condition": "WHERE",
5071 "original_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))",
5072 "steps": [
5073 {
5074 "transformation": "equality_propagation",
5075 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
5076 },
5077 {
5078 "transformation": "constant_propagation",
5079 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
5080 },
5081 {
5082 "transformation": "trivial_condition_removal",
5083 "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
5084 }
5085 ] /* steps */
5086 } /* condition_processing */
5087 },
5088 {
5089 "substitute_generated_columns": {
5090 } /* substitute_generated_columns */
5091 },
5092 {
5093 "table_dependencies": [
5094 {
5095 "table": "`t1`",
5096 "row_may_be_null": false,
5097 "map_bit": 0,
5098 "depends_on_map_bits": [
5099 ] /* depends_on_map_bits */
5100 },
5101 {
5102 "table": "`t2`",
5103 "row_may_be_null": false,
5104 "map_bit": 1,
5105 "depends_on_map_bits": [
5106 ] /* depends_on_map_bits */
5107 }
5108 ] /* table_dependencies */
5109 },
5110 {
5111 "ref_optimizer_key_uses": [
5112 {
5113 "table": "`t2`",
5114 "field": "b",
5115 "equals": "2",
5116 "null_rejecting": true
5117 }
5118 ] /* ref_optimizer_key_uses */
5119 },
5120 {
5121 "rows_estimation": [
5122 {
5123 "table": "`t1`",
5124 "rows": 1,
5125 "cost": 1,
5126 "table_type": "system",
5127 "empty": false
5128 },
5129 {
5130 "table": "`t2`",
5131 "rows": 1,
5132 "cost": 1,
5133 "table_type": "const",
5134 "empty": false
5135 }
5136 ] /* rows_estimation */
5137 },
5138 {
5139 "condition_on_constant_tables": "(('26' + '200') = cos(10))",
5140 "condition_value": false
5141 }
5142 ] /* steps */,
5143 "empty_result": {
5144 "cause": "Impossible WHERE noticed after reading const tables"
5145 } /* empty_result */
5146 } /* join_optimization */
5147 },
5148 {
5149 "join_execution": {
5150 "select#": 1,
5151 "steps": [
5152 ] /* steps */
5153 } /* join_execution */
5154 }
5155 ] /* steps */
5156 } 0 0
5157 drop table t1,t2;
5158 #
5159 # Trace of non-default db
5160 #
5161 create table t1(a int);
5162 insert into t1 values(1),(2),(3);
5163 create database mysqltest2;
5164 create table mysqltest2.t2(a int);
5165 insert into mysqltest2.t2 values(1),(2);
5166 analyze table t1, mysqltest2.t2;
5167 Table Op Msg_type Msg_text
5168 test.t1 analyze status OK
5169 mysqltest2.t2 analyze status OK
5170 select * from t1,mysqltest2.t2;
5171 a a
5172 1 1
5173 1 2
5174 2 1
5175 2 2
5176 3 1
5177 3 2
5178 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
5179 QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
5180 select * from t1,mysqltest2.t2 {
5181 "steps": [
5182 {
5183 "join_optimization": {
5184 "select#": 1,
5185 "steps": [
5186 {
5187 "table_dependencies": [
5188 {
5189 "table": "`t1`",
5190 "row_may_be_null": false,
5191 "map_bit": 0,
5192 "depends_on_map_bits": [
5193 ] /* depends_on_map_bits */
5194 },
5195 {
5196 "table": "`mysqltest2`.`t2`",
5197 "row_may_be_null": false,
5198 "map_bit": 1,
5199 "depends_on_map_bits": [
5200 ] /* depends_on_map_bits */
5201 }
5202 ] /* table_dependencies */
5203 },
5204 {
5205 "rows_estimation": [
5206 {
5207 "table": "`t1`",
5208 "table_scan": {
5209 "rows": 3,
5210 "cost": 0.501282
5211 } /* table_scan */
5212 },
5213 {
5214 "table": "`mysqltest2`.`t2`",
5215 "table_scan": {
5216 "rows": 2,
5217 "cost": 0.500854
5218 } /* table_scan */
5219 }
5220 ] /* rows_estimation */
5221 },
5222 {
5223 "considered_execution_plans": [
5224 {
5225 "plan_prefix": [
5226 ] /* plan_prefix */,
5227 "table": "`mysqltest2`.`t2`",
5228 "best_access_path": {
5229 "considered_access_paths": [
5230 {
5231 "rows_to_scan": 2,
5232 "access_type": "scan",
5233 "resulting_rows": 2,
5234 "cost": 0.700854,
5235 "chosen": true
5236 }
5237 ] /* considered_access_paths */
5238 } /* best_access_path */,
5239 "condition_filtering_pct": 100,
5240 "rows_for_plan": 2,
5241 "cost_for_plan": 0.700854,
5242 "rest_of_plan": [
5243 {
5244 "plan_prefix": [
5245 "`mysqltest2`.`t2`"
5246 ] /* plan_prefix */,
5247 "table": "`t1`",
5248 "best_access_path": {
5249 "considered_access_paths": [
5250 {
5251 "rows_to_scan": 3,
5252 "access_type": "scan",
5253 "using_join_cache": true,
5254 "buffers_needed": 1,
5255 "resulting_rows": 3,
5256 "cost": 1.1013,
5257 "chosen": true
5258 }
5259 ] /* considered_access_paths */
5260 } /* best_access_path */,
5261 "condition_filtering_pct": 100,
5262 "rows_for_plan": 6,
5263 "cost_for_plan": 1.80216,
5264 "chosen": true
5265 }
5266 ] /* rest_of_plan */
5267 },
5268 {
5269 "plan_prefix": [
5270 ] /* plan_prefix */,
5271 "table": "`t1`",
5272 "best_access_path": {
5273 "considered_access_paths": [
5274 {
5275 "rows_to_scan": 3,
5276 "access_type": "scan",
5277 "resulting_rows": 3,
5278 "cost": 0.801282,
5279 "chosen": true
5280 }
5281 ] /* considered_access_paths */
5282 } /* best_access_path */,
5283 "condition_filtering_pct": 100,
5284 "rows_for_plan": 3,
5285 "cost_for_plan": 0.801282,
5286 "pruned_by_heuristic": true
5287 }
5288 ] /* considered_execution_plans */
5289 },
5290 {
5291 "attaching_conditions_to_tables": {
5292 "original_condition": null,
5293 "attached_conditions_computation": [
5294 ] /* attached_conditions_computation */,
5295 "attached_conditions_summary": [
5296 {
5297 "table": "`mysqltest2`.`t2`",
5298 "attached": null
5299 },
5300 {
5301 "table": "`t1`",
5302 "attached": null
5303 }
5304 ] /* attached_conditions_summary */
5305 } /* attaching_conditions_to_tables */
5306 },
5307 {
5308 "finalizing_table_conditions": [
5309 ] /* finalizing_table_conditions */
5310 },
5311 {
5312 "refine_plan": [
5313 {
5314 "table": "`mysqltest2`.`t2`"
5315 },
5316 {
5317 "table": "`t1`"
5318 }
5319 ] /* refine_plan */
5320 }
5321 ] /* steps */
5322 } /* join_optimization */
5323 },
5324 {
5325 "join_execution": {
5326 "select#": 1,
5327 "steps": [
5328 ] /* steps */
5329 } /* join_execution */
5330 }
5331 ] /* steps */
5332 } 0 0
5333 drop table t1;
5334 drop database mysqltest2;