Debian Codegraph @

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